How to group all dates together in MS Access 2010 query

dorianit
dorianit used Ask the Experts™
on
I have the following query in Access 2010:

SELECT Issue.EID, Issue.Closed, Issue.Created, Kana.Completed, Issue.DateTime
FROM Issue INNER JOIN Kana ON (Issue.DateTime = Kana.DateCompleted) AND (Issue.EID = Kana.EID)
GROUP BY Issue.EID, Issue.Closed, Issue.Created, Kana.Completed, Issue.DateTime
HAVING (((Issue.EID)="E107746") AND ((Issue.DateTime)>=#3/20/2014# And (Issue.DateTime)<#4/4/2014#));

The result is shows up as:

EID            Closed      Created      Completed      DateTime
E107746      0            0               8                      2014-03-20
E107746      0            0               37                      2014-03-24
E107746      0            0              44                      2014-03-27
E107746      0            0              46                      2014-03-26
E107746      0            0              58                      2014-03-21
E107746      1            0              103                      2014-03-25
E107746      30            17               28                       2014-03-28
E107746      34             7              209                      2014-04-03
E107746      36            20              81                      2014-04-01
E107746      36            20             165                      2014-04-02

I would like the result to be one line that shows the total (closed,created,completed) for the date range.  How would I do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Try this,

SELECT Issue.EID, SUM(Issue.Closed), SUM(Issue.Created), SUM(Kana.Completed)
FROM Issue INNER JOIN Kana ON (Issue.DateTime = Kana.DateCompleted) AND (Issue.EID = Kana.EID)
WHERE (Issue.EID="E107746")
GROUP BY Issue.DateTime
HAVING ((Issue.DateTime)>=#3/20/2014# And (Issue.DateTime)<#4/4/2014#));

Author

Commented:
That query still shows each date on it's own line.
Commented:
Remove the Group by clause and Issue.EID from the Select clause and run it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial