Milti-Table Query

  • Thread starter Thread starter avkb03
  • Start date Start date
A

avkb03

Hello,

Not sure of how to handle a query.

I have a DB with multiple tables. Here's a sample layout

tblRegister
-ID (Prikey)
-JobName

tblEstimator
-ID (Prikey)
-Name

tblRegEstimator
-tblRegisterID (Prikey) (Related to tblRegister/ID)
-tblEstimatorID (Prikey) (Related to tblEstimator/ID)

For each JobName there can be multiple Estimators. I've got it setup so
that I can assign multiple estimators to a job. My problem is when I
want to pull the data out for reporting purposes. I'm not sure how to
do it.

I'd like a report that would list all of my jobs and then list all the
estimators on each of the jobs.

Thanks for your help!
Andy
 
first, a couple notes on your tables.
1. "Name" is a property of many Access objects; to use it
as the name of a table field (or a control in a form,
etc.) is going to cause you problems.
2. suggest you don't use the prefix tbl when naming a
field in a table. tbl is a standard prefix for table
objects, you're likely to confuse yourself and other
developers if you use it in field names. i generally use
the naming conventions as follows

tblRegisters
RegID (pk)
RegJobName

tblEstimators
EstID (pk)
EstName

tblRegEstimators
RE_RegID (combo pk; foreign key from tblRegisters)
RE_EstID (combo pk; foreign key from tblEstimators)

using the above tables, here's the SQL for your query:

SELECT [tblRegEstimators].[RE_RegID], [tblRegisters].
[RegJobName], [tblRegEstimators].[RE_EstID],
[tblEstimators].[EstName]
FROM tblEstimators RIGHT JOIN (tblRegisters RIGHT JOIN
tblRegEstimators ON [tblRegisters].[RegID]=
[tblRegEstimators].[RE_RegID]) ON [tblEstimators].[EstID]=
[tblRegEstimators].[RE_EstID]

that gives you the data you need. group and sort the data
in the report's design view to present it the way you want.

hth
 
Back
Top