Table Stucture

  • Thread starter Thread starter David
  • Start date Start date
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, i give my Thanks in Advance
David
 
-----Original Message-----
Please post the SQL of the query.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

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, i give my Thanks in Advance
David

SELECT [Weekly Sales].WeekEndingDate
Click to expand...
Click to expand...
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 Scott
 
Back
Top