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.
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
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.
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)
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: