Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rank beginner (2000)

    Hi. I created a database with 3 fields that have drop down menus. I'm trying to get a count for each response for each of those 3 fields. I was able to create 3 separate queries that show the count for each of the responses for the questions separately by grouping by the field and then counting the record IDs. I'd like to create a query that could combine these 3 all into one, but haven't been able to do it.
    I also might need to add another field and would want to change the query to sort by this new field then by the original field before giving the count.
    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: rank beginner (2000)

    Hi yilaoshi

    Welcome to the Lounge.


    I have done something similar in an Access database when I was inputting a survey and wanted to count the number of people who said Yes to a particular question. I used a union query in the SQL part of the query designer and used this syntax:

    SELECT Count(item1) AS Rank
    FROM tblList
    WHERE Item1=1

    UNION

    SELECT Count(item2)
    FROM tblList
    WHERE Item2=1

    UNION

    SELECT Count(item3)
    FROM tblList
    WHERE Item3=1

    ;

    Item 1-3 are myfield names and the 1 is because I am counting the responses 1 you can have your variable there instead. There are other methods I am sure but I found this by far the most flexible as I could cut and paste it to any order I wanted...enjoy
    Jerry

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: rank beginner (2000)

    Do the three fields that have drop down menus, all have the same entries in the DropDown? e.g. Yes, No, Unknown.

    Are the values for the dropdown stored in a table, or as a value list?

    If they are in a table, you can base your query on that table, then join your existing 3 queries by response.
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Sep 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rank beginner (2000)

    No, the 3 fields have different response categories. They are stored in a value list, not a table.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: rank beginner (2000)

    Maybe this is me, but I can't envisage what the results of this query would look like.

    Can you build an Excel spreadsheet with some dummy data in it, (and post it here) that looks like the data you want the query to produce.
    (The results of a query are always a series of rows and columns where each row has the same structure, so it should be possible to represent it in Excel.)

    It might help someone to answer you, or might help you to work out whether your request is doable.
    Regards
    John



  6. #6
    New Lounger
    Join Date
    Sep 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rank beginner (2000)

    I just cut and pasted the 3 existing queries into an Excel spreadsheet

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: rank beginner (2000)

    From what you have described you could have done that with a union query that Jezza suggested earlier.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: rank beginner (2000)

    You could get something like this with a union query, as has been mentioned before, but you would have to rename the second column in each case so they had the same name. You would then get something like the following, which would be hard to follow as you would not know what question the response related to.

    But in your earlier post you said you wanted to add an extra field from the table and sort by that. You have not put that into the Excel
    Regards
    John



  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: rank beginner (2000)

    I don't think you have to have the same names in each select query, they must have the same data type though.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: rank beginner (2000)

    But once they get put together by the union query there is only one column with one field name (column heading) for it.
    This might be assigned by the union query , but once it is done there can't be three different names.
    Regards
    John



  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: rank beginner (2000)

    What you will find is that the union query uses the field names from the 1st select statement in the union query.

Posting Permissions

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