Total from multiple records

[TrekMietta]

Software: Access 2007

I have a table with these fields:
orderno, sku, item, qty, price, sub

whenever i insert an item it creates a new record: i.e.
SKU ITEM QTY PRICE SUB
RECORD1: PK999 Pink Dress 2 9.99 19.98
RECORD2: PK998 Black Dress 1 4.99 4.99

On the form i also have an unbound text box, how do i get this box to show the total from all of the SUB fields e.g. 24.97, i have already got the sub field to automatically total the qty and price fields using "=[price]*[qty]".

If i enter "=[sub]" it only shows me the total for the selected record, i need it to total all of the records on screen, please note that only records with the same orderno are displayed.

Regards and thank you in advance for any help offered

 

[vbaInet]

=Count(*) in a textbox placed in the footer of your subform.

Refer to that textbox in your main form (using the Expression Builder) to get the value.

OR

A more reliable way is counting the recordset if you have thousands of records:

Dim rst as recordset
set rst = me.recordset

rst.recordcount

 

[SOS]

In the form's FOOTER you put:

=Sum([price]*[qty])

But make sure that the control that houses price and qty are not named the same as the field names or else you'll get #Name in the answer. Make sure that the names are changed and then you use the FIELD names for this.

 

[Galaxiom]

=Sum[sub]
Edit: No actually that won't work. I was misled by your incorrect use of terminology.

You said you had a field with the line subtotal. You didn't. You had a CONTROL with the subtotal.

Fields are in tables and queries (including Record Source queries).
Controls are on forms and reports.

SOS has the right answer.

 

[SOS]

=Sum[sub]
...............

betcha it won't work. To sum them you need to recreate the calculation as it will give an error #Name or #Error if you try to sum a calculated control.

 

[SOS]

Oh, and you should use the Nz function when doing it too, to handle nulls:

=Sum(Nz([price],0)*Nz([qty],0))

 

[vbaInet]

Did I say Count? I must be dozy. It's past my bed time anyway

 

[Galaxiom]

My edit was so soon after I posted that it didn't even show as an edit in the footer.
Must have a minute of grace before it becomes an edit I guess.

When I saw SOS post the expression with the recalculation I realised he would not do this for no reason. Then I saw the equal sign in the "field" (sic) expression.

I think the use of accurate terminolgy needs to be drummed into posters.

 

[vbaInet]

My edit was so soon after I posted that it didn't even show as an edit in the footer.
Must have a minute of grace before it becomes an edit I guess.

When I saw SOS post the expression with the recalculation I realised he would not do this for no reason. Then I saw the equal sign in the "field" (sic) expression.

I think the use of accurate terminolgy needs to be drummed into posters.

Yeah something like a one minute grace and I think it also depends on whether the poster has read it too. Not sure.

So where's Galaxiom At at the moment? Not at home?

 

[Galaxiom]

At work. I spend a little while here most days because reading other people's problems has saved me from so many unanticipated design traps that it is worth it. I regularly work beyond my mandated hours anyway.

 

[TrekMietta]

Thank you for your comments and taking the time to help me, but so far none of the things you have said work:

1) =Count(*) : this only gives me a total number of records, i want to add up the SubTotal CONTROL.

2) =Sum(Nz([price],0)*Nz([qty],0) : This just says #Error, or if i'm lucky it gives me a total but it adds up wrong.

View the image it will give you a better understanding of what i'm trying to do

Regards

 

[SOS]

Yes, the count is working as it shows. But I didn't say to use count. It shows 3 which is the number of records on the subform.

But you need to change it from =Count(*)

to what I said:
=Sum(Nz([price],0)*Nz([qty],0)

And it should give you a total of 124.

 

[SOS]

And if you can't get it, post your database here for us to do it quickly for you.

 

[vbaInet]

I regularly work beyond my mandated hours anyway.

I hope you recoup in cash or in kind. I bet you have regular meetings with the Personnel department hehe!

 

[TrekMietta]

THANK YOU SO MUCH, that has been bugging me since this morning, thank you all for your help it's much appreciated. Regards Trek

 

[SOS]

Did you get it?

 

[TrekMietta]

I bet you have regular meetings with the Personnel department hehe!

I am the personnel department, and accounting, and the big bad boss! I run my own company just trying to make my life easier using a database instead of loads of excel and word documents.

I'm not a large company and i'm not an expert in access, but you guys help a lot, can i donate towards the forum? if so where?

And yes it now works! Thanks

Regards
Trek

 

[Galaxiom]

I hope you recoup in cash or in kind. I bet you have regular meetings with the Personnel department hehe!

I get a lot of respect here. I value that greatly after my last job where I worked hard for a long time while constantly frustrated in my efforts to achieve by a hoard of ignorant, arrogant fools.

 

[vbaInet]

I am the personnel department, and accounting, and the big bad boss! I run my own company just trying to make my life easier using a database instead of loads of excel and word documents

I attest to that. Although, Excel does have its place. It cannot be completely replaced by Access.

I'm not a large company and i'm not an expert in access, but you guys help a lot, can i donate towards the forum? if so where?

Maybe SOS has a website for collecting donations? Or maybe Jon

 

[vbaInet]

I get a lot of respect here. I value that greatly after my last job where I worked hard for a long time while constantly frustrated in my efforts to achieve by a hoard of ignorant, arrogant fools.

Yes, it's hard to get your message across to people who feel they know when they really are clueless. I'm glad you're now in a job where your efforts are appreciated.