If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum up by cust w all details
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. |
#2
|
|||
|
|||
Sum up by cust w all details
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 hoachen wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201002/1 |
#3
|
|||
|
|||
Sum up by cust w all details
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. "hoachen" wrote: 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. |
#4
|
|||
|
|||
Sum up by cust w all details
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 "KARL DEWEY" wrote: 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. "hoachen" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|