Can I do this in one Query?

[Tim]

This query below is very nice in pulling one record in table
dbo_Project for each instance where a related record in table
dbo_ProjectBid.Awarded = True.

SELECT DISTINCT dbo_Project.*, dbo_ProjectBid.Customer_ID
FROM dbo_Project INNER JOIN dbo_ProjectBid ON
dbo_Project.Project_ID=dbo_ProjectBid.Project_ID
WHERE (((dbo_ProjectBid.Awarded)=True));

Now I would like to pull in one field from a 3rd table and I'm having
trouble:

New Table dbo_ProjectAmount has fields: Project_ID; Amount;
Amount_Type

Tbl dbo_ProjectBid from the above query has a defined Amount_Type.

So dbo_Project.Project_ID = One or more dbo_ProjectBid where Awarded =
True AND for each of those, I want to display the Amount field from
dboProjectAmount where the dbo_ProjectAmount.Amoun_Type =
dbo_ProjectBid.Amount_Type AND dbo_ProjectAmount.ProjectID = (the
project ID for each result in the above query).

Ugh... lot easier to think and say this than it is to type.
Can anyone shed some light... the data is all there... there's got to
be an easy answer here.

 

[Ken Snell [MVP]]

Not sure if this is what you're seeking, but it may get you started:

SELECT DISTINCT dbo_Project.*, dbo_ProjectBid.Customer_ID
FROM (dbo_Project INNER JOIN dbo_ProjectBid ON
dbo_Project.Project_ID=dbo_ProjectBid.Project_ID) INNER JOIN
dbo_ProjectAmount ON dbo_ProjectBid.Project_ID =
dbo_ProjectAmount.Project_ID AND dbo_ProjectBid.Amount_Type =
dbo_ProjectAmount.Amount_Type
WHERE (((dbo_ProjectBid.Awarded)=True));