Sum up by cust w all details

[hoachen]

Table Data
CustID Item# ItemName Amount
123 A11 Abc 11.5
123 A13 cde 10
123 A16 fgh 20.5
111 A11 Abc 11.5
111 A16 fgh 10

Output example
123 A11 Abc 11.5
123 A13 cde 10
123 A16 fgh 20.5
42

111 A11 Abc 11.5
111 A16 fgh 10
21.5


I am trying to get it the total of each customer which will look like above
example on Access 2003
I can group it by CustID and the amt but when I make it display on Item# and
itemName the total sum will not group together anymore
I've tried using form or report but unsucessful. Anyone have the solution
which will display like the output example?

Thanks for everyone try to help.

 

[KenSheridan via AccessMonster.com]

You can do it in a query, but the total will be an additional column, not row:


SELECT CustID, [Item#], ItemName, Amount,
(SELECT SUM(Amount)
FROM YourTable As T2
WHERE T2.CustID = T1.CustID)
AS TotalAmount
FROM YourTable As T1;

In a report group the report by CustID and give it a group footer. Put the
CustID, [Item#], ItemName and Amount in the detail section and the
TotalAmount in the group footer to give the desired layout.

BUT, while the above will work, its pretty pointless as you simply need to
base the report on:

SELECT CustID, [Item#], ItemName, Amount
FROM YourTable;

Again group it by CustID and give it a group footer. In the footer add a
text box with a ControlSource of:

=Sum([Amount])

Theoretically it is possible to return a result table with the totals as
separate rows by using a UNION operation, but you'd have to show the CustID
in the total row too as otherwise there'd be no way of knowing which customer
each total is for. However, given the ease of doing it one or other of the
above ways a UNION operation like this would be akin to Samuel Johnson's
famous dictum:

"A woman's preaching is like a dog walking on its hinder legs. It is not
done well; but you are surprised to find it done at all"
James Boswell - Life of Samuel Johnson, 1791

Not very PC these days, but you take the point.

Ken Sheridan
Stafford, England

 

[KARL DEWEY]

This places the total at the top of each but if you either use another query
or in a report you could get it the way you want.

SELECT hoachen.CustID, hoachen.[Item#], hoachen.ItemName,
Sum(hoachen.Amount) AS SumOfAmount
FROM hoachen
GROUP BY hoachen.CustID, hoachen.[Item#], hoachen.ItemName
ORDER BY hoachen.CustID, hoachen.[Item#], hoachen.ItemName
UNION ALL SELECT hoachen.CustID, Null, Null, Sum(hoachen.Amount) AS
SumOfAmount
FROM hoachen
GROUP BY hoachen.CustID, Null, Null
ORDER BY hoachen.CustID;

 

[hoachen]

Thank you very much for your solution. Yup, i tried another solution like
create another but it kind of troublesome. This solution work great.

Thanks again

This places the total at the top of each but if you either use another query
or in a report you could get it the way you want.

SELECT hoachen.CustID, hoachen.[Item#], hoachen.ItemName,
Sum(hoachen.Amount) AS SumOfAmount
FROM hoachen
GROUP BY hoachen.CustID, hoachen.[Item#], hoachen.ItemName
ORDER BY hoachen.CustID, hoachen.[Item#], hoachen.ItemName
UNION ALL SELECT hoachen.CustID, Null, Null, Sum(hoachen.Amount) AS
SumOfAmount
FROM hoachen
GROUP BY hoachen.CustID, Null, Null
ORDER BY hoachen.CustID;

--
Build a little, test a little.

Table Data
CustID Item# ItemName Amount
123 A11 Abc 11.5
123 A13 cde 10
123 A16 fgh 20.5
111 A11 Abc 11.5
111 A16 fgh 10

Output example
123 A11 Abc 11.5
123 A13 cde 10
123 A16 fgh 20.5
42

111 A11 Abc 11.5
111 A16 fgh 10
21.5


I am trying to get it the total of each customer which will look like above
example on Access 2003
I can group it by CustID and the amt but when I make it display on Item# and
itemName the total sum will not group together anymore
I've tried using form or report but unsucessful. Anyone have the solution
which will display like the output example?

Thanks for everyone try to help.