Group by month

[n09]

Hey guys,
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.
P1 P2 P3
August 2009 20 35 40

thanks for the help!

 

[KARL DEWEY]

Your data collection is wrong.
The table should have these fields --
ChildID -
Type -
VacDate -

To get there use a union query --
SELECT ChildID, "P1" AS Type, [Date of P1] AS VacDate
FROM [Table Children]
WHERE [Date of P1] Is Not Null
UNION ALL SELECT ChildID, "P2" AS Type, [Date of P2] AS VacDate
FROM [Table Children]
WHERE [Date of P2] Is Not Null
UNION ALL SELECT ChildID, "P3" AS Type, [Date of P3] AS VacDate
FROM [Table Children]
WHERE [Date of P3] Is Not Null
UNION ALL SELECT ChildID, "Measles" AS Type, [Date of Measles] AS VacDate
FROM [Table Children]
WHERE [Date of Measles] Is Not Null
UNION ALL SELECT ChildID, "Measles 1" AS Type, [Date of Measles 1] AS VacDate
FROM [Table Children]
WHERE [Date of Measles 1] Is Not Null;

Then use a crosstab query.

 

[Duane Hookom]

If you have fields with names like [P1], [Date of P1], [P2], [Date of P2],...
then your table structure is un-normalized and IMO should be changed.
However, if you can't change the structure, then you can use a normalizing
union query

SELECT ID, "P1" as Vaccination, [Date Of P1] as VaccinationDate
FROM [Table Children]
WHERE [Date Of P1] Is Not Null
UNION ALL
SELECT ID, "P2", [Date Of P2]
FROM [Table Children]
WHERE [Date Of P2] Is Not Null
UNION ALL
SELECT ID, "P3", [Date Of P3]
FROM [Table Children]
WHERE [Date Of P3] Is Not Null
UNION ALL
SELECT ID, "M1", [Date Of Measles 1]
FROM [Table Children]
WHERE [Date Of Measles 1] Is Not Null
UNION ALL
SELECT ID, "M2", [Date Of Measles 2]
FROM [Table Children]
WHERE [Date Of Measles 2] Is Not Null;

Then create a crosstab query from the result of the UNION query. Set the
Column Heading to the Vaccination field, Row Heading to
Format(VaccinationDate, "yyyy mm"), and the Value would be Count of ID.