• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

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

jocph

Member
Local time
Tomorrow, 00:26
Joined
Sep 12, 2014
Messages
52
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
Tomorrow, 00:26
Joined
Sep 12, 2014
Messages
52
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, 12:26
Joined
Feb 19, 2002
Messages
29,671
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, 12:26
Joined
Oct 20, 2018
Messages
3,467
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