Carry over Problem

[wheels27]

i need access to add the total in one column and give me a grand total of that one in another,instead all the scripts i have been given are carrying the total over to the next column and not adding anything

exp:

SumOfAmounts Total
$5,021.00
$900.29
$1,500.00 7421.29

instead i get this

SumOfAmounts AvgOfAmounts Number
$5,021.00 $5,021.00 $5,021.00 $900.29 $900.29 $900.29
$1,500.00 $1,500.00 $1,500.00

SQL

SELECT [Rental Table].Number, Count([Rental Table].Number) AS CountOfNumber, [Rental Table].Amounts, Sum([Rental Table].Amounts) AS SumOfAmounts, Avg([Rental Table].Amounts) AS AvgOfAmounts,
[Rental Table].Number
FROM [Rental Table]
GROUP BY [Rental Table].Number, [Rental Table].Amounts, [Rental Table].Number
ORDER BY [Number];

 

[danvlas]

Grand total in a query (adding it to the bottom of regular rows)?
By definition, a select query is a collection of similar rows of data resulted from whatever you have specified in the SQL statement. Nothing more.
A grand total is a different info that is not (and IMO cannot) be added to the resulting set as a 'final' row.
The most you can do about it in SQL is use a domain aggregate function like DSum, but the result will be listed in its corresponding column for all resulted rows. Not to mention that it will slow down your query significantly...

Why not creating a report on the basis of what you have and add a Grand total in the Report Footer section? It's easy...
And it will give you the result you expect...in a nicer presentation.

Hope I got it right...

Dan

 

[wheels27]

How Do you do that in a report what functions do i use

 

[danvlas]

Use a wizard to create the report.
Then, in the report footer, create a text box.
Control source:
=Sum([SumOfAmounts]) or whatever field you want to sum up.

I think the wizard asks you whether to create a Grand Total or not, but I am not very sure.

HTH

Dan

 

[wheels27]

yes ok i pasted it in, it says error, yes Amounts is the field i want to use so sum of amounts, should i base this report off query or table
could my error be because i am basing it off query

 

[danvlas]

What error do you get?
You must use the EXACT name of the field - no spaces, no extra characters.

If you use the table as source, then the formula will be:

=Sum([Amounts])

Don't forget the equal sign (=).

Good luck (gotta go, end of work day here, I'll be back tomorrow)

Dan

 

[wheels27]

I got it to work but it is now asking for a paramater

 

[MichaelRed]

I think you were closer in the beginning than after the machinations. Look up aggregate query (queries) in help. do just one item at a time untill you get the basics down. I would use two queries, one for JUST the individual records and a seperate one for the aggregates. If the issue is to generate data for a report, you can do the Sum within the report (see running totals in Help). It is basically the addition pf two text boxes. One in the detail which is (usually) hidden with the running sum set to over group/all (depending on other factors), and an additional one in a footer which displays the hidden one's contents. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over