Need Query to stop grouping by Date!

[Elwood07]

Hi,

I have a query that requires a date range input, so I've added the transaction date to the query fields and added a parameter, but it does not show on the query output.
The query results show personnel number, ssn, name and total gratuity.


I need the query to sum by each person's name or personnel number, but it looks like it is keeping them separated by date.
In the design i've chosen Sum totals, and every field defaults to "Group By". I've changed my gratuity to "Sum", but it won't allow me to delete the group date on the transaction date field.

I can't have duplicate names show on this list.
Any help would be great!

[JoeM]

Can you change your query to SQL View, and copy and paste the SQL code here for us to see?

[June7]

Does the date field have WHERE instead of GROUP BY?

What do you mean by 'delete the group date'?

Could build a report and use its Sorting & Grouping features with aggregate calcs in group and report footer sections. This will allow display of detail records as well as summary data.

[Elwood07]

Here it is with the Sum Total

SELECT tblPayee.SAPID AS [Personnel Number], "" AS SSN, tblPayee.PayeeName, tblOutlet.CostCenter AS [Cost Center], "" AS [Rate Override], Sum(tblPayee.GratuityAmount) AS 7550
FROM (tblServer INNER JOIN (tblOutlet INNER JOIN tblOutletServer ON tblOutlet.OutletName = tblOutletServer.OutletName) ON tblServer.ServerID = tblOutletServer.ServerID) INNER JOIN (tblPayee INNER JOIN tblServerPayee ON tblPayee.PayeeID = tblServerPayee.PayeeID) ON tblServer.ServerID = tblServerPayee.ServerID
GROUP BY tblPayee.SAPID, "", tblPayee.PayeeName, tblOutlet.CostCenter, "", tblServer.TransDate
HAVING (((tblServer.TransDate) Between [Begin Date Range:] And [End Date Range:]))
ORDER BY tblPayee.PayeeName;

Thanks for looking into!

[JoeM]

Try moving your Date criteria out of the "HAVING" clause and enter it into a WHERE clause, and remove the date from your GROUP BY clause, since it is not in your SELECT clause. You also do not need to include any blanks or hard-coded values in your GROUP BY clause, i.e.

SELECT tblPayee.SAPID AS [Personnel Number], "" AS SSN, tblPayee.PayeeName, tblOutlet.CostCenter AS [Cost Center], "" AS [Rate Override], Sum(tblPayee.GratuityAmount) AS 7550
FROM (tblServer INNER JOIN (tblOutlet INNER JOIN tblOutletServer ON tblOutlet.OutletName = tblOutletServer.OutletName) ON tblServer.ServerID = tblOutletServer.ServerID) INNER JOIN (tblPayee INNER JOIN tblServerPayee ON tblPayee.PayeeID = tblServerPayee.PayeeID) ON tblServer.ServerID = tblServerPayee.ServerID
WHERE (((tblServer.TransDate) Between [Begin Date Range:] And [End Date Range:]))
GROUP BY tblPayee.SAPID, tblPayee.PayeeName, tblOutlet.CostCenter
ORDER BY tblPayee.PayeeName;

[Elwood07]

That was so amazing, Thank You for your quick fix!

Do you know why, even though I have a grand total set up to show on the query result (and it does), when I export it, the grand total won't show up. I did at one point, but along the way with the fiddling around I've been doing, it no longer exports. Is there another quick fix to that, do you think?

Thanks Again!

[JoeM]

I don't work too much with using Grand Totals within queries, but how exactly are you exporting the query?
What file type are you exporting it to?

Note. If you were using the Query Builder to build your query, the way that you would do what I do in the SQL code directly is under your Date field, uncheck the "Show" box and change the Totals row value from "Group By" to "WHERE".