Query Questions

[xeb]

Hi:

I have the following query:

SELECT Avg(D.tdValue) AS Average1, "" as Average2, "" as Average3, "" as Average4
FROM tsMain AS M INNER JOIN tsData AS D ON M.tsTsID = D.tdTSID
WHERE D.tdCol In (21,22,23,24,25) and Weekday(M.tsDate) = 2
UNION
SELECT "" AS Average1, Avg(D.tdValue) AS Average2, "" as Average3, ""as Average4
FROM tsMain AS M INNER JOIN tsData AS D ON M.tsTsID = D.tdTSID
WHERE D.tdCol In (21,22,23,24,25) and Weekday(M.tsDate) = 3
UNION
SELECT "" AS Average1, "" AS Average2, Avg(D.tdValue) AS Average3, ""as Average4
FROM tsMain AS M INNER JOIN tsData AS D ON M.tsTsID = D.tdTSID
WHERE D.tdCol In (21,22,23,24,25) and Weekday(M.tsDate) = 4
UNION
SELECT "" AS Average1, "" AS Average2, "" AS Average3, Avg(D.tdValue) AS Average4
FROM tsMain AS M INNER JOIN tsData AS D ON M.tsTsID = D.tdTSID
WHERE D.tdCol In (21,22,23,24,25) and Weekday(M.tsDate) = 5;

It returns results in columns that are not even and therefore can't be used on a report. I tried a second query to "sum" the results to get them all in one row but it says there is a criteria problem. So I tried it using "max" and that worked. Is using "max" okay?

Is there a way to accomplish what the above SQL does without using two queries?

Thanks,

xeb

 

[dhookom]

Try this first:
SELECT Avg(D.tdValue) AS Average1, Null as Average2, Null as Average3, Null as Average4
FROM tsMain AS M INNER JOIN tsData AS D ON M.tsTsID = D.tdTSID
WHERE D.tdCol In (21,22,23,24,25) and Weekday(M.tsDate) = 2
UNION
SELECT Null, Avg(D.tdValue) AS Average2, Null, Null
FROM tsMain AS M INNER JOIN tsData AS D ON M.tsTsID = D.tdTSID
WHERE D.tdCol In (21,22,23,24,25) and Weekday(M.tsDate) = 3
UNION...



Duane
MS Access MVP
Find out how to get great answers faq219-2884.