SQL to show lookup data when referenced twice in table (1 Viewer)

jocph

Member
Local time
Today, 19:26
Joined
Sep 12, 2014
Messages
61
Let's say I have 2 tables, tblProcess and tblWeld, as shown in the figures below:

1592890371904.png

1592890430843.png


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

1592890582045.png
 

jocph

Member
Local time
Today, 19:26
Joined
Sep 12, 2014
Messages
61
Ok, got it! Found out you can add the lookup table twice in the query builder.

1592893364976.png


SQL:
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

Super Moderator
Staff member
Local time
Today, 07:26
Joined
Feb 19, 2002
Messages
42,970
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.
 

Micron

AWF VIP
Local time
Today, 07:26
Joined
Oct 20, 2018
Messages
3,476
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.
 

Users who are viewing this thread

Top Bottom