The vaccination db returns...

[mishmish3000]

Greetings! Thanks to all who helped me earlier with questions about reporting in a vaccination database. Now I have a new request for help, in the same database. In the db, there's a report called RptBaseCHR. This report has a subreport embedded in it called RptsubreportforQry120DayCHR. The main report shows patient data, while the subreport shows individual shots the patient has recieved. I have the subreport counting the number of shots and that looks great. I also have the subreport showing DTAP, which is one of the vaccinations, listed as the first shot, since it's the main one we look at. Thanks for all the help I've received so far!

What I need is this: how can I say, in SQL or in a query view, "Show me only children (PATIENT_NO) in the report where the oldest SHOTDATE is GT than the 120DaysAfterDOB date"? What we need to see are the children who haven't gotten their 1st DTAP shot within 120 days of birth.

Field names are: PATIENT_NO for each child; DOB for date of birth; 120DAYSAFTERDOB is a calculated field, ANTIGEN is the vaccination name field, and SHOTDATE is the date of each time the child got their shots for that particular antigen. DTAP is the particular antigen we're focusing on. The table name is CHR. I listed the report names above.

They may have multiple DTAP shots, though, so you have to first find the oldest SHOTDATE for the DTAP shot for the patient. Then, somehow--this is where the coding gurus should step forward!--we compare that date with the 120DaysAfterDOB date. If they got the first DTAP shot within 120 days, we're cool--we don't need to see their records in the report. If they didn't, however, that means they are highly likely to have missed many of their important childhood vaccinations, and we need to see their records in the report.

Please let me know if there's a good way to do this. Of course, if I had been able to structure the data more, this wouldn't be much of an issue, but unfortunately, I've just this CHR table to deal with.

Thanks!

[HansV]

Start query design.
Add the CHR table.
Add the PATIENT_NO, DOB, SHOTDATE and ANTIGEN fields to the query grid.
Enter >[DOB]+120 in the Criteria line under SHOTDATE
Enter DTAP in the Criteria line under ANTIGEN.
Click the Totals button (the Greek letter Σ).
Set the Totals option for PATIENT_NO and DOB to Group By, for SHOTDATE to Min and for ANTIGEN to Where.
This query should return the patients you're looking for.

[mishmish3000]

By George, I think it worked!
Now I just want to run it by the end users to see if they think the number of patients the query returns is reasonable.
Thanks!!
MishMish