I have a query pulling data from multiple tables. So each ID has multiple amounts, and I'd like the sum of the amounts for each ID.
Example of what my query results look like:
ID Amount
--- --------------
1 5
1 15
1 2
2 6
2 6
3 12
3 4
3 7
I have a query pulling data from multiple tables. So each ID has multiple amounts, and I'd like the sum of the amounts for each ID.
Example of what my query results look like:
ID Amount
--- --------------
1 5
1 15
1 2
2 6
2 6
3 12
3 4
3 7
Write a query with groups [id] and sum[amount]?
Sent from my iPhone using Tapatalk
Query was this:
SELECT IvcTbl.IvcID, IvcTbl.IvcDt, IvcTbl.CustId, IvcTbl.OrdId, IvcTbl.Amt, IvcTbl.SlsTax, CustTbl.Customer, CustTbl.Addr1, CustTbl.City, CustTbl.State, OrdTbl.ShpVia
FROM (CustTbl INNER JOIN OrdTbl ON CustTbl.[CustId] = OrdTbl.[CustId]) INNER JOIN IvcTbl ON OrdTbl.[CustId] = IvcTbl.[CustId]
WHERE (((IvcTbl.IvcDt) Between #2/1/2013# And #2/28/2013#) AND ((IvcTbl.CustId)="stuff") AND ((CustTbl.State)="__"))
ORDER BY IvcTbl.IvcDt;
And is now:
SELECT IvcTbl.IvcID, IvcTbl.IvcDt, IvcTbl.CustId, IvcTbl.OrdId, SUM(IvcTbl.Amt) AS TotalAmount, IvcTbl.SlsTax, CustTbl.Customer, CustTbl.Addr1, CustTbl.City, CustTbl.State, OrdTbl.ShpVia
FROM (CustTbl INNER JOIN OrdTbl ON CustTbl.[CustId] = OrdTbl.[CustId]) INNER JOIN IvcTbl ON OrdTbl.[CustId] = IvcTbl.[CustId]
WHERE (((IvcTbl.IvcDt) Between #2/1/2013# And #2/28/2013#) AND ((IvcTbl.CustId)="stuff") AND ((CustTbl.State)="__"))
ORDER BY IvcTbl.IvcDt,
GROUP BY IvcTbl.IvcID;
Getting a syntax error on the last line. I've tried shuffling things around, and the error isn't going away.
Remove comma at end of ORDER BY Line?
You cannot do this manually, you must let Access do it for you. Take your original query, go into Design View, click on the Totals icon at the top and see what happens.
What if I only want to group the IDs together? A pop says "Your query does not include the specified expression 'column_name' as part of an aggregate function" for every single column that I leave blank in the Total row.
Your first post says all that you want is the ID and the amount. A grouped query requires all fields to have something in the total line.
Ahh okay. The person who wants this info needs other columns to be shown as well, but I was just showing the relationship between the ID and amount in my first post. Sorry for confusion!
You will probably need two queries. One will contain all the info in your SQL statement, join in the second query which just contains the amount totaled.