Combine 3 Queries into one

  • Thread starter Thread starter PC
  • Start date Start date
P

PC

I have a simple DB (1 Table). From this I want to extract Quarterly totals
based on 3 different criteria (i.e. totals for 3 locations). Currently I
have 3 seperate queries (1 for each location) which I would like to combine
into one query. The SQL view of one of the queries would be as follows:

SELECT "Qtr " & Format([Call_Date],"q") AS Period, Sum(tblCALLs.Cost) AS
[Totals For Dublin]
FROM tblCALLS
WHERE (((tblCALLS.TARGET_NUMBER) Like "1234*"))
GROUP BY "Qtr " & Format([Call_Date],"q")
ORDER BY "Qtr " & Format([Call_Date],"q");

all that would change between this and the other 2 queries would be the
criteria (i.e. LIKE 1234* would change to LIKE 4523* and Like 7567*)

The result would show the quartery totals for each location in the same
table.

Any help would be appreciated

pc..
 
PC,

I'll assume that your [Target_Number] field is what is corresponding
to a location.

I would recommend creating another table that contains the values of
the first 4 numbers of the [Target_Number], and the City that is
associated with those numbers. Then, you could simply do:

SELECT CN.City
, "Qtr " & Format([Call_Date],"q") AS Period
, Sum(tblCALLs.Cost) AS [QuarterlyTotals]
FROM tblCALLS
INNER JOIN tblCityNumbers CN
ON LEFT(tblCalls.Target_Number, 4) = CN.[Target_Number]
WHERE CN.[Target_Number] IN ("1234", "4523", "7567")
GROUP BY "Qtr " & Format([Call_Date],"q")
ORDER BY "Qtr " & Format([Call_Date],"q");


--
HTH

Dale Fye


I have a simple DB (1 Table). From this I want to extract Quarterly
totals
based on 3 different criteria (i.e. totals for 3 locations). Currently
I
have 3 seperate queries (1 for each location) which I would like to
combine
into one query. The SQL view of one of the queries would be as
follows:

SELECT "Qtr " & Format([Call_Date],"q") AS Period, Sum(tblCALLs.Cost)
AS
[Totals For Dublin]
FROM tblCALLS
WHERE (((tblCALLS.TARGET_NUMBER) Like "1234*"))
GROUP BY "Qtr " & Format([Call_Date],"q")
ORDER BY "Qtr " & Format([Call_Date],"q");

all that would change between this and the other 2 queries would be
the
criteria (i.e. LIKE 1234* would change to LIKE 4523* and Like 7567*)

The result would show the quartery totals for each location in the
same
table.

Any help would be appreciated

pc..
 
Back
Top