Results 1 to 5 of 5

Thread: Query help

  1. #1
    Join Date
    Nov 2010
    Posts
    6

    Unanswered: Query help

    I am trying to bring back an item and its corresponding item that it may or may not have. For example, there are PRFs and PIFs. A PRF may or may not have a corresponding PIF and a PIF may or may not have a corresponding PRF. Both of these items are in the same table. I would think a self full outer join would do the trick, however; Access does not support full outer joins. Can anyone assist me?

    Also, please no snarky or condescending comments. If I've omitted some information that you need to help answer my question, just ask me. I think it is important to be professional and respectful.


    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What determines the correspondance between the items? If possible post the definition of the table (at least the relevant part that is implied in the operation).
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Posts
    6
    Each PRF and PIF have an ID. If a PIF is associated with a PRF, the PIF's ID would be populated in the Associated PIF column. If a PRF is associated with a PIF, the PRF's ID would be populated in the Associated PRF column

    Table columns
    ID (primary key) - unique ID of PIF or PRF
    Associated PRF = ID of PRF (there may not be)
    Associated PIF = ID of PIF (there may not be)


    A PIF may not have a PRF and a PRF may not have a PIF.

    Let me know if you need any other information. Thank you so much for your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I've tested the following solution on a table with this structure:
    Table name: Tbl_Items
    Column1: [Unique_ID], AutoNumber, Primary key
    Column2: [Item], Text
    Column3: [Item_Type], Text, Validation rule: "PRF" Or "PIF"
    Column4: [Associated_PRF], Number (Long)
    Column5: [Associated_PIF], Number (Long)

    The query:
    Code:
    SELECT Q1.Unique_ID, 
           Q1.Item, 
           Q1.Item_Type, 
           IIf([Item_Type]="PRF",[Associated_PIF],[Associated_PRF]) AS Associated_ItemID,
           (SELECT Tbl_Items.Item FROM Tbl_Items WHERE Tbl_Items.Unique_ID = IIf(Q1.Item_Type="PRF",Q1.Associated_PIF,Q1.Associated_PRF)) AS Associated_Item
    FROM Tbl_Items as Q1
    For selecting a unique row, complete the query with:
    Code:
    WHERE Q1.Unique_ID = <ID>
    Have a nice day!

  5. #5
    Join Date
    Nov 2010
    Posts
    6
    Thank you for the reply. I will give this a try.

Posting Permissions

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