I have a table Table Children which records different vaccinations received (children are identified by a unique ID). I want to create a query that will let me see how many P1, P2, P3, Measles 1 and Measles 2 shots were given per month. Each field (i.e. P1, P2, P3, Measles and Measles 2) are preceded by respective date fields i.e. Date of P1, Date of P2 etc.
I can create individual queries but I don't know how to do it for in one query:
SELECT Format([Table Children]![Date of P1], 'mmm yyyy') AS Month, Sum(Abs([Table Children]![P1])) AS Penta1_month
FROM [Table Children]
GROUP BY Format([Table Children]![Date of P1],'mmm yyyy');
But suppose I want to see a table which has the months in the column of the left and the different vaccination amounts in the row e.g.
I guess what you're looking at is (at the risk of getting kicked out of here for academic behaviour, does one get kicked out for academic behaviour) the "normalized" solution for you database design.
Your table should be child,type of shot, date.
You might want to transform your current table in the table I suggest by appending (union) the queries to each other. You might even find a query, (or a report) that reproduces your current table structure from the structure I suggest.