Counting checked boxes

[SaneN85]

Hi, I am new to this forum and was hoping that someone would be able to help me with my Access issue. I have a form/table set up to count the number of immunizations are given. The immunizations are set up as checkboxes and I need to be able to count the number of each separate immunization given by counting the checked boxes and returning a positive number. I have never done anything beyond the basic Access DB and have not used SQL or anything like that. I keep seeing a Sum function when I google my issue, but have not yet figured out where to put it.

I hope that this question is clear, but will be happy to clarify any part that doesn't make sense. Any help on this issue is extremely appreciated.

 

[dhookom]

A yes/no field stores either -1 for True/Yes/checked or 0 for False/No/Not Checked. You can create a totals query or use Sum() in a report since the field is numeric.

So, if you Sum() a yes/no field, you will get a negative of the count of Yes values. Use the Abs() function to change this to a positive count.

For instance in the Report Footer section of a report, your could count the number of checked boxes with a control source of:
=Sum(Abs([YourYesNoFieldName]))


Duane
Hook'D on Access
MS Access MVP

 

[SaneN85]

Maybe I am just missing something, but I am still unsure of where to put the =Sum(Abs([YourYesNoFieldName])). You say in the control source, but is this while I am in a query or is this just straight from trying to create a report?

 

[dhookom]

You haven't provided context regarding where you want to see the count. I assume this is a query but is this by patient or a total of a filtered query or what?

Generically, you can use:

SELECT Immunization, Sum(Abs([YourYesNoFieldName])) as NumOfYourFieldName
FROM [table with no name given]
GROUP BY Immunization;

Duane
Hook'D on Access
MS Access MVP

 

[SaneN85]

While the patients will be recorded in the record, the most important information is which immunizations are given. At the end of the month, or another specified time, I will need the total of each immunization given. This is sort of like an inventory per month. Preferably, there would be a totals row at the bottom of each of the categories (columns). I assume that the code you have written would be placed in as SQL, am I correct?

 

[dhookom]

So now your specification has changed to wanting the count of immunizations per month? We weren't aware of a date field.

How about providing a few sample records with actual table and field names and then how you want them displayed in your query?

We don't know if you have properly normalized your table structure or if you have committed spreadsheet by having different fields for different immunizations.

Duane
Hook'D on Access
MS Access MVP

 

[SaneN85]

Sorry I didn't include the information about the dates, because I thought it would just complicate what I am trying to learn on this forum, and that I would be able to figure it out on my own later.

I have attached the link to the Access DB. I have not done much with the DB, because it will not be useable unless I can come up with the solution to this problem. I am more than willing to revamp the format of the DB if the way it is done will not work for my purposes. I hope that the attachment will help clear up the confusion. I am not used to have to explain these type of issues on a forum.

 

[dhookom]

I would add a primary key to the table and not store both the DOB and Age since you can easily calculate age. Date is a reserved word since it is a function name. It is good practice to divide first and last names into separate fields.

Normalize your data with a union query [quniIms] (or better yet permanently). Your SQL view would be

SELECT [Date], [Patient Name], DOB, 'DTaP/DTP' as Immunization  FROM Immunizations WHERE [DTaP/DTP] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'IPV' as Immunization  FROM Immunizations WHERE [IPV] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'Pentacel' as Immunization  FROM Immunizations WHERE [Pentacel] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'PCV' as Immunization  FROM Immunizations WHERE [PCV] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'Rotatec' as Immunization  FROM Immunizations WHERE [Rotatec] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'Hep B' as Immunization  FROM Immunizations WHERE [Hep B] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'MMR' as Immunization  FROM Immunizations WHERE [MMR] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'Varicella' as Immunization  FROM Immunizations WHERE [Varicella] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'Hep A' as Immunization  FROM Immunizations WHERE [Hep A] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'MCV4' as Immunization  FROM Immunizations WHERE [MCV4] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'Tdap' as Immunization  FROM Immunizations WHERE [Tdap] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'HPV' as Immunization  FROM Immunizations WHERE [HPV] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'FLU' as Immunization  FROM Immunizations WHERE [FLU] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'TD' as Immunization  FROM Immunizations WHERE [TD] <> 0 
UNION ALL SELECT [Date], [Patient Name], DOB, 'HiB' as Immunization  FROM Immunizations WHERE [HiB] <> 0 ;

You can then get a count of each type of immunization with a simple totals query:

SELECT quniIms.Immunization, Count(quniIms.[Patient Name]) AS [CountOfPatient Name]
FROM quniIms
GROUP BY quniIms.Immunization;

Duane
Hook'D on Access
MS Access MVP

 

[SaneN85]

Thank you, it seems like it will work. Although, now that I have split up patient name into First Name and Last Name columns, I am unsure of how to put that into the simple totals query. I would definitely appreciate your help in rewriting that code to include the two columns. Thanks again!

 

[SaneN85]

Ok, I got it now. Thank you!!!!!!

 

[SaneN85]

It seems that I was very wrong about being able to figure out how to sort/report based on the date and get the number of each immunizations given in a certain time frame. If you (or anyone on this forum) can offer me help with that, I would greatly appreciate it. It just wasn't as simple as I'd hoped/thought. Thanks again.

 

[dhookom]

You should be able to use the union query [quniIms] as the recordsource for a report. Where are you having issues with this?

Duane
Hook'D on Access
MS Access MVP

 

[SaneN85]

I'm running into trouble using the union query because it will just pull out the name of the patient (whether first or last) and doesn't actually pull out the number of each immunizations given during a specified time. Please keep in mind that I'm a beginner in even using coding to assist with this. I do really appreciate all your help.

 

[dhookom]

Can you provide a specification of exactly what you want? For instance "I want the type of immunization and quantity between the dates of 7/1/2009 and 7/31/2009"

SELECT quniIms.Immunization, Count(quniIms.[Patient Name]) AS [CountOfPatient Name]
FROM quniIms
WHERE [Date] BETWEEN #7/1/2009# and #7/31/2009#
GROUP BY quniIms.Immunization;

This results in no patient names, only the count of each type of immunization between the two dates.

Duane
Hook'D on Access
MS Access MVP

 

[SaneN85]

Thank you so much, I have gotten it figured out from there. Now I'm having trouble with a separate issue and will post a separate thread about it. Again, I really appreciate your help on this.