hi guys,

i have an access query like this -

SELECT master_familiy.c1_1_4, master_persons.c_gender, Count(*) AS Expr1
FROM master_persons LEFT JOIN master_familiy ON master_persons.family_id=master_familiy.family_id
WHERE master_persons.c_gender="1" Or master_persons.c_gender="2"
GROUP BY master_familiy.c1_1_4, master_persons.c_gender;

This basically displays the the amount of males & females in a particular City. master_family table has family details and the CITY name
where that family comes from. master_persons has each individuals data.

So i have to join the 2 tables to group by CITY.


The result set is like below.

CITY GENDER POPULATION
------- -------- ------------
London M 4534534

London F 4353453

NY M 4534566

NY F 3443534

Can u help me to get a query result like below. I don't know if it's possible.


CITY MALE FEMALE
------- -------- ------------
London 4354353 4534534

NY 3443534 3443534

This can help me save a lot of paper :)

Thanks

You should be able to get that result by applying a crosstab query to your query. I'm curious about the WHERE clause that restricts to 2 different genders. How many are there? ;)

thanks mate

more than 2 genders due to data entry errors :)

Can you change the data entry process to eliminate them? I'd use a limit-to-list combo, an option group, or perhaps before update code to stop bad data from getting in to begin with.

yeaj i shud do that - :) thanks again

No problem, and welcome to the site by the way!