Over 1 million tech questions and answers.

SQL Query question for Access 2010 query

Q: SQL Query question for Access 2010 query

For a report I have a customer table, an invoice table and a revenue or payment table.

I need to produce a report that shows all the invoices created during a given month and year - that part is easy.

But then I also need to show the first payment given on that invoice. This is a situation where there is always a down payment made at the issuance of the invoice - but sometimes people will make more than one payment during a month.


I want the customer listed for each new invoice, the total amount of that invoice and what the inital payment on that invoice was: [CustomerID][Customer Name] [Invoice No][Total Invoice Amount] [Payment Amt]

So if there was an invoice issued for $100 and they paid 50 and 25 during the month it was issued and the balance on the next month, I still want to show it on this report as a $100 invoice with a $50 downpayment only.

(The tables are established and populated so it is too late to change the payment table to allow for a unique downpayment field. They all go in as payments.)

My problem is that if they made more than one payment during the month, the whole line with the customer name and invoice number shows up for each payment made. I only wnat the FIRST payment. I need a payment query that shows only the first payment in the month of invoice issuance.

Does that make sense?

Preferred Solution: SQL Query question for Access 2010 query

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: SQL Query question for Access 2010 query

Yes it makes sense, I would create a pre-query that uses the Month & Year but is set to "Totals" and Date Minimum.
Then create the Report query using the pre-query linked to the new query's table via the InvoiceID this will limit both the month, year and minimum payment.

Read other 1 answers

I am very new to Access yet I am trying hard to pick things up as I go. I have a database I am creating that needs to show which training courses employees still need to complete.
I have a table showing employees, a table and a query showing which classes employees have completed, and a training table that lists the possible classes. There are 15 possible classes, all of which are required.

I have an example database that I have been pulling ideas from that has a Count function bouncing off of a list that shows each employee id and the required course ids. This makes sense if each dept or employee requires different classes to be taken, but in my database the employees all need the same classes. (200+ employees and 15 classes each would be a lengthy table...).

Here is what I currently have:
tblEmployees =

tblEmployeeTraining = (shows completed training classes)

tblTraining = (shows the required classes)

CompletedTrainingClasses (query)

Any help would be greatly greatly appreciated!
Thank you!

A:Access 2010 Query Question

hedefinesme, welcome to the forum.
Your database design looks good to me.
So what exactly do you need help with?
Is it how to count the number of classes each person has taken?

Read other 3 answers

NOTE: I could not post code because I must be really careful to protect proprietary information.

I work in an assembly plant and I've designed an Access 2003 program to deliver a set of 4 crucial parts to the floor all within a very narrow degree of tolerance. Further, the program integrates FIFO.

Ive done this by assigning each of the essential measurements codes and then running a series of queries:

Query 1 identifies the oldest Part A by sorting the available parts by the date they were inspected then by serial number (to break ties) and delivering the TOP 1.
Query 2 identifies the oldest Part B that is compatible with that Part A using the same method.
Query 3 identifies the oldest Part C that is compatible with that Part B.
Query 4 identifies the oldest Part D that is compatible with the other end of Part A. I have the parts in this order because we can begin assembly if we don't have a Part D on hand, but we must have Parts A, B, and C.
Finally, query 5 simply pulls the information from those queries into one location for a report.
As long as we have enough stock, life is good; but the moment a match cannot be made, it blows up. I cannot figure out how to make it go back to choose the next Part A. I need to figure out how and where to add code to each query to tell it that if no stock is compatible with the Part A produced by Query 1, to go back and pick the next one in line.

Can anyone help? Is the way I've gone about matching parts completely... Read more

A:Access 2003 – Query Based on Query – need code to rerun earlier query

Read other 11 answers

Hi there,

I'm very new to access and I hope I will get the simplest solution on my question below.

I have a table named "PurchaseOrder" and I'd want to create a query that will display the invoices that will due.


PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
8697 D 888 $88 18-Oct-2010

I want to create 2 queries.

The first query will display all the invoices that will due 5 days before the exact due date.
The invoice due date is 15-Oct-2010. The query will display information on 3241, 6589, and 2234.

For the second query, I want to display all the invoices that have exceeded the due date. The information on the respective invoices will be transfered from the query as mentioned earlier to the new query.

The scenario is as below:

Date: 16-Oct-2010


PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
8697 D 888 $88 18-Oct-2010


PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
Date: 18-Oct-2010


PONo Customer Quantity SalesPrice InvoiceDueDate
8697 D 888 $88 18-Oct-2010


PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
3241 A 421 $12 17-Oct-2010
Hope someone can help me with this. Really appreciate and hoping for ur help.


