Lost in how to query

[EgWeb]

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

 

[lespaul]

Try this:

SELECT R.JOBNAME, E.NAME FROM TBLREGESTIMATOR AS RE INNER JOIN TBLESTIMATOR AS E ON RE.ESTIMATORID = E.ID INNER JOIN TBLREGISTER AS R ON RE.REGISTERID = R.ID GROUP BY JOBNAME, NAME

(paste this into the SQL view and it should give you the name of the projects with the estimator name)

 

[EgWeb]

For some reason I can't get it to work...


Here's the actual Tables...

Register
-ID (Prikey)
-JobName
-Others

Estimators
-ID (Prikey)
-Last
-First

RegEstimators
-RegisterID (Prikey) (Related to Register/ID)
-EstimatorID (Prikey) (Related to Estimators/ID)


Here is the SQL I had:
SELECT register.jobname, estimators.last, register.id, estimators.id, regestimators.estimatorid, regestimators.projectid FROM regEstimators AS RE
INNER JOIN ESTIMATORS AS E ON RE.ESTIMATORID = E.ID AND
INNER JOIN REGISTER AS R ON RE.PROJECTID = R.ID
GROUP BY JOBNAME, Last

Thanks a lot!
Andy

 

[lespaul]

Ok I recreated your tables and here's what I got, worked for me:

SELECT RegEstimators.RegisterID, Register.JobName, RegEstimators.EstimatorID, Estimators.Last, Estimators.First
FROM Register INNER JOIN (Estimators INNER JOIN RegEstimators ON Estimators.ID = RegEstimators.EstimatorID) ON Register.ID = RegEstimators.RegisterID;

 

[EgWeb]

Wow...

I've done SQL statements, but these joins just kill me.

How do you think the SQL statement through when you go to write it?

Truth be told I've got 6 or 7 other tables similar to the estimators setup. For sake of your time I was hoping I could see how it was done with 1 and copy it through to others, but I'm kind of lost. How should I think it through when I go to do it?

Thanks you so much, you are a BIG help!
Andy

 

[lespaul]

Well, I'm glad it worked!

First of all, what the two queries say is EXACTLY the same, Access just has a special SQL syntax.

To create the "correct" query, I used the query design grid, added the three tables, made sure that there was a relationship between the ID fields in RegEstimator and the two other tables (if you have the relationships already set up these lines are already present when you add the tables to the query grid - if for some reason in a particular query you don't want that particular relationship you can delete it in the query and it won't affect the underlying table relationship)

Here's a link to a great site that really explains joins well:

http://www.devshed.com/Server_Side/MySQL/SQLJoins/page1.html

Again, since Access has it's own SQL flavor, some of the syntax won't work, but you will at least have an understanding of how the joins work and when to use what kind of join.

HTH

Leslie

 

[EgWeb]

Oh man! So much easier than I thought... I was trying to write the actual SQL...

Thanks again!