Query does not count all occurrences

[MarieD]

Brian, in this query that you helped me with, can you tell me what to put and where to put it in the query to make it place a zero in the quarters that do not have data?

Returns the correct number of records in each Quarter.
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

 

[Brianwarnock]

Its not quite that simple.
If there is no data for a quarter there is no record to put the 0 in.

What you would need to do is construct a quarters table TBLQuarters with the field QtrNumber , 4 records 1 2 3 4

Then outerjoin this to your query on Qtrnumber = Qtr so that you pulled all records from TBLQuarters and matched records from the query, Select fields from the query except for QtrNumber instead of Qtr, and for the NumberofRecords code Val(NZ(queryname.NumberOfRecords,0))

All of the other fields will of course be Null


Brian

 

[MarieD]

Re: Forcing a by quarter query to show 0 when applicable

What is wrong with this? I am trying to make this query show a zero if the quarter does not have any data. An error message states the select statement includes a reserved word or an argument name that is misspelled or missing, or the puncutation is incorrect.

I created a new table with the fields named Qtrnumber 1, Qtrnumber 2, Qtrnumber 3, Qtrnumber 4. I then opened the relationship and joined these fields to the Qtr one Qtr Two Qtr Three and Qtr Four in my master table with all the data. I'm not sure if I followed the instructions correctly or not.

SELECT [Pacer QA tbl].DateofProcedure, [Pacer QA tbl].PacerorICD, [Pacer QA tbl].NewImplantorGeneratorChange, IIf(Month([DateofProcedure]) In (10,11,12),"Qtrnumber 1",IIf(Month([DateofProcedure]) In (1,2,3),"Qtrnumber 2",IIf(Month([DateofProcedure]) In (4,5,6),"Qtrnumber 3","Qtrnumber 4"))) AS Qtrnumber, Count(*) AS Val(NZ(ICDUpgradeQtrNumbertestqry.NumberofRecords,0))
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)="Upgrade"))
ORDER BY [Pacer QA tbl].PacerorICD;

 

[boblarson]

My guess is this line:

Count(*) AS Val(NZ(ICDUpgradeQtrNumbertestqry.NumberofRecords, 0))


is causing you problems.

Count(*) As SomeAliasNameHere

would be my thought.

 

[Brianwarnock]

No that should be
Val(NZ(ICDUpgradeQtrNumbertestqry.NumberofRecords, 0)) as aliasname

but I'm a bit lost on the rest of the code as I expected to see a join and didn't expect to see all of those IIf statements.

Brian