Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Totaling Question (2k)

    Hi, I have a table that has ~1000 items in it. Each item has an associated date, as well as "X"'s checked in various categories across the table. What I'm trying to do is write a query that combines like items that have the same associated date, and total up the amount of "X"'s. For example, say I have this table:

    Item #: Date: Cat1 Cat2 Cat3 Cat4 Cat5
    Item 1 9/7/04 X X O X X
    Item 1 9/8/04 O X X O O
    Item 1 9/7/04 X O X O X
    Item 1 9/7/04 O X X O X

    Should result in this query:

    Item #: Date: Cat1 Cat2 Cat3 Cat4 Cat5
    Item 1 9/7/04 2 2 2 1 3
    Item 1 9/8/04 0 1 1 0 0

    I'm really not sure how to do this though, any help is appreciated.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Totaling Question (2k)

    Hi,
    Are your X's actually "X" in the field or do you have Yes/No fields?
    You need a totals query which groups by Item and Date and then, if they are "X"s, your sum fields can be iif([Cat1]="X",1,0) etc. If they are Yes/No fields you should be able to use Abs([Cat1]) summed.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query Totaling Question (2k)

    See attached which does exactly what Rory suggested.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Totaling Question (2k)

    When I do that, I get a "Data Type mismatch in the criteria expression". I tried putting the expression inside Val( ) but that didn't seem to work. Is it because I have 2 'group by' fields? (id and date). See attached screen shot for my actual expressions.

    Edit: They are indeed "X"s
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Totaling Question (2k)

    Are status1, status2 etc. text fields or yes/no fields? If they are Yes/No fields, you will need to change the formula to Abs([status1]) as I mentioned.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Totaling Question (2k)

    They are text fields, they can contain "OK", "X", "N/A", or nothing. I tried Abs( as well and it didnt seem to do the trick either. any other suggestions?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Totaling Question (2k)

    Hi,
    At the risk of labouring the point, are they definitely text fields? Your last post implied they can only be certain values so I just want to check this isn't a lookup from another table, in which case the status field might actually be storing a number. If you look at the table design, what does it show? Also, just to check, do you have any other criteria established in your query - i.e. are you restricting the returned records in any way (e.g. by date)?
    Edit: the attached image shows a table. Cat 1 is a text field, Cat2 is a Yes/No and Cat3 is actually a number field which looks up from another table.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Totaling Question (2k)

    The query is based off of a table that does indeed use text fields for the fields I am querying. The choices are selected from a combo box that gets its contents from a status table, this table is also text fields. the query is set to "Group By" for the ID and Date fields. All other fields are set to Sum the expressions shown in the previous screen shot. There are other fields in the original table that I am not paying any attention to, just the Item #, the Date, and the Status categories.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Totaling Question (2k)

    In reply to your edit, my case is the same as your Category 1 field.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query Totaling Question (2k)

    Very strange - using the same iif expressions, I get exactly the result I would expect. Can you post the SQL of your query? Also, what do you get if you run the query without the totals?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Totaling Question (2k)

    Rory, thanks for sticking with me through this. Turns out the expressions were set on "Sum" in the totals section. When i switched them over to "Expression" and used PStatus1: Sum(Abs(IIf([status1]="X",1,0))), everything worked perfect! you guys are the best! thanks for all the help!

    -os
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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