CALCULATE A TOTAL ON A SUBFORM

[lorenzor]

i have to calculate the sum of many records on a subform
for an invoice form.

I been doing everything I found possible with the access help , but still can make it right.

Dont know exactly where is the root of the problem!
What should i use?
the "total" control must to be an independent
control or it can be a control from a table?

If the total control should be independent where must be located in the footpage of the subform or in footsubform?

In the control a generated the expresion SUM([Price*Qty]) is this the way to do calculate the total?

And if I locate the control in the footpage i cant see it on the preliminar view -dont know why but i cant see anything write on this area, do you know what can I do to unblocked or show this area?-; and if a locate the control in the footsubform it only shows $0.00?

Can you help me with my mess?
thanks in advance.
Erick.

 

[dawnd3]

Not sure what you mean by preliminar view or footsubform but I think I can help you.

If you want to Sum fields in a form, put the unbound control in the footer of the form. Or if it is a subform, put it in the footer of the subform. Your calculation above should be SUM([Price]*[Qty]) (notice the brackets around EACH field name)

As to why you can't see the field...do you maybe have the form in datasheet view?

Dawn

http://www.calliopeconsulting.net

 

[lorenzor]

If you want to Sum fields in a form, put the unbound control in the footer of the form. Or if it is a subform, put it in the footer of the subform. Your calculation above should be SUM([Price]*[Qty]) (notice the brackets around EACH field name)

As to why you can't see the field...do you maybe have the form in datasheet view?

I have the form in the form view, i did what you told me and still cant make it right, in the unbound control int he footer of the subform only appears a blank space.

Can you still help me, i really need to get this sum.
thanks again.
Erick.

 

[gusbrunston]

Hi, Erick:

Why not look carefully at how this is done in the sample database (Northwind) that comes with Access? Exactly the thing you want to do is demonstrated fully. If for some reason you don't have Northwind sample database, it can be downloaded from Microsoft.

Cheers. Gus Brunston An old PICKer, using Access2000. Celebrating this year my 72nd birthday and my 50th wedding anniversary.
padregus@attbi.com

 

[lorenzor]

I believe i found the problem, the thing is that the function SUM( ), does not sum an unbound control with an operation, and i have a division to sum.

what i have in the unbound control "des" : [price]/1.15 and i want to get a subtotal of : sum [qty]*[des] but cant make it right, just appears a blank space when i run the form.

Can you help again?
Thanks!
Erick.

 

[gusbrunston]

Hi, Erick

Was the sample in the Northwind database helpful?

Here's a a link to a Microsoft FAQ solution to what you're trying to do:

http://support.microsoft.com/support/kb/articles/Q208/9/98.asp

I assume you have a table for invoices, and another for invoice details, and that they are related "one to many", one invoice to many details (that's what a subform is for, of course.)

Assuming that your sub form is named "subInvDetails" and it displays the details you want on your invoice, including e.g., a text box that has the result of "quantity * price", called "txtExtPrice", then...

In a nutshell, you must put a control in the Form Footer of your sub form...call it "txtSumExtPrice". Put this in it:

=Sum([txtExtPrice])

Then, put a text box control on your main form, and enter this in it:

=[SubInvDetails].Form!txtSumExtPrice

That will capture the sum of your invoice details and put them into the main form.

If this doesn't do it for you, I'll keep trying to help. What calculation is necessary for each record in the sub form to get the extended price? How are your two tables related? etc. If you follow the directions in the MS FAQ listed above, you'll end up with a tiny database that demonstrates all of this.

Let's hear how you're doing. Gus Brunston An old PICKer, using Access2000. Celebrating this year my 72nd birthday and my 50th wedding anniversary.
padregus@attbi.com

 

[gusbrunston]

Gus, again.

Well...I followed the hyperlink after I submitted my last reply, and you won't end up with a tiny database after all. But the FAQ I pointed you to is quite clear and says about the same thing I did...lemme know...I could do a database that demos all of this and attach it to an email if you like. Gus Brunston An old PICKer, using Access2000. Celebrating this year my 72nd birthday and my 50th wedding anniversary.
padregus@attbi.com

 

[lorenzor]

hello, Gus!

Northwind, was helpfull to get the mystake that I had on my subform, but still can't make it right.

I did what you told me, step by step on your last 2 messages, but, as I see a control with the Sum() function does not sum controls with operations. I just get an Error# message in the control of the detailsinvoice subform when I run the main form.

I sent you an email, tell me later, what do you think we can do.

Thanks again.
Erick.

 

[gusbrunston]

¡Hola, Erick!

(I will post this on Tek-Tips as well, so that others who might be able to help with the problem of rounding all those decimal points will have the opportunity.)

I am pleased to be able to be of some little service to you. Here’s what I did:

I took many liberties with the design of your main form and sub form. I found it easier to work with the controls if I could arrange everything so that it could be seen at one time, on one page, without scrolling. I changed the background colors in order to highlight the things I worked on. I made some other changes, putting small margins in some of the text boxes, etc.

I created a one-to-many relationship between the tables Factura and Detalle Factura. Then I linked the main form and the sub form with the field NoFactura. That’s the way the main form knows which records to show for each invoice.

In the property sheet for the sub form, “Data”, “Data entry”, I changed yes to no. If yes, then the form will not display any of the existing invoice records—but will allow the user only to enter new records.

I designed a select query, qrySubDetalles, to select the records from the table, Detalle Factura. I then performed the calculations, dividing precio by 1.15 to get desglosado in the query, not in the form. I find it simpler to perform calculations in a query. There is one problem with dividing precio by 1.15: the result includes mucho decimal points. Therefore, on some of the invoices there is an error of 0.01, since adding all those decimal values sometimes adds a 0.01 to the total of importe. I must admit that I am not very familiar with “rounding”, which is needed to solve the problem of the unwanted 0.01. I think that the topic is covered in Tek-Tips somewhere. If you can’t find the solution, let me know, and I’ll do some looking.

You can discover the changes I made in the controls in the sub form, and the Total de factura that I added to the main form.

I will attach the changed database to this email, and wish you the best of luck and Godspeed.

Once again, I urge you to study the sample database from Microsoft, Northwind. If you do not have it, you can download it from

http://www.microsoft.com.

Yo creo que todo lo que ustedes quieren lograr se cubre en esa muestra. También, usted podría copiar las mesas, preguntas, las formas, etc. con el cambio muy pequeño, porque su negocio parece mucho como Northwind.

Me gusta haber sido de servicio. Espero que yo los haya apuntado en la dirección correcta.

Gustavo
Gus Brunston An old PICKer, using Access2000. Celebrating this year my 72nd birthday and my 50th wedding anniversary.
padregus@attbi.com

 

[Guest_imported]

I want to input data in the format hours (eg: 12300:45, 12300 hours and 45 minutes)

I been doing everything I found possible with the access help , but still couldn't find

Can you help me?
thanks in advance.
Hadee
ahardhy@hotmail.com