A:Query Access 2007 (how to transfer data from a query to another query)

The first query needs the following first Criteria Row of the InvoiceDueDate
between date() and date() + 5
The second query needs
< date()

I haven't tested it, just worked from memory. so let me know how it goes.

Read other 2 answers

Hi, I have set up a new Access 2010 database (saved as .accdb) (copying tables, forms and queries from my current one then deleting the old data and modifying a lot of fields). I have a query in with some simple calculations. All the calculations work perfectly in Access. When I merge this into Word 2010 (set up as a letter document) it doesn?t use/show the correct data.

The calculated fields in the query are ?number of people * price tea & coffee = cost tea & coffee? and ?number of people * price buffet = cost buffet". I then use these costs in the same query to work out ?VAT? (another calculated field adding up the calculated "cost tea & coffee" and calculated "cost buffet" as well as a number of other non-calculated fields and multiplying the total by 0.2) and ?Total Cost? (a calculated field adding up various non-calculated fields, the calculated "cost tea & coffee", calculated "cost buffet" and the calculated VAT field).

There are two errors in the data that ends up in the merged Word document:
The "VAT" doesn?t add in the two calculated fields, it only uses the data from non-calculated fields in the query.
Also the "Total Cost" doesn?t add in any of the calculated fields (teas & coffees, buffet and VAT), it only uses non-calculated fields from the query.

