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;

 

[Pat Hartman]

You might want to also consider rethinking your design. If you have more than one of something, you have many and many requires a separate table. So instead of having ProcessA and ProcessB as column names, the processIDs should be in a separate table. That gives you the flexibility of having a third process in some situations without having to redesign your tables and forms and queries and reports.

Whenever you suffix or prefix column names, you are essentially using data as part of the name and that is a big red flag identifying a design flaw.

 

[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.