If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Group by month
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! |
#2
|
|||
|
|||
Group by month
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. -- Build a little, test a little. "n09" wrote: 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! |
#3
|
|||
|
|||
Group by month
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. -- Duane Hookom Microsoft Access MVP "n09" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|