Restricting Child Table to most recent change.

[mmansfield]

I have an pricing form which must update two tables - a MASTER table and a HISTORY table. It would be easy to normalize the tables, do a form / subform and be done with it.

Problem is, that I want to show the Master Table data along with most CURRENT price (in the HISTORY table). I'm not sure how I can join the tables and only show the MOST RECENT PRICE CHANGE.

If it is not possible to JOIN the tables in this fashion, I would be game for denormalizing the tables, storing the most current price in the MASTER table and also in the HISTORY table. When the MASTER price was changed, the form would update the MASTER, then would write a record into the
HISTORY table, showing the most recent update. Data for the most recent change would exist in two places (MASTER and HISTORY), but we could easily display MASTER data, complete with current pricing. Could this be reliably done using the AfterUpdate event method. Does anyone out here have any experience writing data this way to a log file??

 

[Lightning]

Suggestion:

Can you sort the History table on some field criteria so that the most recent price record is displayed first rather than last? While this would display all the history records, it would put the most current price data at the top of the list, and therefore always visible.

For instance, is there a Date field that shows when the new price was introduced?

Denormalising the database really is not a good idea if there are any other possible solutions.

HTH
Lightning