Best way to maintain billing rate history? 1

[Xeseus]

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?

 

[dhookom]

If you rate depend on a point in time, you could create a rate history table that lists effect dates of the rate. If discounts and other things might change the rates then I would store the rates in the detail table.

Duane MS Access MVP

 

[Xeseus]

Thanks dhookom! So I would link the 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<#2/6/2007#"))

...and the appropriate Rate would be selected.

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

Of course, I could just have data entry add the rate to each billing record.

 

[dhookom]

I think it would be a little more complex. I would grab the most recent applicable rate with a subquery.

SELECT [multiple fields....],
(SELECT TOP 1 Rate FROM tblCustRateHist cr 
WHERE cr.CustomerID = cb.CustomerID AND cr.RateDate<=cb.SaleDate 
ORDER BY cr.RateDate DESC) as TheRate
FROM tblCustBills cb

This makes some assumptions about your tables and fields.

Duane MS Access MVP

 

[lespaul]

I would probably store the rate with the billing information. Yes it breaks the rules of normalization, but it makes it much easier to retrieve the rate that was in effect when the billing occurred.

HTH

Leslie

 

[dhookom]

There are a lot of scenarios where I would agree with storing the rate with each detail. Northwind does this with the UnitPrice in the Order Details table. Disk space is cheap and performance could increase dramatically.

Duane MS Access MVP

 

[Xeseus]

Yah, I think I will include it with the order detail, that seems to make the best sense. Thanks a lot for all of your input!