Multi-table lookups

  • Thread starter Thread starter Rob Mazur
  • Start date Start date
R

Rob Mazur

Hi. I'm trying to create a query that pulls information
from 3 seperate tables. Here's how I want it to work:

User is prompted for SSN

Table 1:
SSN
Name
Gender

Table 2:
SSN
Age
Repetitions

Table 3:
Gender
Age
Repetitions
Score

After the user inputs the SSN, I need to look up the score
from table 3 based on age and repetitions from table 2. I
thought DLookUp() was the way to do it, but (1) I've been
having problems with it and (2) I've read that it isn't
the most effective way to lookup that kind of information.

Can anyone help me? I can create this query either
through QBE or SQL.

Thanks

Rob Mazur
 
I don't think there's enough info here to really give you a solid answer,
but I'll address your DLookup() Concern.

Dlookup() is not optimal, performance-wise, and the VBA purist will scoff
their nose at it. Others are just trying to get their job done, so they use
it, consistantly and effectively. DLookup is very flexible, in that you can
either use it against a table or another query. It is not uncommon to see
something like:

strXYZ = Dlookup("xyz", "qryGetDataFromMultipleTables", "x=1 and b=5 and
...."

So, you can probably do what you need to do, but you may need to make
another query to do it.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top