Counting numbers of shots for patient in Access 2007

[mishmish3000]

Hello! I have an Access 2007 db in which I'm keeping track of patients getting their childhood vaccinations. I have a report with a subreport; the report shows the child's name, address, etc. and the subreport shows the vaccinations and the vaccination dates. On the subreport, I'd like to show the number of each specific vaccination the child's received and if it's under 2, then format it conditionally to catch the nurse's eye. The table is called Tbl120DayCHR; the field for the vaccination is called antigen, and the day the vaccination was administered is named shotdate. A child may have, for example, 3 vaccinations of antigen MMR (on 3 different dates), 1 vaccination for polio (with 1 date), and 2 vaccinations for hepatitis B (with 2 dates). In that example, we'd want to conditionally format the hepatitis b antigen bright red or something so the nurse reviewing the report will know the child needs 1 more hep b shot within 120 days. Any help would be greatly, greatly appreciated!!

[HansV]

Welcome to Eileen's Lounge!

You wrote that you'd like to format an antigen if the number of vaccinations is under 2. In your example, that would hold for polio (with 1 vaccination), not for hepatitis b (2 vaccinations), or am I missing something?

[mishmish3000]

I need to clarify... I'm tired. Anyway... how about if we say, conditionally format the antigen name (MMR, Polio, Hep B) if the number of shots is equal or less than 2? So for the example I used, the MMR is fine but the polio and the hep B need to be red. The way you'd count them is by how many shotdates are associated with each antigen... I think. But I'm not sure. does that make any more sense? Good catch, Hans!
So the report would ideally look like this:

child 001 name address city state zip
antigen: MMR shotdate: 1/1/10 shotdate: 2/1/10 shotdate: 3/1/10
antigen: polio shotdate: 1/1/10
antigen: hep b shotdate: 1/1/10 shotdate: 2/1/10

[HansV]

Thanks for the explanation. How are the antigens identified? Is it a text field or a number field (linked to another table with the names)?

[mishmish3000]

Ideally it would be a normalized database with the antigen names in another table, but what I have to work with right now is one table--and the antigen field is a text field. The shotdate field is a date/time field. So Child 001 may have numerous records, each listing an antigen and the date it was administered.

PatientNo PatientFName PatientLName Antigen Shotdate
001 Albert Einstein MMR 1/1/10
001 Albert Einstein Polio 1/1/10
001 Albert Einstein Hep B 1/1/10
001 Albert Einstein MMR 2/1/10
001 Albert Einstein MMR 3/1/10

etc.
On the report, the main part of the report shows Albert's name, age, address. On the sub report, I have the antigens listed in alphabetical order and then within the antigen list, sorted by most recent date first.

[HansV]

Sorry to keep on asking questions. Is the subreport grouped by antigen?

[mishmish3000]

Hi there! Yes, the sub report is grouped by antigen. The design view looks like
ReportHeader
PageHeader
AntigenHeader
Detail
Antigen Shotdate
PageFooter
ReportFooter

[HansV]

Try this:

- Create a query in design view based on Tbl120DayCHR.
- Add the PatientNo, Antigen and Antigen fields (yes, Antigen twice)
- Click the Totals button on the ribbon.
- Change the Total option for the last column to Count.
- This query counts the number of shots per patient and per antigen.
- Save it as qryCount.

- Create a new query in design view based on Tbl120DayCHR and on qryCount.
- Join the table and query on PatientNo and Antigen.
- Add all fields from the table to the query grid, plus the CountOfAntigen field from the table.
- Save this query as (for example) qry120DayCHR.

- Open the subreport in design view.
- Set its Record Source to qry120DayCHR.
- Select the controls you'd like to highlight.
- Click Conditional in the Design tab of the ribbon.
- Select Expression Is from the dropdown under Condition 1.
- Enter the following expression in the box next to it:

[CountOfAntigen]<=2

- Select the formatting that you want, for example red as font color.
- Click OK.

Hopefully, this will do what you want.

[mishmish3000]

Hans, I'll certainly try what you wrote... I'll let you know if it works! Thanks SO much! MishMish3000

[mishmish3000]

Hans, you're great! it worked!! Thanks!!!!!!!

[HansV]

Glad it worked! Thanks for the feedback - it's always useful to know whether a reply worked.