Looking Up Values

[Nick Bradbury]

I have a database used to record results of laboratory tests, the tests are
standard but the priced paid by the customer can vary. Currently I have a
standard price included as a field in the Test Table but this is difficult
when prices increase across the board as I have to close down the existing
test and re-input it with the new cost, also I have to have a routine
whereby a form is used to manually enter any alternative prices before a
routine is run to invoice the customer.

I would like to create some method whereby Access would look for the current
price charged to that customer in some kind of lookup table, acting as a
customer price list, this would be easier to maintain and be less manual
inputting.

Any ideas gratefully received.

Nick Bradbury

 

[rox]

I would like to create some method whereby Access would look for the
current price charged to that customer in some kind of lookup table,
acting as a customer price list, this would be easier to maintain and be
less manual inputting.

First off, that's not really going to be EASIER to maintain over time. I
know why you might think that now but you will find later that it's going to
be messy. Over time as you'll have 100 customers and 100 tests and about
forty bazillon prices. But having said that I do understand why you need to
do that. Just wanted to warn you about planning for that work.

You would need a table that records

1) customer id
2) test id
3) customer price
4) start price validity date
5) end price validity date (which will be a real date if the price has
"ended" and will be NULL if the price is still current.)

Your data in this table will look like this

recID1 Customer A Test A Price $10 01/01/2008 03/31/2008
recID2 Customer A Test A Price $15 04/01/2008
recID3 Customer B Test A Price $12 01/01/2008

(note that the end date of the price in line 1 does not overlap the start
date in line 2)

Open the form--where you'd have an underlying query that brings together the
customer table, the test table and the table I just suggested above (and
whatever else you have tied to that).

User would select the customer ID and narrow the selection by test ID. The
form should show you the price that has a current validity date (i.e. not a
price that's validity has expired). I know you may be thinking that's
overkill and you can get away with just the start or end dates and just do
some funky math to make it work. Please don't.

Future reporting will be easier to do if you have the start and end dates
for price validity--like accounting reports, audit billing discrepancies,
forcast profitability, etc. It makes the current valid price easy to find.
It also allows you to schedule future price increases or decreases as you
negoiate rates with the customers.

You'd need to be sure that there are never times where the table holds 2
"valid" prices....so you'd need to prevent any entry in that table to allow
the combo of same customer with the same test to have 2 prices that have no
end date.

Just to muddy the waters a bit more...do you ever manually override the
customer specific price...like, can Customer A normally get charged $10 for
a test that has a standard price of $90 but today only you're going to
charge him $5? Because that's just a whole new level of ugly that you need
to plan for with a table of "Actual Charges by Customer for A Test" (with
dates and invoice numbers).

 

[Nick Bradbury]

Hi Rox

Thanks for the advice and the clear methodology. Going to give it a try as
manual inputting of prices is causing problems, at least it is my problem if
I don't maintain the system properly.

Many Thanks

Nick Bradbury