Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals query (2003)

    I have a totals query that I run for all the data (see attached). Now I want to limit the data by running it for 5 different areas. I need the same 15 fields in the description field to appear in each query regardless if there are any numbers - these would be 0 all the way across but I would still have the 15 descriptions. Is there any way to do this?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (2003)

    Attach a table with the 15 descriptions you need and join it with your current query using an Outer join (double click on the link line and choose show all items from your description table and only the matching ones from the totals query.

    To display the zeros for unmatched descriptions use the NZ function:
    nz([totals_filed],0)*1

    I've attached a very basic example
    Attached Files Attached Files

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

    Re: Totals query (2003)

    You could create a table (if you don't already have it)listing the 15 descriptions.
    Create a totals query for a specific area. This may not return records for all descriptions.
    Next, create a new query based on the table and the totals query, joined on the description field.
    Double click the join line and select the option to return ALL records from the table and only related records from the query.
    Add the description field from the table and the other fields from the query to the query grid.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (2003)

    It is not working. It sounds like it should but for some reason I am not getting the 15 descriptions. I added a table of the 15 descriptions and did an outer join. I put that query into another query and did the nz for the fields to take care of the zeros.

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

    Re: Totals query (2003)

    Make sure that the outer join goes in the right direction - the arrow should point at the totals query.

    If you can't get it to work, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (2003)

    Thank you both very much. I used a little from both of you. It works perfectly....

Posting Permissions

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