SQL error

[Bee]

Hi,

I have three tables that are joined and I wanted to create a query that would return all records from one of the tables and only those matching from the other table.

I used Access properties window that does that; however, when I try to execute the query, it prints this message:

'The SQL statement could not be executed because it contains ambiguous outer joins. To force one the joins to be performed first, create a seperate query that performs the first join and then include that query in your SQL statement.'

This is my query:

SELECT tblSite.Site_No, tblPhase.Phase_No, tblSite.Name, tblHouse.House_ID, tblHouse.Plot_no, tblHouse.House_No, tblHouse.House_Address, tblHouse.House_P_Code, tblHouse.House_Type, tblHouse.Gross_Floor_Area, tblHouse.No_Of_Apartments, tblHouse.Number_Of_Bedrooms, tblHouse.House_Width, tblHouse.House_Depth, tblHouse.Garage, tblHouse.Phone, tblHouse.Electricity_Meter_Reading, tblHouse.Gas_Meter_Reading, tblHouse.Water_Meter_Reading, tblHouse.Customer_Handbook_Completed, tblHouse.Anticipated_Councial_Tax_Band, tblHouse.Missives, tblHouse.House_Price
FROM tblSite INNER JOIN (tblPhase RIGHT JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No;

I can't get it to work and I will appreciate any help please.

regards,
B

 

[Matt Greatorex]

I had a similar problem.

I got around it by creating one query to join tblHouse to tblPhase, then creating a second to join the results of that query to tblSite.

Not that I had tables of that name, but you get the idea

 

[cuttsy]

So you want all houses and the site and phase details where available?

Does this work?

SELECT tblSite.Site_No, tblPhase.Phase_No, tblSite.Name, tblHouse.*
FROM tblHouse LEFT JOIN (tblPhase INNER JOIN tblSite ON tblPhase.Site_No = tblSite.Site_No) ON tblHouse.Phase_No = tblPhase.Phase_No;

 

[Bee]

I had a similar problem.

I got around it by creating one query to join tblHouse to tblPhase, then creating a second to join the results of that query to tblSite.

Not that I had tables of that name, but you get the idea

Do you mean a query to join tblSite to tblPhase and subquery that returns the result of tblHouse joined to tblPhase?

 

[Bee]

So you want all houses and the site and phase details where available?

Does this work?

SELECT tblSite.Site_No, tblPhase.Phase_No, tblSite.Name, tblHouse.*
FROM tblHouse LEFT JOIN (tblPhase INNER JOIN tblSite ON tblPhase.Site_No = tblSite.Site_No) ON tblHouse.Phase_No = tblPhase.Phase_No;

That's right.
Your SQL returned the following error:
'Join expression not supported'

Cheers,
B

 

[cuttsy]

I'm off out right now I'll take another look later if nobody else shows you how to do it.

 

[Bee]

I'm off out right now I'll take another look later if nobody else shows you how to do it.

Alright, thanks.

 

[Bee]

Please refer to this for help as the previous code I sent was wrong:

SELECT tblHouse.Agreed_Price, tblHouse.House_Price, tblHouse.Deposit_Received, tblHouse.Deposit_Amount, tblHouse.Remaining_Balance_Paid, tblHouse.Customer_Handbook_Completed, tblHouse.Phase_No, [Agreed_Price]+[VO_Price] AS totalPriceToBePaidIncVO, [House_Price]/[Gross_Floor_Area] AS priceM2GFA, tblEmployee.Contact_Forename, tblEmployee.Surname
FROM ((tblCustomer RIGHT JOIN tblHouse ON tblCustomer.Customer_No = tblHouse.Customer_No) INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) LEFT JOIN (tblEmployee INNER JOIN tblServices ON tblEmployee.Employee_ID = tblServices.Employee_ID) ON tblHouse.House_ID = tblServices.House_ID
WHERE (((tblEmployee.Department) Like "Sales Agent"));

I want to have two outer joins for tblHouse; however, access keeps returning the same error that talks about ambiguous outer joins!!!