Best way to maintain billing rate history?

[Sonny Jim]

I have created a table for customer bills, and a table for customer profiles which contains rates associated with them specifically. The problem is that after a new rate change has been imposed I want to be able to look at an old bill, via a report, and have it reflect the "old" rate within proper historical context. I'm looking for suggestions on how to best manage ongoing rate changes while maintaining a billing rate history in my reports?

 

[David Eagar]

You would need need to create a Rate table and use it as a lookup in the Customer profile & probably have a Billing History table also linked to your customer profile

 

[gemma-the-husky]

either that or store the actual rate used within the saved invoice. To be honest I would have thought that most people would store the saved rate, even if it could be extracted from a rate table. (even though this is not strictly necessary, as it breaks normalisation rules)

 

[Sonny Jim]

Thank you very much for your valuable insight and advice, gemma-the-husky and David. I think I understand my options much better now!

On one hand I could:

Link a Rate History table to the Customer table via the Customer ID. In addition to the Customer ID foreign key, the Rate History table would record a Rate and a Date. The queries for any associated billing report would compare the report date to the Rate Date using a SQL command like:

Nz(DLast("RateDate","Rate","RateDate<ReportStartDate"))

...and the appropriate Rate would be selected.

This would be a highly normalized approach. There would seem to be a problem, however, if there were two different rates within a reporting period.

On the other hand I could just have data entry add the rate to each billing record. This approach would involve less joins and would probably enable quicker response times which might be the best way to go in this particular situation.

 

[neileg]

Two things.

Firstly, I agree with Gemma. The pragmatic approach is to store the rate with the transaction. You may find that for whatever reason a non standard rate may be applied. If you store the rate that's fine, if you look up the rate, it's hard. Just be wary when the normalisation police come around!

Second is you reference to DLast(). Be aware that DLast() as wells as using LAST in an aggregate query will return a differnt record depending on the order of the records. Since a table is an unordered dataset, you have no idea if the records are saved in the order you expect. Even if you view the table, you aren't seeing the raw table, Access presents you with a query based on the table. And if you sort the table, you are really only sorting the display, not the data. If you want to find the most recent record, then use DMax() or MAX on a field that truly orders the data.

 

[Sonny Jim]

Thanks for your advice, neileg. I've posted this question in three forums and nearly everyone prefers the pragmatic approach. You mentioned that looking up the rate is hard. I was hoping to use a lookup for the Rate field on the Bill form that would call on stored rate information located in the customer table. This wouldn't store any history but would provide a quick reference for data entry (to avoid a taped up list of customer rates on the side of the computer) however I am concerned about what might happen when those rates change. Here is my concern: when a value in a drop down changes, will old records that contain values that no longer exist in the drop down be effected?

 

[neileg]

Doing a lookup isn't a problem. I would use a combo box that gets its values from your rate table but is bound to the relevant field in the bills table. Thus, when you enter the data you retrieve the current value in the lookup and store it in the bill. This won't change unless you do another lookup on the old record - but if you want to currupt your old data, you can do that anytime you like! Just open the old record and hit some keys! So basically, if you take appropriate steps to secure existing records from users, there's nothing Access will do to change the existing stored rates.

When I dais that the lookup was hard, I meant doing a lookup every time you wnated to view the record, rather than storing the historic value.

When you look at commercial software you will find that, by and large, only the most highend software allows you to do a retrospective change to date related data and chnge historic transactions. SAP is one that springs to mind.