time dependent data

[potts]

I need help! I am trying to create a table that stores time-dependent information based on a look-up table.

For example, visit information is stored in a table called visits. Each visit has its own unique ID and contains the name of the visiting person, and the fee they were charged which is obtained from a fees lookup table. The problem is that if the fee changes for future visits, I need the visits table to retain all it's original information, i.e. the old fee for the previous visits.

Can any of you help me out??

 

[BLeslie88]

On right track...

Your set-up sounds good... When you register a visit you should write the fee to the tables so that you aren't referencing the value from the ID. Historic tables for reporting should reference from ID's unless you want current data. All info that is to remain written in stone for future reference should be written to tables as face values at those times.

Hope this helps....

 

[potts]

time dependent data continued

Thanks for the reply. Is there any way of writing this info so that it is somewhat automated - I really don't want to have to type the value each and every time if at all possible.

 

[Pat Hartman]

1. Add the fee column to your visits table.
2. In the rowsource query that you use to populate the fees lookup combo, include the fee amount.
3. In the AfterUpdate event of the combo, copy the fee to your visits table.

Me.FeeAmt = Me.YourComboName.Column(2)

The columns of the combo are a zerobased array. So, the first column is .Column(0), the second is .Column(1), the third is .Column(2), etc. I assumed that the fee was the third column. If not, adjust the column number accordingly.

After you add the new column to the combo's rowsource, make sure that you update the column count and the column width properties.