Formula Query in Access

[noob23]

In an Access database, I need to write a query that will perform a formula in a form.

When the form is open to a record, the query will take the date from FieldA and multiply it by the data in Field B and then put the total in FieldC.

I have no idea how to do this. Can someone/anyone please help?

Thank you.

 

[pdx_man]

In a blank column in the design view of the query have:

FieldC: FieldA * FieldB

 

[noob23]

how do i run this from a form?

in other words, when a user is in the form and enters the data into FieldA and FieldB, how does the user activate the query and then populate FieldC with the query's output?

also, i'm not sure how to write the query itself. how should the SQL view look?

thanks.

 

[Rich]

Base the form on the query

 

[noob23]

The form is already based on another query.

 

[Rich]

which tends to suggest that you're trying to store a calculated value, not recommended. Add an unbound textbox and set its control source to =[FieldA]*[FieldB]

 

[pdx_man]

OK, let's get to what you actually have and what you actually want ...

The form is already based on another query

Do you want this result to be on the existing form? If so, then go to the form's underlying query and add the new column in the grid. In SQL View, in the field list, have - (FieldA * FieldB) AS FieldC

when a user is in the form and enters the data into FieldA and FieldB, how does the user activate the query and then populate FieldC with the query's output?

Are the values of FieldA and FieldB being stored in a table? (The user does not have to 'activate' a query if a form is based on this query)

If the form is not based on a query, or any other datasource, then have an unbound text box with the Control Source of:

=FieldA * FieldB

 

[noob23]

yes, indeed I need to store the calculated value. i need to store the data in the table after it is calculated. can i do that? why is it not recommended? i need to do it that way. how can i do it?

 

[pdx_man]

It is not recommended because ... let's say you have a table with these 3 fields, the third being calculated from the first two fields, then the value of the first field changes by a user who does not use the form to edit the data. The third field will not get updated. Bottom line is that it opens you up to data integrity issues. So, you create a query on the table where the first two values are stored ... create a calculated field in query like I showed you above, then reference that query in any form or report.

Please explain why you need to store the calculated field?

 

[noob23]

I "need" to store the calculated field because it is information that is part of our database as a whole. I will also be using this database with a web interface I am building and the information from all the fields needs to be read from the DB to the web interface. I'd like to have the calculated number stored in the main DB table.

I'm going to try what you suggested and see how it works. If I have any trouble, may I send you a copy of the database to show you? Maybe it would be easier to help if you had it in hand. Thanks!

 

[pdx_man]

Sure,

The DB as a whole can reference the query, and pick up accurate calculations in real-time.

If the DB for the web source is read-only, then export the query's recordset to the web's db, not the underlying table.