Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complex Query (2000/SR-1)

    Can someone help with a query?

    I have 3 tables: Main, Related, Description

    Main contains a primary key, Key1, linked to a related field in Related, also called Key1. Description has a primary key, Key2, and is not defined in table relationships. Related contains a combined primary key made from fields Key1 and Key2. Key2 in this table contains related information linking it to Description.

    E.g.,

    MAIN
    Key1
    Name
    Address
    ...

    RELATED
    Key1
    Key2
    Due
    Done
    Notes
    ...

    DESCRIPTION
    Key2
    TransactionDesc1
    TransactionDesc2
    ...

    In other words, each TransactionDesc has a Due date, Done date, and Note.

    I need to print a subreport based on data from Related and Description. The report displays data from Main.

    My problem arises when I want to print ALL records from Description combined with the proper data from Related. I can get all the data from Related to print in the proper report, but cannot get all lines printed from Description, even those with no data (It prints a rreport that leaves blank spaces to be able to fill in when in the field.)

    An SQL answer would be fine.

    thanks.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Complex Query (2000/SR-1)

    1. Create a new query that returns *all* possible combinations of Key1 and Key2. Add the Main and Description tables to the query, but don't join them. Drag key1 from Main into the query grid, also Key2 from Description. The SQL for this query is:

    SELECT Main.Key1, Description.Key2
    FROM Main, Description;

    For illustration purposes, I'll call this macro qryAllKeys.

    2. Create a new query that returns all key combinations, with due dates etc. if available. Add the Related table, qryAllKeys query and Descriptions table to the query. Create an inner join between Desctription and qryAllKeys on Key2. Create an outer join between Related and qryAllKeys on Key1 and Key2, so that *all* records of qryAllKeys will be returned.

    Add the following fields to the query grid:

    Key1 and Key2 from qryAllKeys,
    TransactionDesc1 etc. from Description,
    Due etc. from Related.

    The SQL for this query looks like this:

    SELECT qryAllKeys.Key1, qryAllKeys.Key2, Description.TransactionDesc1, Description.TransactionDesc2, Related.Due, Related.Done, Related.Notes
    FROM (qryAllKeys INNER JOIN Description ON qryAllKeys.Key2 = Description.Key2) LEFT JOIN Related ON (qryAllKeys.Key1 = Related.Key1) AND (qryAllKeys.Key2 = Related.Key2);

    (of course, you can add other fields to this query)

    For illustration purposes, I'll name this query qryAllDescriptions.

    3. Use qryAllDescriptions as record source for the subreport. Link it to the main report using Key1 as master and child link field.

  4. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Hawaii
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complex Query (2000/SR-1)

    Perfect! Absolutely perfect. How do you do it, we dummies wonder?

    Mahalo.

Posting Permissions

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