SQL to show lookup data when referenced twice in table

[jocph]

Let's say I have 2 tables, tblProcess and tblWeld, as shown in the figures below:

How do I create the sql so that what I get in ProcessA and ProcessB is the corresponding ProcessCode without doing DLookup?

 

[jocph]

Ok, got it! Found out you can add the lookup table twice in the query builder.

SELECT tblWeld.WeldNo, tblProcess.ProcessCode, tblProcess_1.ProcessCode
FROM (tblWeld INNER JOIN tblProcess ON tblWeld.ProcessA = tblProcess.ProcessID)
INNER JOIN tblProcess AS tblProcess_1 ON tblWeld.ProcessB = tblProcess_1.ProcessID;

 

[Deleted member 147267]

Ok, got it! Found out you can add the lookup table twice in the query builder.

Hacks and work arounds are going to be the norm for you because your design is wrong, as Pat said. Well, actually I think it was implied, but I agree anyway.