Re: Plotting price changes.
- From: "Frank Martin" <pj@xxxxxxxxxx>
- Date: Fri, 29 Sep 2006 10:47:14 +1000
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
"Smartin" <smartin108@xxxxxxxxx> wrote in
message
news:UZWdnX7dy6c39IHYnZ2dnUVZ_tydnZ2d@xxxxxxxxxxxxxxx
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...
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.
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?
Frank Martin wrote:
For this would I need a new table called
"tblCostDate" ?
With a DateID.
I am still getting the prices updating and
not adding.
Regards.
"John Spencer" <spencer@xxxxxxxxx> wrote
in message
news:uWW$1Mv4GHA.1256@xxxxxxxxxxxxxxxxxxxxxxx
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.
"Frank Martin" <pj@xxxxxxxxxx> wrote in
message
news:%233xzecs4GHA.292@xxxxxxxxxxxxxxxxxxxxxxx
"Smartin" <smartin108@xxxxxxxxx> wrote
in message
news:fZednaW-QKegtobYnZ2dnUVZ_rSdnZ2d@xxxxxxxxxxxxxxx
Frank Martin wrote:
I have a Query which stores the pricesHi Frank,
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
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?
--
Smartin
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
--
Smartin
.
- References:
- Re: Plotting price changes.
- From: Frank Martin
- Re: Plotting price changes.
- From: Smartin
- Re: Plotting price changes.
- Prev by Date: Re: replace null values
- Next by Date: Re: IsEmpty() not giving expected results
- Previous by thread: Re: Plotting price changes.
- Next by thread: ACCESS QUERY: Get Values after Grouping and finding Minimum
- Index(es):
Relevant Pages
|
