Newbie to Access - updating a field with a calculation

  • Thread starter Thread starter a3c05776
  • Start date Start date
A

a3c05776

Thanks in advance to any help with this

I have a simple mdb project for a shopping list (I'm using this as a
learning experience)

In the project I have a tables for Items, Item Versions and Pricing.

ITEMS Table:
ID_ITEM (autonumber - primary key)
Item Name (text)

ITEM VERSIONS table:
ID_ITEM_VERSION (autonumber - primary key)
ID_ITEM (foreign key to ITEMS table)
Size (long integer)
Size Unit (text)

PRICING table:
ID_PRICING (autonumber - primary key)
Store Name (text)
Price (currency)
Date (date)
Cost Per Unit (number)

The tables are set up with a relationship such that after entering a
new item (Milk) I can then add several 'versions' or sizes, brands,
flavours, etc. For example, I might have Milk with three entries, 1
Gal 2%, 1 Gal Skim and 1 Gal Chocolate.
Then for each 'version' of an item, I have the various prices from
each store.

This should enable me to compare prices for the same size, weight,
qty, etc of various items across various stores.

My challenge:
What I am having trouble with is creating a form or update query so
that when I enter the price for an item version in the pricing table,
the Cost Per Unit updates. The calculation is simple; Price / Size.

I'm having difficulty understanding how to create the code, macro or
update query (whichever it is that I need) to take the information
from two different tables (the size from the ITEM_VERSION table and
the price from the PRICING table) and updating the Cost Per Unit in
the PRICING table.

I'd like it to post the calculation into the PRICING table updating
the CPU field, so that I can use that data in reports, exports to
Excel and so on.

Once I have this done, I should be able to compare prices for various
sizes of items based upon their cost per unit. Essentially, I'm
trying to create a way to cost compare apples to apples (pun
intended!)

I can send the mdb file to anyone who is willing to help out.

Thanks!
 
What I am having trouble with is creating a form or update query so
that when I enter the price for an item version in the pricing table,
the Cost Per Unit updates. The calculation is simple; Price / Size.
Click to expand...

That field should *SIMPLY NOT EXIST* in your table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.


John W. Vinson [MVP]
 
Back
Top