union query error 1

[vz]

does anyone know what i am doing wrong i keep gettting an aggregate function error related to the field name:

SELECT qryAllReportEntryProblemsIncluding501s.[SubmittedBy] as Name, qryAllReportEntryProblemsIncluding501s.[DateEntered] From qryAllReportEntryProblemsIncluding501s
UNION ALL Select qryAllReportEntryProblemsIncluding501s.[Reviewer] as Name, qryAllReportEntryProblemsIncluding501s.[DateEntered] from qryAllReportEntryProblemsIncluding501s
GROUP BY qryAllReportEntryProblemsIncluding501s.[DateEntered];

thanks

 

[jitter]

Your group by clause needs to contain all columns that do not have aggregates in them. You might try to add the name column to your group by and see if that corrects the problem.

 

[vz]

I tried that but it didn't work it said tat name since i create it isn't a field. This is what i have:
SELECT qryAllReportEntryProblemsIncluding501s.[SubmittedBy] as Name, qryAllReportEntryProblemsIncluding501s.[DateEntered] From qryAllReportEntryProblemsIncluding501s
UNION ALL Select qryAllReportEntryProblemsIncluding501s.[Reviewer] as Name, qryAllReportEntryProblemsIncluding501s.[DateEntered] from qryAllReportEntryProblemsIncluding501s
ORDER BY qryAllReportEntryProblemsIncluding501s.[Name], qryAllReportEntryProblemsIncluding501s.[DateEntered];

thanks

 

[jitter]

I apologize. You can only use the Group By clause in each of the seperate Select statements in the Union Query. You can use the Order By at the end of the entire Union query. You might try:


SELECT qryAllReportEntryProblemsIncluding501s.[SubmittedBy] as Name, qryAllReportEntryProblemsIncluding501s.[DateEntered] From qryAllReportEntryProblemsIncluding501s
Group By qryAllReportEntryProblemsIncluding501s.[SubmittedBy], qryAllReportEntryProblemsIncluding501s.[DateEntered]
UNION ALL Select qryAllReportEntryProblemsIncluding501s.[Reviewer] as Name, qryAllReportEntryProblemsIncluding501s.[DateEntered] from qryAllReportEntryProblemsIncluding501s
GROUP BY qryAllReportEntryProblemsIncluding501s.[Reviewer], qryAllReportEntryProblemsIncluding501s.[DateEntered]
Order By Name, DateEntered;

I think that might work.