Query for calculations

[ASunny]

Hi,

I want to use a query to calculate %uptake of vaccinations by year group in each school. I have data for 628 schools, each with 7 year groups (0-6).

The table I'm working with is thus:

VaccineSessionID - Primary Key - Autonumber
SchoolID - Number - Unique School identifier (1-628)
WeekofVisit - Number
YearGroup - Number (from 0-6)
StartDenom - Number (i.e. number of children in that year group, for that school)
Consents - Number of kids that got consent
TotalVaccine1 - Number of kids with consent that got vaccine 1
TotalVaccine2 - Number of kids with consent that got vaccine 2.

My problem is that each year group for a particular school may appear say 4 times, as there were multiple visits to each school and year group.

I want the calculation to make a sum of TotalVaccine1 and TotalVaccine2 over all the entries for a year group for a school (accumulative by WeekofVisit), and divide it by the total consent for that same year group. The consent appears as many times as the year group, but it is not a cumulative total, it is a constant total for that year group. E.g.....

SCHOOLID | WEEK | YEAR | CONSENT | VAC1 | VAC2 |
35--------|--45---|---1 ---|----30-----|--20---|---1--|
35--------|--50---|---1 ---|----30-----|--4----|---0--|
35--------|--51---|---1----| ----30----|--3 ---|---2--|


So i want the query to total VAC1 (=27) and VAC2 (=3) then add those together (=30) and then divde by consent (=30 NOT 90). I want it to be able to do this for each school, and school year. :banghead:

I'm not sure if this is even possible actually! I'm quite new to Access... Sorry if this doesn't make a bit of sense!

Thanks in advance.

 

[GSSDevelopment]

Hi there!

There are 2 solutions I can think of off the top of my head:

Solution 1
If the "consent" field is truly static (per schoolId/year), then you'll probably want to move it to a separate table for consent. You can then join the tables based on the schoolId/year, SUM the Vac1 and Vac2 fields, then divide by the joined consent value. This would be the more normalized approach.

See SQL created for replicated MySQL tables:

SELECT `vax`.`schoolId`, `vax`.`year`, SUM(`vac1`) AS `vac1Total`, SUM(`vac2`) AS `vac2Total`, (SUM(`vac1`+`vac2`)*100/`consent`) AS `pctCompletion`
  FROM `vax`
  LEFT JOIN `consent` ON `consent`.`schoolId` = `vax`.`schoolId` AND `consent`.`year` = `vax`.`year`
  GROUP BY `vax`.`schoolId`, `vax`.`year`;

Note: From this set up, it sounds like you may want to add a field like... calendar year, so you can use the same thing for multiple calendar years. Then you'll want to add a calendar year column to the consent table, too, and add that to the join criteria.


Solution 2
The other solution is to maintain your current table structure and use this calculated field: (SUM(Vac1+Vac2)*100)/AVG(Consent).
Just be sure you group by schoolId and year (and calendar year, if added), as noted above.



Hope this helps!