Avatar of dorianit
dorianit
 asked on

How to group all dates together in MS Access 2010 query

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?
Microsoft AccessSQL

Avatar of undefined
Last Comment
Gottler

8/22/2022 - Mon
Gottler

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#));
dorianit

ASKER
That query still shows each date on it's own line.
ASKER CERTIFIED SOLUTION
Gottler

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23