Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    Greenville, South Carolina, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Query (Access 97)

    I'm working with an Access 97 database that's combining information from about a dozen tables into one big query for a report. Due to slight differences in the data that will be placed into the categories, each table is a different category (one for personnel infractions, one for equipment check, etc.), but all the information is pulled together into one report. My users want to see all the categories (i.e. table names) at once, even if there is no data in that category. Seems to me that due to the nature of the union query, that's not possible. Anyone have any ideas on how to include everything, even if there's nothing to display?

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

    Re: Union Query (Access 97)

    You need a table or query that contains one record for each distinct category. If you already have such a table, use that, otherwise, create a table or query for this purpose.

    Next, create a new query, add the Categories table or query and the union query. Join them on the Category identifier (ID, name, ...). Make it into a left join, i.e. make it display all records from the Categories table/query.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Union Query (Access 97)

    You will need to base your Union query on other queries, rather than directly on tables. Each of these other queries must use an OuterJoin between the tables to ensure that at least 1 record is returned.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    Greenville, South Carolina, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query (Access 97)

    Thanks! I created a table with the category names as records, then did the left join. At first it wasn't working, but then I realized I had to pull the category names from the table of names and not my union query. My boss and I thank you!!!

Posting Permissions

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