Plotting price changes.

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

I have a Query which stores the prices
charged by suppliers.

I need to keep a record of price
fluctuations, so this would mean storing any
changed prices somewhere.

Can someone point me in the right direction
to do this; would it mean a special table?

Please help, Frank
 
Frank said:
I have a Query which stores the prices
charged by suppliers.

I need to keep a record of price
fluctuations, so this would mean storing any
changed prices somewhere.

Can someone point me in the right direction
to do this; would it mean a special table?

Please help, Frank
Click to expand...

Hi Frank,

Just to reduce confusion, let's say firstly that queries do not "store"
anything. Table store information, and queries retrieve the stored
information.

You would like to track varying part prices over time? Does a part have
one or more suppliers? Does a supplier provide one or more part?

Assuming all three questions are "yes", you need three tables to manage
this.

One, a Suppliers table. In this table you include information only about
suppliers. Name, address, phone number, etc.

Two, a Parts table. This speaks only to parts, /without regard to
suppliers/: Description, (your) part number, assembly, etc.

Third, the "special table": This table joins the relationships between
suppliers and parts. Call it "SuppliersParts". It contains foreign keys
to identify the particular supplier and part combination, plus (getting
back to your original question) a price and effective date.

Now you can track the fluctuations of a Part price over time, by each
supplier.

Does this help?
 
message
Hi Frank,

Just to reduce confusion, let's say firstly
that queries do not "store" anything. Table
store information, and queries retrieve the
stored information.

You would like to track varying part prices
over time? Does a part have one or more
suppliers? Does a supplier provide one or
more part?

Assuming all three questions are "yes", you
need three tables to manage this.

One, a Suppliers table. In this table you
include information only about suppliers.
Name, address, phone number, etc.

Two, a Parts table. This speaks only to
parts, /without regard to suppliers/:
Description, (your) part number, assembly,
etc.

Third, the "special table": This table
joins the relationships between suppliers
and parts. Call it "SuppliersParts". It
contains foreign keys to identify the
particular supplier and part combination,
plus (getting back to your original
question) a price and effective date.

Now you can track the fluctuations of a
Part price over time, by each supplier.

Does this help?
Click to expand...




Yes, thank you.

I do have such a table arrangement called
"Products", "Suppliers", and SuppCost" with
the "SuppCost" between the other two.

"SuppCost" is used to store the
supplier/products costs, part numbers, order
quantities etc.

I have made queries based on the three above
tables to supply an order form with the costs
the suppliers charge, and this is updateable
for when prices change.

But when I change a cost the new cost
displaces the old one, and therefore
historical costs are not stored - which is
what I need.

I need a way to store the displaced cost
(with associated supplier) into a table so
that I can construct a crosstab query later
on.

Is there a standard way of doing this?

Regards, Frank
 
Ah, but you should not change the existing price in a record for that part
and supplier.
You should ADD a new record for that part and supplier with a new cost and a
new effective date.
 
Yes I see. I'll do this.
Regards


message
Ah, but you should not change the existing
price in a record for that part and
supplier.
You should ADD a new record for that part
and supplier with a new cost and a new
effective date.


message
Click to expand...
 
For this would I need a new table called
"tblCostDate" ?
With a DateID.

I am still getting the prices updating and
not adding.

Regards.




message
 
Well you could do that, but I doubt it's necessary. It is probably much
simpler to add an EffectiveDate column to your SuppCost table. Or have
you already done that?

Let's take a step back...

This seems to be the problem. If the form has controls that are
ultimately bound to the SuppCost table, changing values on the form will
update existing records. Not what you want.

What you need is a way to create a new table record, and I hope someone
with more experience than I will jump in here.

I'm thinking to create an unbound control for the cost element. Populate
it with a value from the query but capture an On Update event to fire an
Insert query in case someone "updates" the cost. Does this make any sense?

Not related to your original problem, I noticed you include "order
quantities" in your SuppCost table. In my mind, SuppCost is a fact table
about Suppliers, parts, and costs over time. What does this have to do
with orders?
 
Thanks, I'll look at this.

My thoughts were that since the linking table
between "Products" and "Suppliers" is
"SuppCost", and that this "SuppCost" table
contains two primary keys which by definition
are always a unique pair and so always
accumulate and cannot be overridden, I might
do the same with "EntryDate" in a new table
called tblDate, and have a Date ID in it, and
so make the IDs in the "SuppCost" table
contain THREE primary keys ProductID, CustID,
& DateID. This trio would always be
unique, and so accummulate.

The "order quantities" mentioned below refers
to the minimum order a supplier will accept
(a static value) , and a typical volume
ordered by us otherwise. The quantity on the
purchase order is a separate field which is
always entered manually.

Regards, Frank




message
 
Back
Top