So simple, It's ridiculous

[pepegot]

Just started with numerical data and I cannot believe this! Below is a simple table. All I want to do is SUM QTY in the QTY column using the Query structure.

Rec_No PN Qty State
1 234562 23 NY
2 234562 450 NY
3 234568 288 FL
4 234569 45 FL
5 234344 3 NJ

This is what I get:

PN SumOfQty State
234344 3 NJ
234562 473 NY
234568 288 FL
234569 45 FL

I don't want totals on PN, just an old straight total of the Qty Column, but no soap!

SUM([Qty]) in criteria gives aggregate error. Sum from Sigma(Sum) gives the above. How does this thing just give you a simple sum of (809)?

Just entered QTY alone, and I got the correct answer, but it apparently does not work when other fields are present

 

[Sergeant]

If you want the total in the last row of the datasheet, you can make a totals query that contains all the same field names (aliases) and then union it to the other query. (I don't know if this is a 'best practice', but I have done it for an ASP page...and it works fine.)

 

[gray]

So Simple It's Ridiculous but I can't do it either

I have a very similar requirement... I've been trying to total a column in a report for two days now... I think I've used every syntax known to mankind since the dawn of time....including ancient Egyptian/Mesopatamian etc etc... ...

I have a table of dummy data (see "Invoices.pdf" attached) with several numerical rows formatted as; Double, General Number, 2 dec places... Let's stick to just one of them as an example... i.e. Invoice_Value_Ex_VAT

I run a Query on my table to extract certain invoices (see "VAT Query Detail.pdf" attached) and then print those results with a Report (see "VAT Report.pdf" attached).

All I want to do is total the "Invoice_Value_Ex_VAT" field for all the rows in the Report (err.. so that's about £325010.00). Also I don't want this by page but a Grand Total for that column in the Report.

As you will see from the Text21 Textbox in the footer (as shown in the .pdf), the best I've been able to do is generate an error. Summing this column (a' la spreadsheet) must be so simple it escapes me. My last effort in the textbox in the footer was:-

=Sum(Nz([Invoice_Value_Ex_VAT],0))

Would some kind soul out there please, please put an end to my misery...... thanks in anticipation.

P.S. I have set a group-by in the Invoice_number column of ascending in the Query... I have to confess though I have not quite got my head around grouping especially in Reports??

Thanks again

 

[GohDiamond]

I created Table with all your fields and populated it with your data. The table name is Sample.
I used the Query Designer and looked at the SQL which is below

SELECT Sum(Sample.Qty) AS SumOfQty
FROM Sample;

Result is 809

When you want to get the sum of a single column you can't have another column with various data because you will get a sum on each of the other data.
so if I include the state field/column I get:

333 FL
3 NJ
473 NY
--------
809 for the 3 different states listed


Happy Accessing!
Goh

 

[GohDiamond]

Gray,

You're on the right track. Put the formula in the text box control and the control in the Report footer, not the page footer of the Report. you may have to pull down some workspace in the design view to create the report footer.


Make sure that the fields in the Query you are using for the report are formatted as Currency and that the control is formatted as currency, that will help and make the control wide enought to display your answer.

Cheers!
Goh Diamond

 

[gray]

Hi

You sir, are a gentleman and a scholar! It works a treat now.... thanks for replying... much appreciated... You may even have saved me from therapy....