It doesn?t ping up any error messages. Why is it changing the data between Access (where the query works per... Read more

Read other answers

I've got a lending table in which we lend items out, now they can be loan or permanent.

I can run a report with the query parameter on the ReqDate_Fld criteria Between [Report Start Date] And [Report End Date]

The report prints and counts everything borrowed.

Now I want to add more records to the report to include everything borrowed that was returned during that period specified by the criteria Paramenter set in the query. But using the Return Date (RetDate_Fld)

How can I select rows from the query based on the parameter date filled in when the query ran?
[Report Start Date] and [Report End Date].

I can build a separate report just for the returns using the current parameter but on the RetDate_Fld Between [Report Start Date] And [Report End Date] set by the query.

I've got it grouping at 3 levels to get it to break by Borrower, ReqID, and Request Date

Does this make any sense?

A:Solved: Access query report query paramater use the param

You would have to have another group for whether or not the RetDate_Fld was filled in or not.
Add a new column to the query with the following Heading

RetDatefilled: iif(not isnull(RetDate_Fld), 2, 1)

That should place a 2 in the new column when there is a date and a 1 when there isn't.
You can now group on that data.

Read other 2 answers

I have written a macro to make a table from data in an existing table. Then I use the data in the new table to select information in another table. That select query does not allow me to update the information in the 2nd table. It is like the records are locked. Even if I create a new table myself the select queries are locked but all my pre-existing queries work fine. Is there some type of setting I am missing that causes the information to be locked in select queries? I am thinking somehow the setting may have been changed. Any thoughts would be appreciated.

A:Solved: Access Make Table Query and Select Query

Read other 7 answers

I have a query that returns a list of items. What I need is to display a green/red image/circle to indicate if the item is usable or not. Some suggestion for the indicator are an image ofcourse, or using text where the font is set to Webdings. Either is fine I just have no idea how to get this in a return column of the query. Any tips or other ideas would be a big help. Thank you

Example of form:
(Green) Truck1
(Green) Truck2
(Red) Truck3
(Red) Car1
(Green) Car2

A:Access 2010 Query Help

I think that is asking a bit much of a query, it would be better to create a Form based on the Query and use the Form's Conditional Formatting or VBA to do what you want.

Read other 1 answers

I have a field in the tbl named EquipType. Is there a way that the query can count each type of item that is listed? I have searched here and seen some discussion on this but didn't find the answer. Any help would be appreciated.

A:Access 2010 Query

Read other 10 answers

Hi all,

My brain is melting trying to get this to work! Basically I have the following tables:


Training Record

I want to be able to identify people that have never done a particular course/s. In this case, those with a Course number of 20, 27 or 29.

I assumed it would be using Not Exists, but it just doesn't seem to be working.

Can anyone advise please?


A:Access 2010 query help please

Personally I would change the design slightly. I would have 3 tables, one which ho.ds the employee details, one for all course details then another which bring them together.


Employee table

Training table
Course Name

Employee Training table

So in the Employee Training table you would have something like

ID EmployeeID TrainingID
1 1 1
2 1 2
3 1 4
4 2 1

So then the query would be pretty much easier, you can query on EmployeeID where TrainingID equals (or does not equal) 20, 27, 29.

Read other 2 answers


How do I include a portion of a field in a query parameter?

For example, if one record's data in the field I'm querying is, say, "RPMX-1011" and another record's data is, say, "CO-0852", how do I tell the query I want all records that have "PM" appearing somewhere in the field?

A:Access 2010 query parameters

select * from tablename where fieldname like "*PM*"

Read other 9 answers

Because my desktop just died, I'm having to use my laptop. The former had Office 2000 Pro, which is orders of magnitude easier to use than Office 2010 Pro, which I happen to have on my laptop and have avoided using until now. I am constantly running into things that make no sense, and hit one this morning. I want to rename a query. I go to it (do not open it), right click and pick "Rename". . . but nothing indicates I can rename it. If I try to start typing a new name, the highlighter just moves to a query that starts with the first letter.

I must be missing something simple. So how the h*** do you rename a query in Access 2010?????????????

A:How does on rename a query in Access 2010

In Access 2007 which 2010 is based on you do indeed right click the query and rename it. You could also do a Save As and then delete the original.
But these latest versions of Access have these really annoying supposedly helpful things that Access 2000-2003 didn't have.
So you need to ensure that you are viewing the Items as "Object Type" so that they are not linked to other tables & queries.
Compared to Access 2000 the later versions are a royal pain in the rear end with their helpful Record sources and Grouped form & report objects. I have been using it for a couple of years now and still hate it.

Read other 3 answers

Hi, I am putting together my first Access database, so I could be doing all kinds of things wrong.
What I think I need is to have a query that returns a blank or null or '?' if there is no data entered.

I have been messing around with the Nz() function, and the only way I can get that to work is setting up a separate Genus% Query for the single Genus% Table containing null values, and then setting up my MainQuery using Genus% Query instead of Genus% Table. That is OK, I think.

The main problem is that:
There are several Genera (pl of Genus) that I do not have information for, and the only way I have gotten the blank data to show up is to pull in the Genus from the lookup table, then leave % diseased blank. Since there are soooo many things that I do not have that information for at this point, that is not ideal. As the database continues to grow, we hope to find that information, and also be able to find what information we don't have.

Here is the setup of some tables:
Table: Genera%
ID | Lookup to Genus | % Diseased | Source

Table: Species%
ID | Lookup to Species (also pulls the linked-Genus)|% Diseased | Source

Table: Diseases
ID | Lookup to Genus | Lookup to Species | Lookup to Disease | Lookup to Location | lookup to Depth | Lookup to Source

Now the MainQuery has 6 tables, including all species that are in the database, whereas Genera% and Species% only hold information for some of the other records. I would like the query to return all of the inf... Read more

A:MS Access 2010 - Query w/ Nulls

Try testing for Null. Tables generally hold Nulls if there is no data in a field. Null and "" are two differnt things.

A query should return whatever is in the table regardless if it is a null or blank.

Read other 3 answers

I have a query but I need some help with it so that I can have the reports run correctly.
in the query, it has fields that are:
ProfileRunDate; Freq; NextRunDate

Now what I like to do, it might not be able to be done.
I need to be able to take the ProfileRunData and be able to have the NextRunDate come up when I put in the Freq.

Under Freq, I have m for monthly, q for quarterly, semi-annual and I think that some one told me to use d for days and use 180 days.

I have this formula for the NextRunDate: NextRundate: DateAdd("m",1,[ProfileRunDate]) but my question is, is there a way that I can have the same query set up so that when i put in the freq that it will generate a report for that time period?
Oh, I wanted to add the SQL view here. SELECT [M_DOT Profile Info Table].CoCode, [A_Co Info Table].[CoDOT#], [M_DOT Profile Info Table].[DOTPIN#], [A_Co Info Table].MCS150Update, DateAdd("yyyy",2,[MCS150Update]) AS MCS150Due, [M_DOT Profile Info Table].[MCC#], [M_DOT Profile Info Table].FreqRan, [M_DOT Profile Info Table].ProfileRunDate, DateAdd("m",1,[ProfileRunDate]) AS NextRundate, [M_DOT Profile Info Table].PortalUserID, [M_DOT Profile Info Table].PortalPassword, [M_DOT Profile Info Table].HowTransmited
FROM [M_DOT Profile Info Table] INNER JOIN [A_Co Info Table] ON [M_DOT Profile Info Table].CoCode = [A_Co Info Table].CoCode
WHERE ((([M_DOT Profile Info Table].CoCode) Like [Enter CoCode:] & "*") AND (([M_DOT Profile Info Table].F... Read more

A:Solved: Query in Access 2010

Read other 10 answers

I'm having a devil of a time trying to implement this and I need some help. I have a table that keeps track of maintenance periods for pieces of equipment. I have 2 queries that should prevent entry of dates that would contain another maintenance period for the same equipment, and likewise if one date entered is during another maintenance period again for the same machine.

the dates contain a maintenance period looks like this:

PARAMETERS M_ID Short, E_ID Short, Ddate DateTime, Udate DateTime;
SELECT DISTINCT MaintID, EquipID, DownDate, [UpDate]
FROM tblMaintenancePeriod
WHERE ((tblMaintenancePeriod.MaintID)<>[M_ID]) AND (tblMaintenancePeriod.EquipID)=[E_ID] AND (DownDate BETWEEN Ddate AND Udate OR [UpDate] BETWEEN Ddate ANd Udate);

I would like to add further insurance that conflicting dates cannot occur or that at least the admin can be notified and make the appropriate changes.

My hope is to have a form that contains 2 subforms. The first subform would be populated based on which records currently have conflicting dates. So what I need is to have the query run on each record so that it is compered to all others. If it does have a record count above 0 then it should populate the subform, otherwise it should not show up.

The second subform would just be the query and use the current record in the first subform for the parameter data.

I know this is probably not the best approach, so I am open to suggestions.
Thank you in advance to all those who post.
&nbs... Read more

A:Access 2010 Using Query that preforms on every record

If you do not already have a lot of data in the database the best approach would be to do the check in the data entry form and prevent the entry in the first place, it is also the best long term approach as well.

Read other 3 answers

I have to have this query produce a monthly average availability for a selection of equipment. The query looks pretty ugly and rather bloated and I'm wondering if there's something I can do to make it better.

Right now I have an Expression column representing each month. In each month, take January for example, I need to check if the maintenance period occurred within the month, started before the month but ended during, started during the month but ended outside, completely encapsulates the month (if Jan 2012, then started maintenance Dec 2011 ended Feb 2012), or not count it because it was fully operational throughout the month.

So as you can see checking these 4 cases and doing calculations afterwards really bloats up one month, let alone 12! So I'm just looking for a suggestion if there's something more I can be doing. I'm not afraid of some RnD if someone has articles they can point me towards. If this approach is not overly bad I don't mind, I would just like to do better if its possible.

Thanks in advanced!

A:Access 2010 Optimizing a bloated query

Read other 15 answers

Hi Forummers!
Access 2010 - I have a modal pop up logging form with a little VB it works fine all forms open ok.
Problem is one form calls to open results from a query and the query opens in the back ground.
Users would have to close the forms to get to the results. Can't have users messing around in the database.
This is the VB in the back ground from that is opening the all forms:
Private Sub Form_Load()
atDocName = "frmUserLogin"
DoCmd.OpenForm atDocName, , , atLinkCriteria

End Sub

Im not sure what to do - what code to use, and where to place it -- any advise what to do !!

A:MS ACCESS 2010 modal pop up / query results

Without seeing the database...Sounds like you need to have the offending query (results) attached to a form and open that form rather than opening the raw query.

Read other 2 answers

I have a query that will export to excel availability of items my database keeps track of. When i look at the excel export though the percentages are implemented as strings and excel gives of a warning/ notification.

the line in my query for this field is :

[DownDate] Between FirstDayInWeek([thisDate]) And [thisDate] And [UpDate] Between FirstDayInWeek([thisDate]) And [thisDate],

Round((DateDiff('s',[DownDate],[UpDate]))/(DateDiff('s',FirstDayInWeek([thisDate]),[thisDate]))*100,2) ),

"Fixed") & "%" AS Week

I cut out the other parts of the switch for simplicity. Anyway a percentage value is produced "51.20%" but it seems it is recognized as a string instead of a value. I thought this may be becasue of Format and the inclusion of & "%" but when i tested it a string was still produced. I just need a number produce so excel doesn't ***** advice is much appreciated.

A:Access 2010 query produce String and not #

I could ask why you are using Excel?
Try placing a Val() around the calculation, which should force it to a number.
The % sign may well be complicating the issue, so can't you format that in Excel.

Read other 2 answers

Access 2010 - I have a modal pop up logging form with a little VB it works fine all forms open ok.
Problem is one form calls to open results from a query and the query opens in the back ground.
Users would have to close the forms to get to the results. We can't have users messing around in the database.
This is the VB in the back ground from that is opening the all forms:
Private Sub Form_Load()
atDocName = "frmUserLogin"
DoCmd.OpenForm atDocName, , , atLinkCriteria

End Sub

Im not sure what to do - any advise what to do !!

A:MS ACCESS 2010 modal pop up / query results

Double posting is frowned upon by the site admins...Prepare to be chastized!! The 'business apps' forum is the correct place to post your MS Access issue.

Read other 2 answers

I have a previously designed Access DB and there is a query that I am trying to understand. It has several tables that are queried more than once in a single query. First how do I determine the actual table name? I can only see the alias name. Also what is the purpose of using the same table twice in a query?

A:Solved: Access Query using tables more than once in same query

It is normally to do calculations or comparisons on prior or next records. It is the one weekness of Access Queries, the Totals function has some good arithmetic calculations like average, Count Sum etc but manipulating previous records like add or subtract values from the current record it can't do. So it requires more advanced SQl. Which is apperas you have.
Have you looked at the query in SQL view?
It should use the word "As" to designate the alias.

Read other 2 answers

I'm trying to export a query to excel but the query is geneared based in the following two parameters:

[Start Date (MM-DD-YYYY)?] DateTime,
[End Date (MM-DD-YYYY)?] DateTime;

When I try to run the code below it give me the following error "Run-time error '3061': Too few parameters.Expected 2 , highlighting this line of the code: Set rs = db.OpenRecordset("S QMETRIC - INITIATED - CC VS CM")

I'm pretty sure that the code is not recognizing these two parameters because when I run the code without using input parameters to filter my query data the code works perfectly, it transfer the data completely. But I need to limit the data to specific date.

Private Sub Command39_Click()
Dim x1 As Excel.Application
Set x1 = New Excel.Application
x1.Visible = True
x1.Workbooks.Open ("C:\Users\mhernand\Desktop\QMR GRAPH Template.xlsx")
Dim ws As Worksheet
Set ws = x1.ActiveWorkbook.Sheets("CC Vs. CM")
Dim db As DAO.Database, rownum As Long
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("S QMETRIC - INITIATED - CC VS CM")
rownum = 2
Do While Not rs.EOF
ws.Cells(rownum, 1).Value = rs.Fields("STARTING YEAR ORDER").Value
ws.Cells(rownum, 2).Value = rs.Fields("Start Quarter").Value
ws.Cells(rownum, 3).Value = rs.Fields("Total").Value
ws.Cells(rownum, 4).Value = rs.Fields("Standarized Change Classification").Value
rown... Read more

Read other answers

You guys have been a huge help in the past with issues I've had with Software Licensing database I created in Access, so I'm hoping you can lend a hand with a new issue I'm running into:

i copied some data from a table to do some comparison and clean up in Excel. I realized a little late that I should have pulled more fields than I did... I have done the clean up and now I'd like to get the additional fields to finish the job. I've imported the cleaned up data as a new table (Table A) and want to run a query against the original table (Table B) to pull those additional fields. Not all of the records in Table A are present in Table B, and vice versa.

In the query design, I show Tables A and B and join the First Name, Last Name and Application fields. The Join Properties are to include all records from Table A and only those from Table B where the joined fields are equal. Table A has 1606 records, so my hope is to have the query return 1606 records with the missing fields added. I'm bringing in all of the fields from Table A and adding the missing fields from Table B.

The query returns 1899 records. I ofound one user who only has four records in Table A, three records in Table B, and the query is returning 12 records for them.

What am I doing wrong?

A:Access 2010 - Adding fields to a table using a query

Read other 16 answers

I'm sure this is an easy fix but I can't seem to find it. I just have a form, that will be a subform of another, that needs to display the results of a query.
The query is simple enough, just displays all fields of records that fall between specified dates. The query works great, but when I attach it to the form as its record source it doesn't display the data. I can see the correct amount of record selectors so I know its understanding the query but its as if all fields are hidden!

I have also tried building a query to the forms record source that was simply Select query.* From query. Oddly I have had this working before but I had to specify every field. What I mean is:

Select title From query
Select type From query
Select date From query ...

And so on for all the fields but this seems foolish, can anyone think of what I may be doing wrong?
Thanks in advance!
Edit, forgot to mention I also tried the foolish solution that I mentioned above and it didn't work so its definitely some issue that I'm not seeing, some property that's probably not appropriately set

A:Solved: Access 2010 form not displaying query

Read other 8 answers

I have several queries that I run daily to update sales, shipments, etc. How do I set up a macro to run a make table query and then export the table to another access database? (The query deletes the records already in the table before re-populating.) If possible, I would prefer to not open the query, just run it. Please know that I am a very new user of Access, so I may need step-by-step examples.
Thanks for your help!

A:Access 2010 Macro to run make table query

Are you sure that you need a maketable query?
Won't an append or update query work?
Also why do you then need to export the table, can't this be done in the other database?

Read other 3 answers

I have multiple office locations that don't have a network or computer expertise but they do have email. I needed to get them to enter their budget data, export it and then email it to a central office where each department's export to excel will be imported into the master database to be worked with further there. I didn't want the employees to have too many difficult operations so I set up a query to combine all their tables into one query and then one button click exports it to an email to a guy at the central office. The problem is that once he gets these emails in, I want him to be able to append each of their spreadsheet data back onto the three tables that they were originally on. He will have to do this for each department that sends him info. The problem is the append query only works for the first table. I have set him up with imports into an IMPORT DATA table. Once there, the append queries are to query out just the data to append to the PROJECT table, then another append query is to get the data for the COST table and the third is for the FUNDING table.

Only the Project table which is the lead table in all the relationships will run this append query. The other two keep losing the data in the query. Everytime I reopen the query I see that the fields I had put on them are gone and the query is blank again. I am getting this message "Reserved error(-3087); there is no message for this error".

Can you point me in the right direction?

Marg... Read more

A:Solved: Access 2010 append query error message

Read other 6 answers

1. I have a query already built that doesn't have any criteria built into it, because the criteria can change with the type of information that a user is looking for.

2. Rather than have users who are not familiar with Access trying to input the criteria they need on a table into the design view of a query I would like to utilize a form.

3. I would like to find out if it is possible for a user to enter the criteria they are looking for in fields on a form and have a table open with the records that match that criteria when the user clicks submit on the form. (To me it is kind of like parameter queries. But, I just want it in a form instead having the query asking the user one question at a time.)


Read other answers

I am creating a golf club database as part of my studies, I had a few issues previously which were resolved on here, thanks.

But now I have another drama, I have an update query "Category_Update" which updates information into a table "tblWinners". There are 6 possible entries. M - J, M - A, M - S, F - J, F - A, F - S, where M is male, F is female, J is junior, A is adult and S being senior.

I have a macro which deletes the current entries in the "tblWinners" table and a form which uses text boxes to update the table using the query. Prior to adding the macro, if an entry matching the data I try to input is present (e.g. Gender:M and Category: A already in the table) it will attempt to run the query, but fail because "Gender" and "Category" are primary keys in the "tblWinners" table. But if the data I input does not match an existing entry the query will fail, not even attempting (as in 'You are about to append 0 row(s).' as opposed to 'You are about to append 1 row(s).' and then failing due the the primary key issue.

Previously in my study i read that you need an entry to append to, but with the deletion macro there is nothing to append to and so it fails.

Any help would be greatly appreciated.


A:Solved: Access 2010 Update Query from text box parameters in a form

Normally Append Queries append to empty Tables without any problems.
So it would appear that you are not meeting the criteria that the Table needs to do the append with, does the query run on it's own when you run it manually.

Read other 2 answers

Hi Access bods.
Can anyone tell me how to do an expression to query from DATATABLE (the name of table), containing

ID: Auto ID key for the table
SITEID: There are multiple sites
ITEMID: Each site will have multiple Items
QUALITYID: This is set to either F, or is left blank
Id like to find the Percentage of Non-F, to each SITEID and ITEMID combo. I hope this makes sense.

A:Solved: Access Query, Query

Read other 6 answers

Hello all,

Im working on a query in our Access Donor DB.

I want to show only gifts for the last three years, and I want to only show the last 5 gifts a donor has made during that period.

I got the query to show the gifts for the last three years but, how do I display only the last five gifts?

A:Access query question

In the Queries properties, set the Output Records to "5" instead of "All".

Read other 3 answers

I haven't used Access in awhile so I am having a brain fart regarding queries. I have a table with a ton of information in it so I want to have a button on a switchboard where I can look up the information by last name or order number. How would I create this query?

Thank you in advance ....


A:Access Query Question

Read other 6 answers

I have a database with a form that has 5 blocks of events. Each block has an id, date, and a drop down list to choose a company. So there are 5 fields for the company. It's a scheduling form, that is what I had to make 5 blocks with 5 fields. I need a query that will search for one company and list all blocks for that company whether it is in block 1,2,3,4,or 5 for a particular date. It could be repeated in either of the blocks for any of the dates. Any ideas? I wrote a query that combined all the block companies together, but it lists all the companies in the blocks and not just the one I need.

A:Access Query Question

angiej, the fact that you are having difficulty says that your Data Structure is not correct, is your Form based on just 1 Table?

Read other 3 answers

This may be easy and I am just making it harder in my pea brain or something, but I have a database with COUNTRIES SHIPPED TO and order dates right?
I want to beak down the countries by "USA" and then ALL OTHERS and have a count of PER MONTH SALES to each group. Then I want to make it into a chart.

I have tried multiple queries and figuring this out by my brain is fried. Anyone have a solution?

A:ACCESS query question

Welcome to the forum.

I would look into PivotTables and PivotCharts. It is a view you can switch to from your "View" menu. I works great for summarizations. If you have not tried these out let me know and I will provide further assistance.

Read other 2 answers

I need to get all the records for two different criteria

ReqDate_Fld between [Report Start Date] and [Report End Date]


RetDate_Fld Between the same dates above

putting it in the query twice doesn't work.

The RetDate_Fld can include records where the request date ReqDate_Fld is outside the start and end date criteria.

Example item criteria for ReqDate is between 2013-04-01[Report Start Date] and 2014-03-31 [Report End Date] and I have an item returned 2014-03-28 but it was originally requested 2011-03-21. The row doesn't get selected as it's outside the start/end date.

It requires AND or is it OR but danged if I know how to get it to select everything the was requested or returned between two dates.

A:Solved: Access 2010 selecting data from query based on two different sets of criteria

Read other 16 answers

I have a query setup in access and it prompts me for a ID number such PL37229.
how can i set it up so that all that i have to do is just enter PL37, and it will bring up all records that have those characters in that sequence.
right now in the criteria of the query i have
[Pack List ID is equal to]
Pack list ID is the field name. (PL37229)

A:Easy Access Query question

Read other 9 answers

If I can identify fields in a simple select query that need to have data flipped is there an easy way to do that?

A:Access 2003 Query Question

Read other 11 answers

When you build a query and you want to search a specific field, a Name Field, can you have the query find items that contain the search criteria?

I would enter [Enter Search Criteria] in the field for the Query.

I would like the query to search and select all records that contain the search criteria similar to a find used in records searches.

Not a match of the 'whole field' but matches that contain the search criteria in 'Any part of the field'.

If the search criteria is Smith for example, all records that contain that would show up:


They would all come up.

Is this possible?


A:Access 2000 Query Question

Do a search in Access for the word Like:

Like Operator
Compares a string expression to a pattern in an SQL expression.

expression Like &#8220;pattern&#8221;

The Like operator syntax has these parts:

Part Description
expression SQL expression used in a WHERE clause .
pattern String or character string literal against which expression is compared.
You can use the Like operator to find values in a field that match the pattern you specify. For pattern, you can specify the complete value (for example,

Like &#8220;Smith&#8221, or you can use wildcard characters to find a range of values (for example,
Like &#8220;Sm*&#8221.

In an expression, you can use the Like operator to compare a field value to a string expression. For example, if you enter

Like &#8220;C*&#8221;in an SQL query, the query returns all field values beginning with the letter C. In a parameter query , you can prompt the user for a pattern to search for.

Read other 2 answers

I'm developing a database for work and need to write a query to search a table and pull up the info in the table.
Right now I have:
SELECT Catalogue_Number, CD_Name, CD_Description
WHERE CD_Name like SearchBox;

As it is when I hit the query button it pops up a searchbox and I have to enter in the exact info I'm looking for. I'd like to just be able to enter in part of the cd_name (Ex: Instead of "Label Factory Deluxe", I'd like to type in "Label" and get any Record that has Label in the CD_Name field

Any help would be appreciated.

A:Solved: Access Query question

Read other 7 answers

I am trying to query records that are sixty days old or more based on an "arrival date" field and today's date. For example, if an arrival date is 12/12/2004 the query should return this record and any other that are more than 60 days old. Does anyone have a suggestion for the critera?

A:Solved: MS Access Query Question

Read other 8 answers

Please could someone help me. I am creating a query in Access 2007 from a table. I am a relatively new user. What I am trying to do is:
Column 1 Column 2

I want B to be written in Col and and B. Where B is in Col 2 and preceded by an A, it will type the A that is in Col 1 and i do not want this A to be displayed. I have written: Not "BT" and ="PROPHECY" but it is not working. Please can someone help. Thank you very much.

A:Access 2007 Query Question

Danielle, welcome to the Forum.
You need something like this in a New Coulmn Heading
Output: iif([Field1] <> "BT", [Field1], [Field2])

Where Field1 is the name of the field in Column 1 and Field2 is the name of the field in Column 2

Read other 2 answers

I want to create a query where only the fields that have values in them, such as text will be included in the query.

Like a movie database, and only the titles and descriptions will show up that have a value in the description field.

A:Access 2000 Query Question

Let me add that it's not just a certain word or value, but it could be any value in the that field... is that possible

Read other 2 answers

I am trying to build an expression that will display only sales that are more than 30 days prior to the current date. I have tried using the DateAdd function like this:

I have also tried using the expression <Date()+30. Nothing seems to work. Any help would be greatly appreciated!


A:Access 2003 query question

Well, using the expression <DateAdd("d",-30,Date()) in the SQL WHERE clause should find all records with dates more than 30 days ago. In SQL the WHERE clause should look something like: WHERE Mytable.Mydate < DateAdd("d",-30,Date())

In the query builder grid (Query by Example), or in 'Filter by Form' this is the equivalent of typing: < DateAdd("d",-30,Date()) in the criteria cell for field Mydate.

Obviously substitute your table and field names for Mytable and Mydate.

All this assumes that your field is defined in the table design as a date/time data type. If it is defined as data type text then you must also use the CVDate() function - eg: < DateAdd("d",-30,CVDate(Date())) - be aware thought that this will return an error if the field does not hold text that is a true date in a valid date format. To protect against this you can use an expression that uses IsDate() to check for valid dates before using CVDate(), for example:

In SQL: WHERE IIf(IsDate([Mytable].[Mydate])=False,False,[Mytable].[Mydate]<DateAdd("d",-30,Date()))=True

In the QBE grid type: IIf(IsDate([Mytable].[Mydate])=False,False,[Mytable].[Mydate]<DateAdd("d",-30,Date())) as an expression in the 'Field' cell and type: True in the 'Criteria' cell.

I don't have Access 2003 so my some of my terminology may be out of date, but the SQL should work.

Hope this all makes sense to you!


Read other 3 answers

I have information in tables and when I query the tables some records do not appear. What causes that to happen and how do I solve it? I have written specific queries just to get that record and it does not even show up.

A:Solved: Access Query Question

Read other 8 answers

Is it possible to have a table with a column for "name" and a column for "Prior Year Eval Rate" and then in a query, everytime a particular name shows up in the table it will find the "Prior Year Eval Rate" last recorded for that name? The example below is showing a table of information going to folks and I want to pull what the prior year eval rate was from the Eval Review Date of 1/1/2012.

Eval Packet to Manager qryDate to HRCOS ReasonEmp NameDept FromEval To ManagerEval Review DateEffective DateEval PayDateEval to Director DateCommentsRN Demographic SurveyPrior Year Eval Rate
Annual ReviewRhoda Meza61412/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewAnita Stowers66812/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewAmanda Machen76212/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewMichelle Breaud75512/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewTerra Garrison75512/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewAllison Stephens75512/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewNancy Brock75212/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewMelynda Williams75212/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewDaniel Harbin66812/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewSherri Moore621112/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewSallie Heard61212/3/20121/1/20132/3/20132/21/20131/28/2012

Annual ReviewSusan Kelley61112/3/... Read more

A:Solved: Access DB Question Regarding Query

Read other 13 answers

I've got a database that exports queries via macros into Excel. Included in this is - for example - the name of a consultant and the number of hours he's worked.

Alas, while the query in Access displays the first and last name of the consultant for the ConsultantID field (which is the number field that's related to the ConsultantID unique number field in the Consultants table), it shows only the ConsultantID as a number after export.

To remedy this, I put in another column in the query, which is basically Expr1:[Consultants]![FirstName] & " " & [Consultants]![LastName]. This works fine for queries in which the table containing the name (be it consultant, customer, etc) is related to only one other table in the query. However, when the name table is related to two tables that are already used in the query, I get the error:

The SQL statement could not be run b/c it contains ambiguous outer joins. To force on the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement.

I gotta admit: I'm not sure what that would mean here. I tried creating a seperate query that combined the names, and then referenced that query in the query I was to export, but no dice: same error.

Thanks for any guidance!

A:Solved: Access 2003 query question

Read other 9 answers

I just created a query for a one table database. I'm trying to find the top and bottom values in one query for one value. I can manage to organize it by ascending and taking one top-value, but I can't manage to get top and bottom simultaneously. I was wondering how one would go by doing that, and I'm in Query Design view. Thanks for anyone that can help me out.

A:Access 2003 Top-values Query Question

Nevermind, I've figured it out, thanks. This thread may be deleted as it is of no need anymore.

Read other 1 answers

Microsoft Access Query Question:

I am trying to figure out how to add up the total amount in donations for individual records through a Microsoft Access Query. Below is what the form looks like:


A:Solved: Microsoft Access Query Question

I am a little confusd about the query to total the Donations, your form is showing a totals field already.
Can you explain what the query will be for?
You can use Grouping with the Sum function in a query.

Read other 2 answers