query sum issues

[wheels27]

I need the amounts column to add all three values up as one whole to and not to carry the totals to sumoftotal column,
there should be only one number in the sum of total column and that is the total of all three. next i need to add the assets up and divide by the total number of assets.

SELECT [Rental Table].Number, [Rental Table].SERVICE_TAG, [Rental Table].Amounts, [Rental Table].Amounts AS SumofTotal, [Rental Table].[Asset Count]
FROM [Rental Table];

is my sql

my table reads

Amounts SumofTotal Asset Count
$5,021.00 $5,021.00 1
$900.29 $900.29 1
$1,500.00 $1,500.00 1

 

[Steve101]

You need to learn about aggregate (or total) queries; an example of what you're looking for:

SELECT [Number], Count([Number]), Sum([Amounts]
FROM [Rental Table]
GROUP BY [Number]
ORDER BY [Number]

You would need variations on the above. I suggest that you have a bit of a 'play' with this, just based on a single table. Easiest to do it in query design view (use the View, Totals option to go into Aggregate 'mode'.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)