How to output another column in this query

dorianit
dorianit used Ask the Experts™
on
The following query returns one column and one row with the Total.  How do I display the EID column also?

SELECT Sum(Issue.Closed)+Sum(Issue.Created)+Sum(Kana.Completed) AS Total
FROM Issue INNER JOIN Kana ON (Issue.DateTime = Kana.DateCompleted) AND (Issue.EID = Kana.EID)
WHERE (((Issue.EID)="E173495")) HAVING (((Issue.DateTime)>=#3/20/2014# And (Issue.DateTime)<#4/4/2014#));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Guru
Most Valuable Expert 2011
Commented:
This should work I believe:
SELECT min(Issue.EID) as EID, Sum(Issue.Closed)+Sum(Issue.Created)+Sum(Kana.Completed) AS Total
FROM Issue INNER JOIN Kana ON (Issue.DateTime = Kana.DateCompleted) AND (Issue.EID = Kana.EID)
WHERE (((Issue.EID)="E173495")) HAVING (((Issue.DateTime)>=#3/20/2014# And (Issue.DateTime)<#4/4/2014#));

Open in new window


I'm pretty sure you'll get an error if you just select the EID column without using min(), though an alternative might be to do that but also add "group by Issue.EID" to the query (before the HAVING part).
Add the column to the select clause and a group by clause

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

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