Possible? Yes
Could you give us a bit more detail about the table structure?
I think what you want is to list each local authority (is that in the same
table) and a count of records by the value in each Age Group field. Do you
want both counts in one row or could you have the counts in separate rows.
The SQL statement to do this all in one row might look like the following:
SELECT [LocalAuthority]
, Count(IIF([ServiceUserAgeGroup]="<16",1,Null) as SU16
, Count(IIF([ServiceUserAgeGroup]="<65",1,Null) as SU65
, Count(IIF([ServiceUserAgeGroup]="<65+",1,Null) as SU66
, Count(IIF([ContactAgeGroup]="<16",1,Null) as Contact16
, Count(IIF([ContactAgeGroup]="<65",1,Null) as Contact65
, Count(IIF([ContactAgeGroup]="<65+",1,Null) as Contact66
FROM [SomeTable]
GROUP BY [Local Authority]
If you can't build the query in SQL View and can't figure out how to do this
in query design view with the help of the above, post back for an outline on
how to do this in query design view.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
In the same table I have a ServiceUser Age Group (<16, <65,65+) and a Main
Contact Age Group (<16, <65,65+). I want to create a query that sums ages
groups of the two fields and organised the by Local Authority
Is that possible?
Thanks
Ioia
.
