Combine Queries into SQL statement

[Juls]

Hi,
Can I combine the 3 queries below into 1 SQL statement?

QR1
SELECT AgenCall.Log, Sum(AgenCall.[3]) AS SumOf3
FROM AgenCall
WHERE (((AgenCall.Dte)>=[Start] And (AgenCall.Dte)<=[End]))
GROUP BY AgenCall.Log;

QR2
SELECT SaleLog.Stref, Sum(SaleLog.first) AS SumOffirst
FROM SaleLog
WHERE (((SaleLog.Dte)>=[Start] And (SaleLog.Dte)<=[End]))
GROUP BY SaleLog.Stref;

QR3 (this gives the results)
SELECT Staff.FNme, Staff.StRef, Staff.Tel, QR1.SumOf3, QR2.SumOffirst
FROM QR1 INNER JOIN (Staff INNER JOIN QR2 ON Staff.StRef = QR2.Stref)
ON QR1.Log = Staff.Tel
WHERE (((Staff.CentCode)=[Centre Code]) AND ((Staff.EnDate) Is Null Or
(Staff.EnDate)>=[End]))
GROUP BY Staff.FNme, Staff.StRef, Staff.Tel, QR1.SumOf3,
QR2.SumOffirst;

Everything I've tried increases the actual results.
Many Thanks
Juls
NB. I'm new to Access

 

[Michel Walsh]

Hi,


You can use a UNION ALL, but then , each SELECT need to have the same number
of fields. Something like:

SELECT Staff.FNme, Staff.StRef, Staff.Tel, QR1.SumOf3, QR2.SumOffirst
FROM QR1 INNER JOIN (Staff INNER JOIN QR2 ON Staff.StRef = QR2.Stref)
ON QR1.Log = Staff.Tel
WHERE (((Staff.CentCode)=[Centre Code]) AND ((Staff.EnDate) Is Null Or
(Staff.EnDate)>=[End]))
GROUP BY Staff.FNme, Staff.StRef, Staff.Tel, QR1.SumOf3,
QR2.SumOffirst

UNION ALL

SELECT AgenCall.Log, Null, Null, Sum(AgenCall.[3]) , Null
FROM AgenCall
WHERE (((AgenCall.Dte)>=[Start] And (AgenCall.Dte)<=[End]))
GROUP BY AgenCall.Log

SELECT Null, SaleLog.Stref, Null, Null, Sum(SaleLog.first)
FROM SaleLog
WHERE (((SaleLog.Dte)>=[Start] And (SaleLog.Dte)<=[End]))
GROUP BY SaleLog.Stref




Hoping it may help,
Vanderghast, Access MVP

 

[Juls]

Hi,
Thanks for your help, the statement still refers to the 2 queries QR1 &
QR2? would this not be the same as just using QR3? I need to remove the
references to QR1 & QR2. Sorry if I'm not getting something.
Cheers
Juls

 

[John Spencer]

I can't think of a way to do it with the names that you have for fields and
with the embedded parameters. JET SQL (native Access) won't let you use
square brackets inside subqueries. That means you couldn't use the
parameters [Start] and [End] AND you couldn't use the field [3].

Normally, you would use something like the following -- this will not work.
If you can rename the field [3] in the table and know how to build the SQL
using VBA you could get the parameter values, build the SQL string and use
that in place of the query (or change the SQL property of the stored query).

SELECT Staff.FNme, Staff.StRef, Staff.Tel, QR1.SumOf3, QR2.SumOffirst
FROM
(SELECT AgenCall.Log, Sum(AgenCall.[3]) AS SumOf3
FROM AgenCall
WHERE (((AgenCall.Dte)>=[Start] And (AgenCall.Dte)<=[End]))
GROUP BY AgenCall.Log ) AS QR1
INNER JOIN (Staff INNER JOIN
(SELECT SaleLog.Stref, Sum(SaleLog.first) AS SumOffirst
FROM SaleLog
WHERE (((SaleLog.Dte)>=[Start] And (SaleLog.Dte)<=[End]))
GROUP BY SaleLog.Stref ) as QR2
ON Staff.StRef = QR2.Stref)
ON QR1.Log = Staff.Tel
WHERE (((Staff.CentCode)=[Centre Code]) AND ((Staff.EnDate) Is Null Or
(Staff.EnDate)>=[End]))
GROUP BY Staff.FNme, Staff.StRef, Staff.Tel, QR1.SumOf3,
QR2.SumOffirst;

 

[Juls]

Hi John,
I' ve renamed the field 3 "Bob" & given the [End] & [Start] parameters
actual values, when i try to run it I get a 'Syntax error in FROM
clause'?
The approach you've taken is the kind of thing i was looking for but I
can't get away from the sytax error>
Cheers
Juls

 

[John Spencer]

Can you post the SQL statement that is giving you the error? That always
helps when trying to diagnose problems.