I have three tables in MSAccess 2011(query builder not allowed)
1- Member-(memid,gender etc) m:n with joining table member_event to
2- Event-(eventid,eventdate, etc) m:1 with
The query i want should show venue city = budapest, event date between 01/01/2009 and 31/01/2009, and the total number or males and total number of females who attended each of the events in the result
(my gender attr is set to text with values m/f and (not yes/no or 1/0))
i have 7 entries in my member table with 3 females as 4 males but my query result(below) gives me 16 for all....??
Each event is only attended by one person at a time, so for each city and date the male and female count should only be null and 1 or 1 and null if this helps to explain it better.
This is what ive done so far but as i said in the above that its not the result i want...
SELECT city , eventdate, count(gender) as male, count(gender) as female
from venue, event, member
where gender in('m') and ('m') and year(eventdate) = 2009 and month(eventdate) = 01 and city = 'Budapest'
group by City, Eventdate
Yeah it's homework. Shouldve mentioned it but thought that bit made it clear.
I'm not looking for just the answer, I was looking for an explanation or hint on how to add the m and f count to the other 2 cols in the query as i can get the results for city and eventdate. I also included what I've done so far at the bottom. Can it be done without a join query using select statements (nested query)?