COUNT Query problem Please help! XD

[aphex]

Hey,

Im implementing changes to a medical database and have come accross a query problem.

I have one main table named Patient Info and from that I am wanting to report on anti biotic treatments with the 3 fields AntiBioticRegime (can be one of 5 drop down choices), AntibioticStartDate and AntibioticFinishDate.
On my reports page I have a user defined date range function which I call within each query.

The query Im trying to create is a count query to count the recurrences of AntiBioticRegime within the user defined date range. When I use the standard access select and count expressions it only counts each occurence of the differing dates and does not give me overall totals for each type of anti biotic regime. Below is the SQl in the query previously talked about...

SELECT PatientInfo.Antibioticregime, PatientInfo.AntibioticStartDate, PatientInfo.AntibioticFinishDate, Count(PatientInfo.Antibioticregime) AS [No]
FROM PatientInfo
GROUP BY PatientInfo.Antibioticregime, PatientInfo.AntibioticStartDate, PatientInfo.AntibioticFinishDate
HAVING (((PatientInfo.Antibioticregime)>"1") AND ((PatientInfo.AntibioticStartDate)>=[Forms]![Report Screen]![dateFrom] And (PatientInfo.AntibioticStartDate)<=[Forms]![Report Screen]![dateUntil]));

Any help on this would be extremely useful as I am stumped

 

[SRAB]

Try it in two passes.
Do your select query first, send that to a temp table.

SELECT PatientInfo.Antibioticregime, PatientInfo.AntibioticStartDate, PatientInfo.AntibioticFinishDate INTO TEMP_TABLE
FROM PatientInfo
WHERE....

Then count the values of the Antibioticregime. All the values will be within your time period, but not tied to it.

SELECT Count(AntiBioticRegime) AS [Count], AntiBioticRegime
FROM TEMP_TABLE
GROUP BY AntiBioticRegime

 

[boblarson]

You don't need a temp table. You can run nested queries.

 

[SRAB]

You don't need a temp table. You can run nested queries.

Yup you're right. I like saving things out as a habit, I like to look at the results see if the "seem" right.

 

[boblarson]

That might be good to do in the start of the development stage, but if you can get away from temp tables it will keep the database from growing as quickly and needing more frequent compacting.

 

[aphex]

Decided to use the TEMP_TABLE method as the anti bitoic regime only accects about 10% of patients per year on the database so wont get too high in the next few years.. Also couldn't get the nesting to work, for some reason no temp relationship was being created when I was making up the new query, so dont know if it is a problem with primary keys or what. Either was thanks very much for both your help. Will stick with the temp table just now but in future will look at nesting query's etc.