Adding A Sum Column

[torontoguy]

I just created a query with a column to be the sum of 5 different columns



My question is: Can I create a "TOTAL" for the column "TotalItemValue"?

 

[mondo3]

can you show me the query?

 

[torontoguy]

oh the design view? sure

 

[mondo3]

actually, could you paste the sql view into the message?

 

[torontoguy]

here you are:

SELECT [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four, Sum([Zero]+[One]+[Two]+[Three]+[Four]) AS TotalItemValue
FROM [Mock Value]
GROUP BY [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four;

 

[mondo3]

are you looking to get a grand total of all of the "TotalItemValue" numbers?
if so, try this:

SELECT [Item Number], Dimensions, Zero, One, Two, Three, Four, TotalItemValue, SUM(TotalItemValue) AS GrandTotal FROM (

SELECT [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four, Sum([Zero]+[One]+[Two]+[Three]+[Four]) AS TotalItemValue
FROM [Mock Value]
GROUP BY [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four
)
GROUP BY
[Item Number], Dimensions, Zero, One, Two, Three, Four, TotalItemValue

 

[torontoguy]

are you looking to get a grand total of all of the "TotalItemValue" numbers?
if so, try this:

SELECT [Item Number], Dimensions, Zero, One, Two, Three, Four, TotalItemValue, SUM(TotalItemValue) AS GrandTotal FROM (

SELECT [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four, Sum([Zero]+[One]+[Two]+[Three]+[Four]) AS TotalItemValue
FROM [Mock Value]
GROUP BY [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four
)
GROUP BY
[Item Number], Dimensions, Zero, One, Two, Three, Four, TotalItemValue

Yes I am trying to find the grand total of all the TotalItemValue numbers.

However, when I changed my SQL stuff with what was given, it seems to have made another COLUMN named "Grand Total" after TotalItemValue and this is something that just equals the same number as the TotalItemValue.

Any other ideas you may have? Or maybe a tweak in this sql coding?

 

[mondo3]

how many rows appeared when you ran the query?
the results should have been something like (say it gave 2 rows):
[Item Number], Dimensions, Zero, One, Two, Three, Four, TotalItemValue, GrandTotal
1234,abc,100,200,300,400,1000,2001
1235,def,101,201,301,401,1001,2001
where the grandtotal is the sum of the totalitemvalues =1000+1001=2001

 

[torontoguy]

hmm

what i was getting from your code was the same number as the TotalItemValue per row

more like:

[Item Number], Dimensions, Zero, One, Two, Three, Four, TotalItemValue, GrandTotal
1234,abc,100,200,300,400,1000,1000
1235,def,101,201,301,401,1001,1001

 

[Brianwarnock]

What you are asking for is not possible in 1 query, after all the answer is not known until the query is completed.

Either create another query to sum the TotalItemValue from the irst query or create a report and place the total in the Report Footer.

Brian

 

[torontoguy]

ah alright
thanks

btw, is there a way to sum only a certain amount of records at a time?

eg.

Item # Dim Value Sum
1 Cost 1 7
1 Production 4 7
1 Repair 2 7
---------------------------------------------
2 Cost 3 9
2 Production 3 9
2 Repair 3 9
---------------------------------------------
3 Cost 4 7
3 Production 2 7
3 Repair 1 7

 

[Brianwarnock]

You can Select records based on Criteria.

Brian

 

[namliam]

Reports can do totals per group... just use the report wizard....

 

[torontoguy]

alright

i'm gonna think about this for the weekend and let you guys know how its going
thanks

 

[Brianwarnock]

Somethink bothered me about this thread so i did a rethink, OK I would still take the reort route but

SELECT [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four, Sum([Zero]+[One]+[Two]+[Three]+[Four]) AS TotalItemValue,
(Select sum(zero+one+two+three+four) as Grandtotal)
FROM [Mock Value]
GROUP BY [Mock Value].[Item Number], [Mock Value].Dimensions, [Mock Value].Zero, [Mock Value].One, [Mock Value].Two, [Mock Value].Three, [Mock Value].Four

I think might work for your original request.

Brian