Access 2007 calculation query

[Dalek Dave]

I am designing a form for a Purchase Order System. All is in place, data tables created and linked etc. What I need is one field in a record to be the product of the preceding two fields. IE I have a field that gives Price and a field that the User inputs Quantity (These are the Field Names). I want the next field (Total) to auto fill the result of Price * Quantity. Racking Brains has produced no result, anyone got a clue for a poor delusional chap?

------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

[loyal ginger]

I think the value can be created on the fly in a query and don't need a specific field to store it. In case you really want the field, I think you have to use some update query to put the number in the field. The query can be executed when the focus leaves the quantity box. This is just a quick thought.

[David Mujica]

If I understand you correctly, you just want to have a field on your form which represents the Price*Quantity. This data will not be saved to your database table. You can achieve this by creating a textbox on your form and placing the formula in the control source. 1) From the toolbox, click on Textbox, and create a new textbox on your form. 2) Right click on the textbox, choose properties, click on the data tab. 3) In the control source field, you will enter a formula like: "=[PO]![Price]*[PO]![Quantity]" (no quotes, but use the equal sign) The [PO] represents the table name of where your data is stored, followed by a bang (!), then the field name. Hope this helps. :cool:

[Dalek Dave]

I do need the field, and thanks for the advice, I will try that. It didn't occur to me to try that approach, I naturally (and wrongly it seems), assumed that there was some maths processing ability in Access.

------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

[Dalek Dave]

Further to my answer to a different approach (above), This also has possibilities, as although it is an Access DB, it is being run via a VB form at the front end, so I can post the toatl back into a field. Cheers to both of you for two different approaches.

------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave

[Avi Berger]

The approach loyal ginger referred to would be to use something like:

SELECT Price, Quantity, Price * Quantity AS Total
FROM table
WHERE condition

This gives you a record set that has a total field, without saving one on the disk. For an entry form you would use something like David Mujica's suggestion for the UI. This does violate your stated requirement of saving the field to disk. You might want to explore getting that requirement changed. All database fanatics will tell you so. :) According to the rules of relational databases, the total field does not belong stored in the table. It would be a violation of "normal form" (3rd normal form if I am not mistaken). Practically, it adds no information not already present and is subject to the creation of data anomalies from updates. If someone else wrote a program that uses the same database and in one place changes quantity, but forgets to update total, well, you get the picture. Triggers could be a way to deal with this, but the Access (Jet) backend doesn't have them. If you use an Access front end to a different database backend, that backend might have this capability.

Please do not read this signature.