Results 1 to 5 of 5
  1. #1
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Complex Query (2007)

    I have a report I have to build that has elements of three separate tables in it.

    Should I construct a query that ties these together and then build the report from the query?

    Regards,
    Chuck Billow
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

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

    Re: Complex Query (2007)

    Yes, that's probably the best way to do it. Take care to join the tables correctly.

  3. #3
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Complex Query (2007)

    Hans:

    I think the relationships should be like this. Whatcha think?
    Attached Images Attached Images
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

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

    Re: Complex Query (2007)

    That looks OK.

    If you want to display all vendors, even if they don't have related records in the Item table, double click the join line and select the option to return ALL records from the Vendor table.
    Similar for the line between Item and Reorder_Table.

    One remark: the symbols at the ends of the line joining Vendor and Item show that you have created a relationship between these tables that enforces referential integrity. That is excellent - it means that the user cannot create a record in the Item table without a valid Vendor code.
    The other line doesn't have such symbols. This may indicate that you haven't defined a relationship between Item and Reorder_Table, or if you have, that you haven't enforced referential integrity. So the user could create a record in the Reorder_Table without a valid Item Id. You should have a relationship that enforces referential integrity.

  5. #5
    Uranium Lounger CWBillow's Avatar
    Join Date
    Jul 2002
    Location
    Las Vegas, NV USA
    Posts
    6,371
    Thanks
    78
    Thanked 12 Times in 11 Posts

    Re: Complex Query (2007)

    Hans:

    Thanks much.

    >>
    The other line doesn't have such symbols
    <<

    That one had slipped by me. It would have screwed it up I think.

    Thanks again.

    Regards,
    Chuck
    -------------------------------------------------
    "Good judgment comes from experience, and experience - well, that comes from poor judgment."

    ~ A(lan) A(lexander) Milne (1882-1956)- "House at Pooh Corner"

Posting Permissions

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