Flagging price-downs

  • Thread starter Thread starter vw
  • Start date Start date
V

vw

I have a large pricing database, and I want to flag each
time a product has historically dropped in price. I have
a separate flag field to note the drop but am not sure of
how to go about it.
what is the simplest method of writing the query to add
say an 'x' to my flag field each time the price drops in
field 'y'. Sorry, simple stuff I know.....
 
Dear VW:

It sounds like you have a column in the table that is the
EffectiveDate for each price. Each product could then have several
rows in the table with different prices starting at various dates? If
so, there's no need to flag which one is the current one and which
ones represent older prices that have been changed (or are about to
have a price change go into effect.) No new column would be needed,
and indeed I would recommend against it. The PK of the product would
include this date and that should be enough.

I don't think this is really a completely simple thing. Give me a
query on your data that includes all the other columns you need to
see, and I'll try to add the column showing which price is the
"current" price and which are historical ones.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
The data I'm working with is all historical Purchase
Order data, ie there is no 'current' price. There is an
order date,an order cost, and item number field etc, as
per a normal PO module. I can't do any damage with this
data as it's not live, so have created a field to flag
each date the price drops for each product (if at all).
Field Y = Itemcode, Field X = OrderDate, Field Z = Order
Cost. There can be 1000's of lines for each item, over a
two year period. I want to flag each time an item
actually drops in order cost, so I can Identify the date
and new lower cost (to validate price protection claims.)
as opposed to wading through and manually identifying.
Hope this helps. I'm no access guru unfortunately...
 
The reason for asking how you know when the price has dropped is because
that is the best place to run the code to set the flag. (Of course, unless
you reset this flag after noting it, it will soon become meaningless because
you won't be able to tell when a price has again dropped or hasn't changed
since the last drop.)

If you want to run code to flag things at various times, then you'll need a
field in the table that holds the "last current price", which initially will
be the same price as in the price field you already have. Then your code
would need to compare the two fields' values to see if they differ. In that
case, you don't need a separate flag field, as the second price field will
be used to identify such changes.
 
Ken, I would have said that, if you had any way of determining which
prices had changed (whether up or down) given the existing data, then
you wouldn't need the flag. But if you don't have that information,
then you don't have anything on which to base the flag. Either way,
you don't need, and shouldn't create the flag.

Then I get completely lost. What does it mean to ask whether a price
has dropped since the last drop? Isn't the answer to that rather
simple? No! But I'm afraid that's an indication of just where this
thread is going.

For purposes of information, I strongly suggest a dated price table
here, keeping all the history. I don't like the approach that reports
that a price has changed, then erases the evidence. I want to be able
to answer questions, such as, "What items went up between September
and December?" "What items are at least 10% lower than they were a
year ago?" Now, that's information. While it's a bit more work to
build, it isn't any more work to enter the data. Or how about this
one, "What was the current price of an item for an order dated
2/15/03?"

If you have the information, organize it and keep it. You'll probably
be very glad you did. The work done to enter all this information is
usually many times as expensive as the work to build a good database
to store it. Don't throw away all that user data entry effort.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top