Hello
I'm having trouble with one of my tables. I have a field qty and another price, the third field is called total, basically once the first two fields have been filled out i want the quantity to automatically appear. I've tried doing this using the expression builder in the default value part of the table design but it doesn't work, i get an error saying it cannot find those particular fields. I have also done it in the form design, and it works - i get the total, but when i go back to the table the value is 0 - so it only gives the value in the form view as opposed to actually changing it in the table.

Any help would be greatly appreciated,
Thanks

Mario

You should not make any attempt to store the total in your table .. as that is a calculated value.
You simply create an expression in a query or directly in the control source of a txtbox in your form or report to calculate the total when it is needed ...

RDH

i have done this - if you check the attached databse (password microsoft94)

if you check invoice main you can see that the values have worked, but then as soon as you check the table invoicevody they are all set to 0? is this something really simple that i have just missed out?

I appriciate you helping me,
Cheers
Mario

You have not listened to what I posted ...

You should not store the total in a field in your table .. you need to calculate the value "each and every time you need it" .. using an expression and .. not store the calculated value ...

RDH

so what happens in my report when it lists a record set of values how to i sum them if they are all from the same field?

for example on one invoice number you get

many of

qty - description - price

I need to make a total of all the prices?

Sorry to sound ignorant - im new to this,
cheers

Mario

Hi Mario

Access is a" RDBMS" (Relational Data Base Management System) and designed to operate around certain mathetical principals and rules such as relational algebra & set theory. Good design practices are outlined in E.F.Codd's 12 Rules of database design. A pioneer on this topic.

However Access can also be used as a sort o f " Excel on Steroids" and all kinds of things can be done with various results and consequences. Most people here are dedicated to following the concepts of good relational design.

That is why Ricky told you that you should not be attempting to store calculated values in a table. It violates the principles of good design. And a further answer would be, "Just because you can do it , does not mean you should do it"

We all were new to this and ignorant at some point, so do not take corrective responses as a personal affront.

There is an excellent Tutorial discussion in the FAQA section by GroverParkGeorge in regard to Stored Calculated Values. And the links in my signature contain a great deal on basic database design principals.

Happy Developing

Charlie

To get the total .. you place an expression in an unbound txtbox ...
Here is and example:

Then if you need grand total of this .. you use the Sum() function in another txtbox in the footer ...
Here is an example of this:

RDH

Thankyou very much for both the last two posts, Mr R Hicks, i have taken into consideration what you have written and i have got the first part to work, here is my code,

[unbound txt box "invoicePartTotal" with control source] =
=IIf([invoiceBodyQty]<=0 Or [OnNoData],[invoiceBodyPrice],[invoiceBodyQty]*[invoiceBodyPrice])

When i complile the report it works fine i get data such as this

*******************************************************
qty desc price total

Labour £200 £200
4 Wheels £150 £600
2 Wipers £25 £50
Valet £10 £10
*******************************************************

I now need to have a total of the TOTAL column, so i tried doing

=Sum([invoicePartTotal])

When i complie the report i just get #Error

The same occurs when i try different things such as

=Sum([invoiceBodyPrice]*[invoiceBodyQty])


Thankyou for your help and time,
Mario

You can not sum the control values .. you "must" sum the field values ...

So .. in the control source of a txtbox in the footer .. you need something like this:

This is asumming that the expression returns the correct total in the detail section ...
Also .. make sure that your controls are not the same name as the fields they are bound to .. or you will get an Error ...

RDH

Hello again,

Ive tried that and i still get an error, ive also renames all of the boxes that are used in the formula and its made no difference, if you would be as so kind as to look at my file i would be very happy! The password is microsoft94 and the report is called invoice, it can be tested using invoice number [10]

Thank you so much
Mario

See the attached file ...

RDH

oh wow perfect!!! thank you soo soo much you have really helped me i really appriciate it!!!!
mario

You are welcome ..

RDH