lookup issue - i don't want to refresh!!

[Guest]

I have a billing database. For any given client i have a lookup billing rate
table that might have 30 different rates in it, depending on insurance
company. I have a form based on the client table that has a control that
looks up that billing table and links the client to the billing rate in the
client table. Problem is if an insurance company changes its billing rate, i
need to update the lookup table, so that for all future client records the
updated rate is entered, but i don't want to alter the rate for the
previously entered rates.

i have understood from many readings of these forums that when you link
tables you only link the primarykey-foreignkey and use queries to display the
related data, but i'm thinking (perhaps incorrectly) that i'm going to have
to store the actual billing rate at the time it's entered into the client
table, so it doesn't change when i change the lookup table.

Am i on the right track here?? Your help is gratefully accepted (in advance)

Cheers
Topher

 

[Jeff Boyce]

Two approaches to consider.

First, if you will only ever have ONE relevant billing rate in your lookup
table, then yes, you will need to store the
"billing-rate-at-time-of-data-entry".

However, if you include a pair of fields in your billing rate lookup table
to hold BeginEffectiveDate and EndEffectiveDate, you could store only the
BillingRateID for the billing rate that was in effect at the time of data
entry. That way, your "old" records continue to point at the "old" rates,
but newly entered records can point to the "currently-available" rates.

The former method is simpler, but could lead to confusion because the
current rates won't match the rates recorded with your records, and if a
mistake were made, there'd be no way to discover what the rate should have
been from within the database.

The latter method adds a "history" of rates, making it possible to mine that
data, plus to reconstruct what the rate would have been when the record was
written.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Thanks for replying...i suspect that the simpler first option will be ok for
my purposes, but being a bit of sticky beak about Access i'd also like to
know the nitty-gritty of how exactly you would do the second approach. So
could you humour me and answer me two questions

1. for the first approach, what's the best way to "embed" the rate in the
underlying table from a form

2. here is my billing table which i lookup from the main form, storing the
RateID in the underlying main table;

RateID
InsuranceFund
Rate

How do i use your second suggestion in this scenario? Your help is truly
appreciated

Cheers
Chris

 

[Jeff Boyce]

See comments in-line below...

Thanks for replying...i suspect that the simpler first option will be ok for
my purposes, but being a bit of sticky beak about Access i'd also like to
know the nitty-gritty of how exactly you would do the second approach. So
could you humour me and answer me two questions

1. for the first approach, what's the best way to "embed" the rate in the
underlying table from a form

Add a field to the underlying table. Add a combobox on the form. Bind the
combobox to the new field, and set its Row Source to the rates in the lookup
table. That way, when you pick a rate, it gets stored in your underlying
table.

2. here is my billing table which i lookup from the main form, storing the
RateID in the underlying main table;

RateID
InsuranceFund
Rate

How do i use your second suggestion in this scenario? Your help is truly
appreciated

Add the two fields suggested (BeginEffectiveDate & EndEffectiveDate) to the
lookup table. I made the assumption that you would have a field in your
underlying table that holds the date the record was created. You could use
selection criteria in a query (behind the form) to ensure that the rates
displayed as choices via your lookup (i.e., a combobox) were the rates
effective as of the record creation date.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/