Query does not count all occurrences

MarieD

Registered User.
Local time
Today, 10:11
Joined
Dec 9, 2009
Messages
53
Can you please tell me why this query will not count all of the occurences that exist in a quarter. The query counts only one occurrence of each date, ie., if there are two or more occurrences of 3/24/2009, the query only counts one occurrent vs, two or three occurences.

SQL:
SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(([DateofProcedure]) In (9,10,11),"Quarter 1",IIf(([DateofProcedure]) In (12,1,2),"Quarter 2",IIf(([DateofProcedure]) In (3,4,5),"Quarter 3","Quarter 4")))
HAVING ((([Pacer QA tbl].PacerorICD)="ICD") AND (([Pacer QA tbl].NewImplantorGeneratorChange)="New"))
ORDER BY [Pacer QA tbl].PacerorICD;

The attached file ICD New by Quarter qry is from the sql above.

The attached file ICD New qry is from the SQL below:

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange
FROM [Pacer QA tbl]
WHERE ((([Pacer QA tbl].PacerorICD)="ICD") AND (([Pacer QA tbl].NewImplantorGeneratorChange)="New"));
 
For a start you query does not include a COUNT column

Add a new column in your query

Cnt:1

Then count this column

David
 
That does not make the query select all of the occurrences that have the same date. This is only counting what the query selected.
 
You have three grouping levels here date, NewImplantorGeneratorChange and PacerorICD it will give you a sub total at each group interval. If you change the group by in the PacerorICD to Where and do the same for the NewImplantorGeneratorChange change this to a where it should work

David
 
You need to use Count(*) or Count(fieldname) and this will give a count of all records within a grouping.

Brian
 
I am editing the SQL bringing in the quarters, correct?
This is the sql changing per your suggestion (I think)
SELECT [Pacer QA tbl].DateofProcedure, IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
WHERE ((([Pacer QA tbl].PacerorICD)="ICD") AND (([Pacer QA tbl].NewImplantorGeneratorChange)="New"))
GROUP BY [Pacer QA tbl].DateofProcedure
ORDER BY [Pacer QA tbl].PacerorICD;

But this results in the following error:
How do I correct this error?

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)
You tried to execute a query that does not include the specified expression as part of an aggregate function or grouping.

Possible cause:
  • You did not enter an aggregate function in theTRANSFORMstatement.
 
Try creating a seperate query that uses the nested iff statement to determine the quarter along with the other fields. Then create a totals query based on this query.
 
The query is not returning all of the data. Don't I have to resolve that before I try to count? There are more than one occurrence of date 3/24/2009 but the query only returns one occurrence. How do I resolve that?
 
As you are grouping on the date it will not show all of the records for that date, but it can count them.

Brian
 
Are you sure you are not using selet distinct in your query. Create a new query and bring in the date field only and sort ascending. Then view in datasheet mode. Can you see duplicates?

Next add another column Cnt:1 and group by date and count cnt. Are you getting the correct sub totals.

Keep adding the fields and the where conditions testing the results each time.

David
 
This sql brings in 60 records:
SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange
FROM [Pacer QA tbl]
WHERE ((([Pacer QA tbl].PacerorICD)="ICD") AND (([Pacer QA tbl].NewImplantorGeneratorChange)="New"));

This sql brings in 51 records:
SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr
FROM [Pacer QA tbl]
GROUP BY [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange
HAVING ((([Pacer QA tbl].PacerorICD)="ICD") AND (([Pacer QA tbl].NewImplantorGeneratorChange)="New"))
ORDER BY [Pacer QA tbl].PacerorICD;

The missing data in the sql that brings in only 51 records are the dates that are the same. Please show me where to change the sql that brings in 51 records.
 
The purpose of Grouping is to put fields with the same value together, therefore having only 1 record for that value or if many fields are grouped that group of values.

Brian
 
So what is needed in the query to make it return the 60 records. I need a report that will reflect the count of 60. I'm sorry but I am just lost with this. I know your information is correct but I just do not understand what to adjust with the query to make it return the 60. Do you mind spelling it out in detail?
 
If you want to show all of the records then you cannot use Group By.

Why are you using Group By?

Brian
 
This is a very large db. I need to pull out the procedure date, whether or not the procedure is ICD or Pacer, whether or not the procedure was New or Changed and to group the date of procedures by quarters, with quarter one starting in the months of 10, 11 and 12. If you can tell me an easier way to accomplish this, please do.
 
If it is just a case of showing all of the records by qtr on the Report then use Report grouping


Brian
 
I don't have to show individual records, but the count does have to include all records. That is my problem. I cannot get the report to show me that there are 60 records. The report continues to show that there are only 51 records. I know you must think that I am a complete dummy with this, but I thought that a report had to come from a query and the report only shows what the query is pulling. When the query is not pulling all of the records, how do I make a report show the correct count?
 
Ok
Did you try Count(*) as I suggested earlier?

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([DateofProcedure]) In (10,11,12),"Quarter 1",IIf(Month([DateofProcedure]) In (1,2,3),"Quarter 2",IIf(Month([DateofProcedure]) In (4,5,6),"Quarter 3","Quarter 4"))) AS Qtr, Count(*) as NumberOfRecords



Brian
 
God Bless you! I could not figure out where to place the Count(*). This is working now. Thank you so very much. I have a lot of work to do now!!:)
 
If you only want to group by quarter then why include the date field in the group by clause?

Remove this from the list of fields. You can still refer to it even if it is not in the list.
David
 

Users who are viewing this thread

Back
Top Bottom