SubQueries in Column

[Jose Perdigao]

To generate the following table,
Last AVG AVG
AREA - EQUIPMENT Day MTD YTD
Lombo-Skimmer 163 147 91
Lombo-Disposal Pile 54 35 33
Bagre-Skimmer 32 27 33
Essungo-Treater 0 0 0
Essungo-Skimmer 0 0 0
Essungo-Disposal Pile 0 0 0
Lombo-Fiscal Lact H2S 29 30 29

I created four queries,
a. A query to get the current data
SELECT PPMs.NameID, PPMs.PPM FROM PPMs WHERE (((PPMs.Date)=rptDate())) ORDER
BY PPMs.Sort;

b. A query to get average for the current month,
SELECT PPMs.NameID, Avg(PPMs.PPM) AS MTD
FROM PPMs
WHERE (((Month([Date]))=Month(rptDate())) AND
((Year([Date]))=Year(rptdate())))
GROUP BY PPMs.NameID, PPMs.Sort
ORDER BY PPMs.Sort;

c. A query to get data for current year,
SELECT PPMs.NameID, Avg(PPMs.PPM) AS YTD
FROM PPMs
WHERE (((Year([Date]))=Year(rptdate())))
GROUP BY PPMs.NameID, PPMs.Sort
ORDER BY PPMs.Sort;

d. A query using the queries above
SELECT Query1.NameID, Query1.Daily, Query2.MTD, Query3.YTD
FROM (Query1 INNER JOIN Query2 ON Query1.NameID = Query2.NameID) INNER JOIN
Query3 ON Query2.NameID = Query3.NameID
GROUP BY Query1.NameID, Query1.Daily, Query2.MTD, Query3.YTD;

Question.
Is it possible creating just one query, using sub queries in the columns and
the query is quickly?

Thanks
José Perdigão

 

[cpnet]

Maybe something like:

SELECT
p0.NameID,
p1.PPM AS DayPPM,
Avg(p2.PPM) AS YTDPPM,
Avg(p3.PPM) AS MTDPPM
FROM
PPMs p0
LEFT JOIN PPMs p1 ON
p0.NameId = p1.NameId AND
p1.Date = rptdate()
LEFT JOIN PPMS p2 ON
p0.NameID = p2.NameID AND
Year([p2.Date]) = Year(rptdate())
LEFT JOIN PPMS p3 ON
p0.NameID = p3.NameID AND
Year([p3.Date]) = Year(rptdate()) AND
Month([p3.Date]) = Month([rptdate())
GROUP BY
p0.NameID,
p1.PPM
ORDER BY
p0.Sort


p0 - this essentially gets you a distinct list of all the NameID 's in your
database
p1 - this gets you any and all data for the current day
p2 - gets you the YTD average for each NameID (even if there's no data on
report date)
p3 - gets you the MTD average for each NameID (even if there's no data on
report date)