Greetings.
I am working on a database that gives me information on railcar status. An identifier is a field called MarknUnitNo (it is a combination of the Mark and UnitNo fields). A railcar is given a mark and unit number as an identifier with the owner. The number can change many times during the life of the car.
What I need to do is set up a MarknUnitNo history table. The original MarknUnitNo could possibly be the key. In addition, I need to know all the previous MarknUnits. I know I could use a make table query that extractst the org, current and previous marknuntis, but how do I automate it so that when the MarknUnit changes, it automatically updates the history table? And by that, move the previous mark over one?
eg...
OrgMark&U CurMark&U PrevM&U1 PrevM&U2 etc.
GNRR123 CNRR222
Mark&U changes to ABAB111
GNRR123 ABAB111 CNRR222
Lastly, is there a way to automatically keep adding fields instead of building a table with 15 or so previous marknunit fields?
Thanks in advance,
James [sig][/sig]
I am working on a database that gives me information on railcar status. An identifier is a field called MarknUnitNo (it is a combination of the Mark and UnitNo fields). A railcar is given a mark and unit number as an identifier with the owner. The number can change many times during the life of the car.
What I need to do is set up a MarknUnitNo history table. The original MarknUnitNo could possibly be the key. In addition, I need to know all the previous MarknUnits. I know I could use a make table query that extractst the org, current and previous marknuntis, but how do I automate it so that when the MarknUnit changes, it automatically updates the history table? And by that, move the previous mark over one?
eg...
OrgMark&U CurMark&U PrevM&U1 PrevM&U2 etc.
GNRR123 CNRR222
Mark&U changes to ABAB111
GNRR123 ABAB111 CNRR222
Lastly, is there a way to automatically keep adding fields instead of building a table with 15 or so previous marknunit fields?
Thanks in advance,
James [sig][/sig]