Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Sums for mult values in drop down (2003)

    I am having trouble summing up different combinations of fields in one of my tables in a query / report... it is easy when there are only a few values, I can list out the permutations, but in the following case, I don't know what to do: I have a field which is a drop down of the 200+ countries in the world.... how do I ask my query / report to add up all the ie: males (another field) from each of the countries represented?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums for mult values in drop down (2003)

    You can select only one item in a combo box (dropdown list) so I don't understand what you mean by "mult values in drop down".

  3. #3
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Sums for mult values in drop down (2003)

    I.E. I have 12 males from 6 different countries - how do I ask for the query / report to return sums for all the matching permutations... without doing mult if /then statements so I will capture all countries the males may have come from?
    3 Males Mexico
    2 Males DR
    6 Males Albania
    1 Male Burma

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums for mult values in drop down (2003)

    You can create a totals query that counts the number of records per country for the gender selected in a combo box on a form; the expression in the Criteria line for the gender field would look like this:

    [Forms]![NameOfForm]![NameOfGenderComboBox]

    with the appropriate names substituted. But I don't understand what the country combo box has to do with this.

  5. #5
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Sums for mult values in drop down (2003)

    I am sorry I am not explaining my needs well...
    I am trying to sum up all the combinations of fields in the records in my table... IE: In one query I have IIf([strsex]="Male" And [strjuvenile]="Yes",1,0)- This adds up all the records of juvenile males... but I also want to know the number of juvenile females etc.. and since some of my fields have over 200 possibilities, I know I dont need to write out each combinations of each field, but dont know how to write it, so the query / report will identify and add all the different combinations of the records... ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums for mult values in drop down (2003)

    Create a totals query - you turn an ordinary select query into a totals query by selecting View | Totals. This will add a row labeled Total to the query design window; the default setting for Total is Group By, but you can select other options such as Sum and Count from the dropdown list.
    Add the fields on which you want to group, for example strSex and strJuvenile. Leave the Total option for these fields as the default Group By.
    Add a field on which you want to count, and set the Total option to Count.
    When you select View | Datasheet, Access will automatically compute all unique combinations of the Group By fields, and show the corresponding count.

  7. #7
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Sums for mult values in drop down (2003)

    I am sorry, but I still cant get what I need... I attached a sample DB so you could see a sample of what I am dealing with... in this case, in my report, I want to know that ie: on 1/4/07, how many males went to each destination and were from x country, how many females went to each destination and were from x country, then I want to summarize, ie: on 1/4/07, 3 Belarus traveled, and NYC was the destination in 3 of the cases, and there were 4 males and 2 females traveling that day. I need a combination of counts and totals.... thanks for your continuing assistance!
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums for mult values in drop down (2003)

    I think you'll need to create a series of queries for that, create reports based on those queries, and places them as subreports on a main report.

    I have created some examples of crosstab queries in the attached version of the database.
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Sums for mult values in drop down (2003)

    Thanks for the wonderful advice!! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

Posting Permissions

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