SQL Syntax for Lookup Tables -- BEST PRACTICE

[MediaDoc]

Hello,

I have got things working, but wonder if there is a better way to do things.

I have an access DB, with many lookup tables, all referrenced in a table of Contacts.

For example
ContactID
FirstName
LastName
SalutationID (lookup from tblSalutations)
IndustryID (lookup from tblIndustry)
ReferralTypeID (lookup from tblReferralType)
etc..

Now, in access the tables store the SalutationID value, but show the Salutation itself (i.e. Mr., Mrs.).

When I build a web based query, the Salutation value not the Salutation itself. So I have taken to creating monstrosities of OUTER JOINs to get all things showing as needed. (use outer joins as not all contacts have salutations, industry type etc.)

I.E.

SELECT c.ContactID, s.Salutation, c.SalutationID, c.FirstName, c.LastName, c.BusinessTitle, c.Organization, c.Street, c.City, c.Province, c.PostalCode, c.Country, t.Industry, c.IndustryID
FROM (tblContacts c LEFT OUTER JOIN
tblSalutation s
ON c.SalutationID = s.SalutationID) LEFT OUTER JOIN
tblIndustry t ON c.IndustryID = t.IndustryID

and so on and so on.

Is there a better / easier way to do this ???

Thanks very much,

Matts

 

[The_Doc_Man]

The key to answering your question lies in this comment:

(use outer joins as not all contacts have salutations, industry type etc.)

Tables that don't have values for all possible linkages are called "Sparse" - with exactly the meaning cartographers apply when they show an area on a map as being "sparsely populated."

When faced with "sparse" tables, you do far better to build the JOIN than to include a more complex syntax. For example, to handle sparse tables another way, you might need to write something really ugly like

Nz( DLookup("[Salutation]","tblSalutations","[SalutationID]=""" & CStr(c.SalutationID) & """"), "" )

The extra functions only add time and make the query that much more complex. Best practise may well be what you have as you wrote it. Get it all done in one operation. The syntax is far easier to maintain and the SQL is probably just a little easier to read than nested DLookup operations.

Besides, the DLookup was going to have to build the SQL "on-the-fly" anyway. Building your SQL all at once as a formal query means it can be pre-optimized by Access when the query is first stored as a permanent QueryDef.