Appending Data - Using Dlookup to retrieve Primary Key value to insert into Foreign K (1 Viewer)

eddix99

Registered User.
Local time
Tomorrow, 08:41
Joined
Jul 7, 2016
Messages
32
I’m at the stage where I need to reach out to ask for some guidance into - what is likely to be - a fairly simple problem.
  • I have a financial Accounting System which can output data in a specific format.
  • I want to take that data, clean it up a bit and append it to an existing Access DB – Using VBA where possible - For enhanced reporting purposes.
  • The Access table has a foreign key field, and the incoming data must have a corresponding valid key value in order to maintain referential integrity during the append process.
  • I am trying to use the DLookup function to fetch the value of the primary key based on a match between two String Values in both tables. (In this case ‘Building Number’)
  • What I’m trying to get DLookup to do is display the primary key value in the second table, using a match between Building numbers to determine the value of the foreign key.
I enclose a very simple db and would appreciate any pointers as to where I’m going wrong.
 

Attachments

  • TempDB.accdb
    472 KB · Views: 111

JHB

Have been here a while
Local time
Today, 23:11
Joined
Jun 17, 2012
Messages
7,732
Why not linking the 2 tables together?
Code:
SELECT DlookupTest2.DTest2_ID, DlookupTest2.Person_Name, DlookupTest2.[Building Number_2], DlookupTest.DTest1_ID
FROM DlookupTest2 INNER JOIN DlookupTest ON DlookupTest2.[Building Number_2] = DlookupTest.[Building Number_1]
ORDER BY DlookupTest2.DTest2_ID;
Or
Code:
SELECT DlookupTest2.DTest2_ID, DlookupTest2.Person_Name, DlookupTest2.[Building Number_2], DlookupTest.DTest1_ID
FROM DlookupTest2 LEFT JOIN DlookupTest ON DlookupTest2.[Building Number_2] = DlookupTest.[Building Number_1]
ORDER BY DlookupTest2.DTest2_ID;
 

eddix99

Registered User.
Local time
Tomorrow, 08:41
Joined
Jul 7, 2016
Messages
32
Boom! Thank you. This is what I wanted to do.

Linking the tables together. Eureka!:)
 

JHB

Have been here a while
Local time
Today, 23:11
Joined
Jun 17, 2012
Messages
7,732
You're welcome, good luck. :)
 

Users who are viewing this thread

Top Bottom