append value to history table and update current value

[tagteam]

I have searched the forum but can't quite find what i am looking for on this.



I have form1 that has the current commodity
When i need to change the commodity I want the following to happen when they click a button:

1. open the commodity change form
2. that form has a drop down to select a new commodity and a date of commodity change date field
3. When the user submits it will move existing value to history along with the Date of change (separate table)
4. Next it will take the value in the new commodity box and make it the current commodity

My thinking is that when i click the button to open the change form I can save the commodity at that time but I wont have the date yet. Just not sure of they best way to go about this.
Thanks for any help

[June7]

More conventional approach is to just mark record as 'inactive', not move to another table.

What is 'commodity' and what exactly is being changed?

[tagteam]

Sorry the commodity is the last contents in a railcar. So the railcar is carrying one type of oil or chemical or whatever. then another customer uses it and we need to record what the last commodity was and what the current one is so we can't mark it as inactive because it is just one field in a much larger list of fields

[cbende2]

Sounds like you may want to use a multi to multi relation join table.

3 tables:
1)RailCarTable----RailCarID, RailCarName, Date, etc.
2)CommodityTable------CommodityID, TypeOfCommodity
3)RailCarCommodityTable----NewID, RailCarIDFK, CommodityIDFK

OR

Before changing the commodity and date, run an append query first. Then you can simply update the record in your orignal table, and now you have the "new" record in your primary table and "old" record in your append table.

[June7]

I agree with cbende2. I think your data structure is not optimized

And the RailCarCommodityTable needs date field. This table would have 'transactions' that document contents of rail cars over time. This provides history as well as current status without having to juggle records between tables.

[tagteam]

Thanks for the help. I created a new table as suggested to take the many to many join and create two one to many joins and now it is doing what I want.
Thanks

[cbende2]

Awesome! Great to hear back that it's working!