Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    How to retrieve values that match between two tables?

    I have two tables: "Delivery times" and "Receiving Times"

    Both are joined by the PO number and the relationship Join Type is set to include only rows where both are equal.

    Where the PO number matches, I want to include these rows. However there can be multiple instances of the PO number in each table.

    Right now, For each row in "Delivery Times" the query is including every row in "Receiving Times," multiplying the result!

    Example:
    "Delivery Times"
    1234567 | X | Y | Z
    1234567 | X | Y | Z
    1234567 | X | Y | Z
    1234567 | X | Y | Z

    "Receiving Times"
    1234567 | A | B | C
    1234567 | A | B | C

    Query Result:


    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z

    Expected Result:
    1234567 | A | B | C | X | Y | Z
    1234567 | A | B | C | X | Y | Z

    How do I force Access to include the total number of combinations and not permutations?

  2. #2
    Join Date
    Jun 2007
    Posts
    4
    Since these forums are dead, I will answer my own question...

    I redid the query with the "Summary" option and selected "Max" for the PO. Access gave no option to due a max on any other field, but this was the only way the wizard would open up the summary row in design view.

    I reset PO to "Group By" and then input "Max" under one of the dates. This gave me the last date that the PO was received on. Phew!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. lookup values in backend tables
    By deb56 in forum Database Design
    Replies: 1
    Last Post: 01-23-2008, 11:12 AM
  2. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM
  3. Finding data that doesn't match
    By dlhayes in forum Queries
    Replies: 1
    Last Post: 11-11-2006, 08:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums