I have a table [RPG] that sometimes contains empty cells after an update. To fill those cells I need to reference another table [SITELIST] and specific fields on that table. It sounds very similar to VLOOKUP in Excel. Is there a way to use DLOOKUP or DLOOKUPPLUS to find and input the data. Maybe an append or update query....
Table - RPG
| HID | SID | Equip | MOD NOTE NUM | Date Complete | OSFSITE | ORG |
|---|---|---|---|---|---|---|
| BRO | RDA | S181 | 6/28/2023 | BRO | WP9250 | |
| BRO | BRO | MSCF | S182 | 6/28/2023 | BRO | WP9250 |
| BRO | BRO | RPG | S182 | 6/28/2023 | BRO | WP9250 |
| CAE | CAE | MSCF | S177 | 4/5/2023 | CAE | WN9310 |
| CAE | CAE | MSCF | S182 | 4/12/2023 | CAE | WN9310 |
| FGF | MVX | RAEG | 219 | 5/25/2023 | MVX | WR9750 |
| HFO | HFO | MSCF | S182 | 5/17/2023 | HFO | WW9182 |
| LWX | LWX | RDA | 203 | 6/30/2023 | LWX | WN9931 |
| MEG | NQA | RDA | S181 | 6/23/2023 | NQA | WP9334 |
| MOB | EVX | WSR88 | 203 | 5/19/2023 | MOB | WP9223 |
| MSO | MSX | RDA | 227 | 6/22/2023 | MSX |
Table - SITELIST
| ID | ORG CODE | SID | Radar ID |
|---|---|---|---|
| 41 | WP9250 | BRO | BRO |
| 42 | WP9250 | BRO | BRO |
| 208 | WT9773 | MSO | MSX |
| 411 | WP9250 | BRO | BRO |
| 487 | WT9773 | MSO | MSX |
| 580 | WP9250 | BRO | BRO |
In above I have a missing 'SID' and 'ORG" on table RPG. This translates to RPG.HID = SITELIST.SID and RPG.SID = SITELIST.Radar ID and RPG.ORG = SITELIST.ORG CODE
I know this is a lot, and probably confusing. Any assistance in how to lookup the data based on empty cells and fill in the information would be appreciated.
I've got a query to find the empty cells
SELECT RPG.HID, RPG.SID, RPG.Equip, RPG.[MOD NOTE NUM], RPG.[Date Complete], RPG.OSFSITE, RPG.ORG
FROM RPG
WHERE (((RPG.SID) Is Null)) OR (((RPG.ORG) Is Null));
beyond that I'm lost at how to proceed with nothing yielding results
These tables do not appear to have a parent/child relationship.
In the case of HID of "BRO" it seems the RADAR ID is always "BRO", HID of "MSO" would return RADAR ID of "MSX". Similarly, HID of "MSO" will return ORG CODE of "WT9773". An UPDATE action can populate both SID and ORG in RPG but it won't be pretty.
Linking RPG to an aggregate (GROUP BY or DISTINCT) of SITELIST query for an UPDATE action fails because Access does not support this.
Linking on RPG.HID and SITELIST.SID will result in a Cartesian product query and an UPDATE will run with a message that 6 records will update for the sample data.
UPDATE RPG INNER JOIN SITELIST ON RPG.HID = SITELIST.SID
SET RPG.SID = [SITELIST].[Radar ID], RPG.ORG = [SITELIST].[ORG CODE]
WHERE (((RPG.SID) Is Null)) OR (((RPG.ORG) Is Null));
Domain aggregate function (DLookup, DFirst, DLast, DMax, DMin - any will work in this case) is an alternative.
UPDATE RPG SET SID = DLookup("[RADAR ID]","SITELIST","SID='" & HID & "'"),
ORG = DLookup("[ORG CODE]", "SITELIST", "SID='" & HID & "'")
WHERE SID Is Null OR ORG Is Null
Both approaches can perform slowly with large dataset. So, a third alternative is VBA using recordsets which might be fastest.