Need help with Group By Total

[Stonewall]

I have a select query in ACCESS 2003. It contains a DATE field that is
necessary but I do not want to GROUP by it. It causes problems in the
resulting report when it groups by the date. If I take GROUP BY off the DATE
field and just leave it blank I get an error (something about not including
the expression DATEe as part of an aggregate function) . If I get rid of
the TOTALS view it fixes the date problem but then the SUM OF fields I am
using in the report no longer exist. How can I fix this problem so I can
have the total row in the query and NOT be forced to GROUP BY the date field?

SELECT Performances.studentid, Performances.serviceid,
Sum(Performances.manikin) AS MnqSum, Sum(Performances.live) AS LiveSum,
Sum(Performances.Hours) AS SumOfHours, Performances.date,
Sum(Performances.grade) AS SumOfgrade
FROM Performances
GROUP BY Performances.studentid, Performances.serviceid, Performances.date;

 

[KARL DEWEY]

Try this ---
SELECT Performances.studentid, Performances.serviceid,
Sum(Performances.manikin) AS MnqSum, Sum(Performances.live) AS LiveSum,
Sum(Performances.Hours) AS SumOfHours, Sum(Performances.grade) AS SumOfgrade
FROM Performances
GROUP BY Performances.studentid, Performances.serviceid;

 

[Jerry Whittle]

Which date do you want to see? You either need to remove it from the query or
use a function to pick one. For example you could do something like this in
the select statement:

Max(Performances.date) As TheLastDate

Make sure to remove it from the Group By.

You could also try Min if you would like the earliest date. First and Last
won't work unless you have a sort or Order By clause.

 

[Stonewall]

I tried this but I need the date. I just don't want to group by it.

 

[Stonewall]

I want to see ALL dates, not the first or the last, all of them. I just
don't want to group by date.

 

[KARL DEWEY]

You can not have your cake and it too.
Use a totals query joined in a select query that has the dates. The totals
will be repeated for every date. In a report have it hide duplicates.

 

[John Spencer]

Is is OK to show ALL the totals next to every date?

SELECT A.[Date], A.StudentID, A.ServiceID
MnqSum, LiveSum, SumOfHours, SumOfGrade
FROM Performances as A INNER JOIN
(SELECT Performances.studentid, Performances.serviceid,
Sum(Performances.manikin) AS MnqSum
, Sum(Performances.live) AS LiveSum,
Sum(Performances.Hours) AS SumOfHours,
Sum(Performances.grade) AS SumOfgrade
FROM Performances
GROUP BY Performances.studentid, Performances.serviceid) as B
ON A.StudentID = B.StudentID and A.ServiceId = B.ServiceID

OR would you want to have all the dates listed in sequence in one field?
Then you could use Duane Hookom's Concatenate function to get the dates
strung together - something like
1/2/2003, 1/5/2007, 12/31/2007, 1/14/2008
all in one calculated field along with the other data that is currently
returned by your query.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Stonewall]

Thanks. That answers my question.

Is is OK to show ALL the totals next to every date?

SELECT A.[Date], A.StudentID, A.ServiceID
MnqSum, LiveSum, SumOfHours, SumOfGrade
FROM Performances as A INNER JOIN
(SELECT Performances.studentid, Performances.serviceid,
Sum(Performances.manikin) AS MnqSum
, Sum(Performances.live) AS LiveSum,
Sum(Performances.Hours) AS SumOfHours,
Sum(Performances.grade) AS SumOfgrade
FROM Performances
GROUP BY Performances.studentid, Performances.serviceid) as B
ON A.StudentID = B.StudentID and A.ServiceId = B.ServiceID

OR would you want to have all the dates listed in sequence in one field?
Then you could use Duane Hookom's Concatenate function to get the dates
strung together - something like
1/2/2003, 1/5/2007, 12/31/2007, 1/14/2008
all in one calculated field along with the other data that is currently
returned by your query.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to see ALL dates, not the first or the last, all of them. I just
don't want to group by date.