spartansFC
Programmer
Hi
I've created a query that lists children at various nurseries, the week commencing dates for that month, i need to create a crosstab or group query to show a summary of the nurseries (row heading), month/year (column heading) and the unique count of each children for that month.
ChildId childactive Status nurseryShort fullname Dates DateMonth DOB age
1149 TRUE Existing place Nursery1 Child1 02-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 09-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 16-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 23-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 30-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 07-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 14-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 21-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 28-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 04-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 11-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 18-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 25-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 02-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 09-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 16-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 23-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 30-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 06-Aug-12 Aug-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 13-Aug-12 Aug-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 20-Aug-12 Aug-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 27-Aug-12 Aug-12 12-Feb-09 3
994 TRUE Existing place Nursery1 Child2 02-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 09-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 16-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 23-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 30-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 07-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 14-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 21-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 28-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 04-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 11-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 18-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 25-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 02-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 09-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 16-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 23-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 30-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 06-Aug-12 Aug-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 13-Aug-12 Aug-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 20-Aug-12 Aug-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 27-Aug-12 Aug-12 06-Sep-07 4
I would like the summary to be shown as below:
Nursery Apr-12 May-12 Jun-12 Jul-12 Aug-12
Nursery1 2 2 2 2 2
So is it best to create 2 queries and join them together, not that i know how to do that or is there a way in 1 query to group everything together.
Anyone have any ideas.
Michael
I've created a query that lists children at various nurseries, the week commencing dates for that month, i need to create a crosstab or group query to show a summary of the nurseries (row heading), month/year (column heading) and the unique count of each children for that month.
ChildId childactive Status nurseryShort fullname Dates DateMonth DOB age
1149 TRUE Existing place Nursery1 Child1 02-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 09-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 16-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 23-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 30-Apr-12 Apr-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 07-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 14-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 21-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 28-May-12 May-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 04-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 11-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 18-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 25-Jun-12 Jun-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 02-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 09-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 16-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 23-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 30-Jul-12 Jul-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 06-Aug-12 Aug-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 13-Aug-12 Aug-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 20-Aug-12 Aug-12 12-Feb-09 3
1149 TRUE Existing place Nursery1 Child1 27-Aug-12 Aug-12 12-Feb-09 3
994 TRUE Existing place Nursery1 Child2 02-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 09-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 16-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 23-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 30-Apr-12 Apr-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 07-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 14-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 21-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 28-May-12 May-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 04-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 11-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 18-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 25-Jun-12 Jun-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 02-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 09-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 16-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 23-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 30-Jul-12 Jul-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 06-Aug-12 Aug-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 13-Aug-12 Aug-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 20-Aug-12 Aug-12 06-Sep-07 4
994 TRUE Existing place Nursery1 Child2 27-Aug-12 Aug-12 06-Sep-07 4
I would like the summary to be shown as below:
Nursery Apr-12 May-12 Jun-12 Jul-12 Aug-12
Nursery1 2 2 2 2 2
So is it best to create 2 queries and join them together, not that i know how to do that or is there a way in 1 query to group everything together.
Anyone have any ideas.
Michael