Problems Writing Query

[crhodus]

I'm having problems writing a query and I was wondering if anyone could help. I'm not exactly sure how I need to do this.
I have 3 tables - tblA, tblB, tblC.
(* denotes primary key)

tblA
----
*project_id
project_name


tblB
----
*company_id
project_id
company_name


tblC
----
*contact_id
company_id
contact_name


I need a query that will return project_name, company_name, and contact_name where tblA.project_id = 1 from these three tables. How can I do this? I'm not sure what to do because of tblC. Can anyone help?

Thanks,
crhodus

 

[Malcy]

I put the three tables in a dummy Db and did a simple query with the three tables linked. Called the tables tblProject, tblCompany, and tblContacts
The SQL is

SELECT tblProject.project_id, tblProject.project_name, tblCompany.company_name, tblContacts.contact_name
FROM tblProject INNER JOIN (tblCompany INNER JOIN tblContacts ON tblCompany.company_id = tblContacts.company_id) ON tblProject.project_id = tblCompany.project_id
WHERE (((tblProject.project_id)=1));

I think this will give you what you want. You would need to build a form to drive the query so that you can select the project.
Hope this helps

Malcy

 

[neileg]

Malcy is about there, except for the INNER JOINs. I suspect these should be LEFT JOINS.

Here's a step by step for beginners.

Create a new query in design mode. Add all three tables to the query. If you have set up the relationships in the database they will appear in the query. If not link them now. Ensure then the relationships are left joins, ie all the records from tblA and those from tblB that match, all the records from tblB and those from tblC that match. If you don't do this, you'll only get results where there is a record in all three tables.

 

[crhodus]

Thanks for your help. I finally got the query to work. I'm use to typing my queries in the SQL view. If I had only used the design view to begin with, it would have been a whole lot easier to create this query. Thanks again!