Access 2007

leighms · Sep 28, 2011

I am in the process of setting up a data base on people that join our organisation.
I need to produce a report that lists by area and by age group as below;
Branch--------0-10-----11-20-----21-30-----etc
ABC------------8---------28--------20
DEF------------12--------26--------25
GHI------------14--------22--------22
I know I will need to do a cross tab report, but I can't seem to work out how to get the data to sort by the age groups first.
So basically I need to know how to sort the data into the age groups, then do a cross tab report from there

Regards

vbaInet · Sep 28, 2011

Now you're in the process, complete the process before thinking about the report. If the table design and relationships are right, everything else will fall into place at a later stage.

leighms · Sep 28, 2011

Thanks, I have the data base set up, but I just can't seem to figure out how to create a query that will generate give me a total of people that join by Branch and then by age group, if I can get this sorted I can then do a cross tab query to get the below report

vbaInet · Sep 28, 2011

You can do it all in a crosstab. Here's a little trick for you:

Code:

TRANSFORM Count([[COLOR=Red]AgeGroup[/COLOR]]) AS CountOfAgeGroup
SELECT [[COLOR=Red]Branch[/COLOR]]
FROM [COLOR=Red]TableName[/COLOR]
GROUP BY [[COLOR=Red]Branch[/COLOR]]
PIVOT Replace(Replace(Partition([[COLOR=Red]AgeGroup[/COLOR]],0,DMax("[[COLOR=Red]AgeGroup[/COLOR]]","[COLOR=Red]TableName[/COLOR]"),11), " ", ""), ":", " - ");

Amend the bits in red.

leighms · Sep 29, 2011

thanks for your help, now sorted

vbaInet · Sep 29, 2011

You're welcome!

Access 2007

leighms

Registered User.

vbaInet

AWF VIP

leighms

Registered User.

vbaInet

AWF VIP

leighms

Registered User.

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread