CAN I TREAT A QUERY AS A TABLE AND USE IT IN AN AGGREGATE QUERY ??? 1

[TNN]

The first two queries below, are used in the third and fourth queries. Then the fifth query uses the third and fourth queries in an aggregate query. That fifth query gives an error "NO VALUE GIVEN FOR ONE OR MORE REQUIRED PARAMETERS".

Can I not use the third and fourth queries, as I do, in the fifth query?? I thought a query was treated as a table in Access and could be used accordingly.

1st query
qrySUBTTLQTDFUTAWAGEPEREMP

SELECT EMPNO,SUM(FUTAWGE) AS TTLFUTAWGE
FROM EMPEARNPERWAGE
WHERE DATEPART('q',DATEPAID)=DATEPART('q',NOW()) AND
DATEPART('yyyy',DATEPAID)=DATEPART('yyyy',Now())
GROUP BY EMPNO
UNION SELECT EMPNO,SUM(FUTAWGE) AS TTLFUTAWGE
FROM STARTUPEMPEARN
WHERE DATEPART('q',MQYENDDATE)=DATEPART('q',NOW()) AND
DATEPART('yyyy',MQYENDDATE)=DATEPART('yyyy',Now())
GROUP BY EMPNO;

2nd query
qrySUBTTLYTDFUTAWAGEPEREMP

SELECT EMPNO,SUM(FUTAWGE) AS TTLFUTAWGE
FROM EMPEARNPERWAGE
GROUP BY EMPNO
UNION SELECT EMPNO,SUM(FUTAWGE) AS TTLFUTAWGE
FROM STARTUPEMPEARN
GROUP BY EMPNO;

3rd query
qryTTLQTDFUTAWAGEPEREMP

SELECT [EMPNO], SUM([TTLFUTAWGE]) AS TTLQTDFUTAWGE
FROM qrySUBTTLQTDFUTAWAGEPEREMP
GROUP BY [EMPNO];

4th query
qryTTLYTDFUTAWAGEPEREMP

SELECT [EMPNO], SUM([TTLFUTAWGE]) AS TTLYTDFUTAWAGE
FROM qrySUBTTLYTDFUTAWAGEPEREMP
GROUP BY [EMPNO];

5th query
SELECT SUM(qryTTLYTDFUTAWAGEPEREMP.TTLYTDFUTAWAGE)-SUM(qryTTLQTDFUTAWAGEPEREMP.TTLQTDFUTAWAGE) AS TTL
FROM qryTTLYTDFUTAWAGEPEREMP,qryTTLQTDFUTAWAGEPEREMP

TNN, Tom
TNPAYROLL@AOL.COM





TOM

http://WWW.TNPAYROLL@AOL.COM

 

[Steve101]

TNN,

Not following exactly what you're doing, but a number of things that I might comment on which could help:

(a) There is no join between between your third and fourth queries; if the third query returns 5 rows and the fourth returns 5 rows (say) then the resultset of the 5th will (before aggregation) be 25 rows. Im sure this is not as intended.

(b) Where you have a SUM in a query, you must have a corresponding GROUP BY clause. Your 5th query has one without the other, and technically is probably the cause of your problem.

(c) I'd suggest that your fifth query needs a join to relate the results of queries 3 and 4, and the removal of the SUM bit, as the aggrega5tion is performed in queries three and four.

I hope that this helps you,
Cheers,
Steve

 

[TNN]

Steve, Thank You for your comments.
What I am trying to do in that 5th query is subtract the results in query 3 from the results in query 4 on a per EMPNO basis. Queries 1 & 2 are just used in 3 & 4 to obtain the necessary results. Queries 3 & 4 do come up with the correct results and have a equal number of records with the EMPNO being unique.
Stuck at this point. Not sure how to make that subtraction with query 5.

Thank You,
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[Steve101]

Tom,

Try the following for your fifth query:

SELECT TTLYTDFUTAWAGE - TTLQTDFUTAWAGE AS TTL
FROM qryTTLYTDFUTAWAGEPEREMP, qryTTLQTDFUTAWAGEPEREMP
WHERE qryTTLYTDFUTAWAGEPEREMP.EmpNo = qryTTLQTDFUTAWAGEPEREMP.EmpNo

That should hopefully give you what you want. Note the following:

(a) The aggregation of the fields is done in queries three and four; hense no need to SUM in this query.

(b) The WHERE clause ensures that the joins between the two source queries are appropriately done.

Hope that this does the trick,
Cheers,
Steve

 

[TNN]

Steve,
Thank You, that query did what I wanted to do.
You kept saying no need to SUM but I wasn't getting it.
I see the WHERE clause making the appropriate join.
Thank You much.
TNN, Tom
TNNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM