Table Relationships

D

David

Having a problem with creating SQL Querys For My Reports.
When I Add The Tables To The Query And Create The Fields I
Receive A Message "The SQL Statement could Not Be Executed
Because It Contains Ambiguous Outer Joins, and So on"
the Following Are the Tables I Am Trying To Join and
Combine To Create A Weekly Wage Roport which shows the
total weekly wages and Total Sales For the Week Ending
Date And eventually used unbound controls in the report to
calculate percentages based on Weekly sales and Purchaese
in the different categories:
WeeklySales Table * WeeklySalesDetail Table
* WeeklySalesID Auto number WeeklysalesDetailID Auto
SalesCategoryID number Actualsales Cur
WeekEndingSate Date/Time ActualPurchases Cur
WeeklySalesID Num
WageDetailID Num

SalesCategory Table Wages Table
* SalesCategoryID AutoNumber * WageID Auto
salesCategory text WageDate D/T
ProposedPercent % TotalWages Cur
TotalHoursUsed Text
ManagerWages Cur

WageDetail Table
* WageDetailID Auto Number
SalesCategoryWages Cur
SalesCategoryHrsUsed Text
AvgSalesCategoryHrlyWage Cur
WageID Num

My Relationships are as follows:
weeklySalesDetail To WeeklySales With Ref Integrity, Both
Cascade D/U and Join Type 2
WeeklySales to SalesCategory With Ref Integrity, Both
Cascade D/U and Join Type 2
WeeklySalesDetail To WageDetail With Ref Integrity, Both
Cascade D/U and Join Type 2
WageDetail To Wages With Ref Integrity, Both Cascade D/U
and Join Type 2

Iguess my problem lies with the structure of the wages
tables and the relationships to the other ones
if anyone is able to help,

SQL As Follows
SELECT [Weekly Sales].WeekEndingDate
FROM ([Sales Category] LEFT JOIN [Weekly Sales] ON [Sales
Category].SalesCategoryID = [Weekly
Sales].SalesCategoryID) LEFT JOIN ((Wages LEFT JOIN [Wage
Detail] ON Wages.WageID = [Wage Detail].WageID) LEFT JOIN
[Weekly Sales Detail] ON [Wage Detail].WageDetailID =
[Weekly Sales Detail].WageDetailID) ON [Weekly
Sales].WeeklySalesID = [Weekly Sales Detail].WeeklySalesID;

Thank You In Advance
David
 
S

Steve Schapel

David,

You can't have a Left Join from a table to another table in a query,
and then have another left join from the other table to a third. It's
against the rules. This is the meaning of the error message you are
getting. One way around it is to create a query with the first two
tables, and then make a second query, using the first query and the
third table. Etc. Hope that makes sense.

By the way, as a side issue... I haven't analysed the informationn you
gave in detail, but I noticed that you have set cascade updates in the
relationships between your tables. This seems curious to me... the
relationships are based on autonumber fields, which can't be updated
anyway.

- Steve Schapel, Microsoft Access MVP


Having a problem with creating SQL Querys For My Reports.
When I Add The Tables To The Query And Create The Fields I
Receive A Message "The SQL Statement could Not Be Executed
Because It Contains Ambiguous Outer Joins, and So on"
the Following Are the Tables I Am Trying To Join and
Combine To Create A Weekly Wage Roport which shows the
total weekly wages and Total Sales For the Week Ending
Date And eventually used unbound controls in the report to
calculate percentages based on Weekly sales and Purchaese
in the different categories:
WeeklySales Table * WeeklySalesDetail Table
* WeeklySalesID Auto number WeeklysalesDetailID Auto
SalesCategoryID number Actualsales Cur
WeekEndingSate Date/Time ActualPurchases Cur
WeeklySalesID Num
WageDetailID Num

SalesCategory Table Wages Table
* SalesCategoryID AutoNumber * WageID Auto
salesCategory text WageDate D/T
ProposedPercent % TotalWages Cur
TotalHoursUsed Text
ManagerWages Cur

WageDetail Table
* WageDetailID Auto Number
SalesCategoryWages Cur
SalesCategoryHrsUsed Text
AvgSalesCategoryHrlyWage Cur
WageID Num

My Relationships are as follows:
weeklySalesDetail To WeeklySales With Ref Integrity, Both
Cascade D/U and Join Type 2
WeeklySales to SalesCategory With Ref Integrity, Both
Cascade D/U and Join Type 2
WeeklySalesDetail To WageDetail With Ref Integrity, Both
Cascade D/U and Join Type 2
WageDetail To Wages With Ref Integrity, Both Cascade D/U
and Join Type 2

Iguess my problem lies with the structure of the wages
tables and the relationships to the other ones
if anyone is able to help,

SQL As Follows
SELECT [Weekly Sales].WeekEndingDate
FROM ([Sales Category] LEFT JOIN [Weekly Sales] ON [Sales
Category].SalesCategoryID = [Weekly
Sales].SalesCategoryID) LEFT JOIN ((Wages LEFT JOIN [Wage
Detail] ON Wages.WageID = [Wage Detail].WageID) LEFT JOIN
[Weekly Sales Detail] ON [Wage Detail].WageDetailID =
[Weekly Sales Detail].WageDetailID) ON [Weekly
Sales].WeeklySalesID = [Weekly Sales Detail].WeeklySalesID;

Thank You In Advance
David
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top