[Help] Breaking down Gender into Male & Female

[Wiredex]

Access 2007

I need help with creating a query to show the number of visitors by countries, further breaking it down to gender distribution (i.e. by number of male and number of female visitors for a particular country).

An example showing sample data:
---------------------------------------
Country | Number of Visitors | F | M |
---------------------------------------
XXXX | XXX | X | X |

I searched around the web for help and couldnt find one after two days.
I figured out how to do that Country and Number of Visitors.
For example

Singapore | 7 |
But how do i break it up to Female and Male so it will show like
Singapore | 7 | 5 | 2

 

[Galaxiom]

You need to explain the record structure for source data.

 

[Wiredex]

What do you mean? i'm quite new to this

 

[Galaxiom]

What does the X value mean in the source data?

 

[Wiredex]

X is an example

FOr example i want it to appear like


Country Number of Visitors F M
Singapore 7 5 2

 

[namliam]

Either make it a crosstab query or use an IIF to split the counts between male and female.

 

[Wiredex]

and how do i do that? How can i use IIF to split the Gender into Male and Female?

 

[Galaxiom]

Do you want to combine Singapore | 7 | 5 | 2 | into a single record as "Singapore 7 5 2" ?

 

[gemma-the-husky]

you dont want F and M separately

you just want a single yes/no field (or a even a single character field)


then you can just do a simple totals guery (groupby)


if you have two separate fields, how do you stop a user checking neither or both. If you just have a single field then you don't need to worry about this.

BUT this will then show as

singapore Yes (ie male) 5
singapore No (ie female) 2

----------------
thinking again, yu can get what you want with a bit of tinkering, but I think you would need

a) a query to select/count just males
b) a query to select/count just females
c) another query to join them together

and you would then have to consider the position that ome of the values may be zero, so you would also need another query to include all the countries


ending up with

country query - linked to all males query, and linked to all females query.

that would work, and give you the layout you want.

 

[Galaxiom]

You seem to just be showing us two examples of the output with X and with numbers.

What do the original source records look like?

 

[Galaxiom]

Make a query with just Gender and Country.
Group By Gender and Country. Add a third field as a Count on one of them.

Then add them together for the total field in a crosstab query based on this one. You can combine them together in a subquery structure.

 

[Wiredex]

I'm sorry but im a beginner i don't really understand what you mean, do you have a step-by-step guide?

 

[namliam]

IIF(YourField='MALE',1,0)
IIF(YourField='FEMALE',1,0)

That splits it into two columns, then 'simply' sum both columns and group by the first and Count(*) the second.

 

[Wiredex]

ill update again whether i got it right! thanks for the help. namiliam just to clarify.
In query design, i add IIF(YourField='MALE',1,0) to one of the field and IIF(YourField='FEMALE',1,0) to another field, Group by the first column and Count the 2nd column. am i right?

 

[Wiredex]

Solved, thanks guys.