Will have to construct a field with calculation to assign range value to each record then use that field in CROSSTAB query. No one age greater than 20 years?
Like:
SELECT *, DateDiff("yyyy", [Birthday], Date()) AS Age, Switch([Age]<=5, "0-5 years", [Age]<=10, "6-10 years", [Age]<=15, "11-15 years", [Age]>15, "16-20 years") AS AgeGroup FROM tablename;
Now use that query in a CROSSTAB. If no records fall into a group, group will not have a row in the CROSSTAB.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
something like
Code:SELECT (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+1 & " - " & (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+5 & " years" AS [Age Range], sum(iif(Gender="Male",1,0)) AS Males, sum(iif(Gender="Female",1,0)) AS Females FROM myTable GROUP BY (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+1 & " - " & (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+5 & " years"
Nice, Ajax, emulating CROSSTAB occurred to me but did not pull it together, wasn't seeing the GROUP BY expression. Again, if no records fit into a particular category, won't be a record.
Thanks orange, learned a new function.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
new function for me as well
Thanks all !!
Calculate age and store it in a new field, then applied Ajax's code.
It works.
Next I want to try Partition function since it looks more simple and easier to read, but still don't know how to separate the result between Male & Female
you shouldn't store things like age - next year it will be different - my suggestion calculates the age on the fly
my suggestion does that as well - I've used your field names, so you just need to change the tablename to whatever your table is called and run it to produce the result you requiredbut still don't know how to separate the result between Male & Female
Thanks Ajax,
I've tried your code and it works perfectly.
but still curious to use Partition....
Not tested, but I would suggest something like this
Code:SELECT Partition(DateDiff("yyyy",Birthday,Date()),0,100,5) & " years" AS [Age Range], sum(iif(Gender="Male",1,0)) AS Males, sum(iif(Gender="Female",1,0)) AS Females FROM myTable GROUP BY Partition(DateDiff("yyyy",Birthday,Date()),0,100,5) & " years"
I had this problem and I searched for maybe 2 hours to find a solution ! you are a life save thanks !