Filling in a table using an expression

[Guest]

I am trying to fill in a field using data that I take from two other fields.
(e.g. Quantity * Cost Each = Total Cost) I can't seem to find out where to do
this, and still fill in the table it only fills in the form.

 

[chris.nebinger]

You have to bind the form control to the table field.

Keep in mind, most of the time, this is considered bad practice, as
it's difficult to continue the link between fields. It's often easier
to use a query to recalculate the value whenever you need to.

However, you show one of the areas that keeping the calculated value is
important, as the price of the unit changes over time, but the price
charged the customer is static. If you save the cost charged in the
order detail record, then there is no reason to save the total cost, as
it can be derived.


Chris Nebinger

 

[Guest]

Hi,
as a general rule we do NOT want to store calculated values.
You can calculate them at runtime on forms/reports or in queries.
Normally a form is either bound to a table and you can then bound fields to
specific table fields. As soon as a record loses focus the data will be
stored in the underlying source. If you have unbound forms you need to use
VBA to write the data to the tables, BUT as earlier mentioned it doesn't
matter either way since we do not store calculated values.
HTH
Good luck

 

[Guest]

I have not gotten to making queries yet, I was just trying to get the data
in. Now that I know this, I won't worry about it until later. Thank you for
your help.
Matt

 

[Guest]

Thanks for the help. I haven't started working on queries yet. This is my
first database, and I am mostly looking at other databases to get ideas on
how to do things. Hopefully I haven't gotten myself in over my head. This
relieves my issue for now. Thank you.

 

[chris.nebinger]

Okay, that's what I thought, but I didn't want to insult you.

In this case, lets assume your table is named OrderDetails. In it, you
have a field Quantity, and another CostEach (notice I didn't use spaces
in the table or field name. Spaces are valid, but cause you more
problems down the road)

In a query, you can show each field, then type the following in a blank
column:

TotalCost: Quantity * CostEach

Now you can run the query and see the calculation. Change one of the
values, rerun the query and the data is updated.


Chris Nebinger

 

[Guest]

No insults there, I admit I'm new. I was handed this task and said to
research how to do stuff. I was able to figure out some of the basics on my
own.

I didn't know what a query was until you told me what it can do. Now that I
know, I'll move on to using them. This helps alot. I'll let you know how
things go.

Matt