Unique Count Query

[spartansFC]

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

 

[SkipVought]

hi,

transform Count(*)

select  nurseryShort

from [YourTable]

group by  nurseryShort

pivot DateMonth

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JonFer]

Start with a query that returns the rows you want to count - unique children per month per nursery:

Select Distinct NurseShort, DateMonth, ChildID
From YourTable

Then use that query as the source for your crosstab query with NurseryShort as the RowHeading, DateMonth as the ColumnHeading, and Count(*) as the Value.