Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Question Unanswered: Gender count on from 3 tables

    Please help with the following

    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
    3- Venue-(venueid,city,etc)

    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

    Thanks


  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'd be curious to know why you highlighted "query builder not allowed". Is this some kind of school homework or job interview?
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    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)?

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As I can see it, you're creating a Cartesian Join (or Cartesian Product) (see: SQL Cartesian Joins Programming Reference and Examples) among the tables (because of: "FROM venue, event, member"). These tables should be joined with proper INNER, LEFT or RIGHT joins.

    Can you please specify the structure of the 3 tables: [venue], [event], [member] (column names and data type for every one of them)?
    Have a nice day!

  5. #5
    Join Date
    Apr 2012
    Posts
    3

    Re column names and data types

    Thanks for your help so far. Will have a look at the link now that I know what to look for.

    The column names are as in the original post and the needed attributes is text in the data type except for the date which is date/time. The primary keys are all text as well.

    Thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •