Newbie to Access - updating a field with a calculation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!

.

Relevant Pages

  • Re: Racial Discrimination by Thai Airways ?
    ... >> dual pricing on all their products and their profits would go up too. ... What price will bring the ... >> profit and what price for Thai people will bring the maximum profit. ... What cost for people living in this area? ...
    (soc.culture.thai)
  • Re: 0800 reverse - scam warning
    ... > pricing should be based on cost+ rather than the value to ... *excessive* charges way above cost. ... > little relationship with the price, ...
    (uk.telecom)
  • Re: Windows 7 SHOULD BE FREE FOR UPGRADE
    ... Of course Ubuntu is free. ... Our companies price is $1399. ... If I had control of pricing, the price would be set @ $999. ... margins, the cost, the work, the whole she-bang. ...
    (microsoft.public.windows.vista.general)
  • RE: Pen-test pricing
    ... Your pricing should be based on estimates of average time spent on device ... You should scope your project per device and price per hour. ... cost) 50 dollars an hour, ... Subject: Pen-test pricing ...
    (Pen-Test)
  • Caster levels, schools and prices for artifacts
    ... Craft Wondrous Item, ... Price 107824 gp. ... for divine spellcasting ability is offset by the cost of damaging ...
    (rec.games.frp.dnd)