Could Refer to More than One Table error in subquery

[Margarita]

Hello,
I have googled this extensively, but couldn't find the answer, so decided to post here. I am working in Access 2003. I have a long MonthSalaries table of each employee's daily earnings, a main Employee table with employees' main attributes, and a Fringe table that details their alloted regular and overtime fringe rate based on title. What I want to do is total up the earnings for the month for each employee (done by subquery [SalSub]), join the Employee table to the fringe table (done by [FringeSub]), and then join the subqueries, which would result in fields of RegularSal, OvertimeSal, RegFringe, OTFringe for each employee (final join accomplished by [joinSalFringe]).

This all works out lovely, EXCEPT now that I need to get the actual amounts of fringe paid for regular time and overtime by defining fields for RegularSal*RegFringe and OTSal*OTFringe. I try to accomplish this with the following query:

Select 
[joinSalFringe].EMPLID,
[joinSalFringe].RegularSal*[joinSalFringe].RegFringe as RegFringePaid
[joinSalFringe].OTSal*[joinSalFringe].OTFringe as OTFringePaid
from
[joinSalFringe]
group by [joinSalFringe].EMPLID;

When I try to do this, I get an error saying that the field EMPLID could refer to more than one table in the from clause. I tried using just EMPLID without specifying the table, I tried using Employee.EMPLID (which is the original table in the group by clause of both the subqueries involved), but I am just not getting it. Can someone please tell me how I should be rererencing the EMPLID field in this three-layer query?
Thank you!

 

[jdraw]

Is this the actual SQL you used?

I see syntax errors

 

[jdraw]

Here is a query that works, but I don't know if this is what you intended.
I created a table and some test data. I also changed to use RegSal

Select
EMPLID,
RegSal*RegFringe as RegFringePaid,
OTSal*OTFringe as OTFringePaid
from
joinSalFringe
order by EMPLID;

 

[Margarita]

Hi jdraw,
No, this is a simplified version of the sql I used. But other than the missing commas- please tell me what other syntax issues you see. I figured I wrote that correctly in terms of syntax, so it would be good for me to get your corrections for future reference. Thanks!

After posting, I thought more about the layering of the subqueries and I decided that I don't need an extra query layer for doing the multiplication of salary and fringe benefits. I tried it in the same subquery that joins [SalSub] and [FringeSub], like:

SELECT 
[FringeSub].EMPLID,
[SalSub].TotalRegSal,
[SalSub].TotalRegSal*[FringeSub].RegFringe AS RegFringePaid, 
[SalSub].TotalOTSal, 
[SalSub].TotalOTSal*[FringeSub].OTFringe AS OTFringePaid, 
FROM 
[SalSub] INNER JOIN [FringeSub] ON [SalSub].EMPLID=[FringeSub].EMPLID;

I have to test the results more, but I skimmed them and this query seems to do it! I hope this helps someone who might be having a similar issue.

 

[Margarita]

Thanks jdraw! I will test out your solution on dummy data too.

 

[jdraw]

I saw the commas and the Group By with no aggregate function.

It seemed to me you were wanting to JOIN something like employee and SalaryInfo, but there were no join statements??
You mentioned subqueries and there were no subqueries?