updating one table from another, automatically

[montyb]

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]

 

[Guest_imported]

Goo-day,

I don't recommend automating field additions,
I think you should concider restructuring your DB
something about what your asking dosn't sound right...
let us know how it goes...

Mav.....

 

[DaveSH]

Could you have a Cars table and a MarkNUnit Table with a 1 to many relationship?

Possibly a simple subform based on the MarkNUnit table could be placed on your Cars form. This would allow entry and viewing of all numbers that were ever assigned to the car.

In this case you will be adding records over time rather than fields.
[sig]<p>Dave<br><a href=mailto:gallagherd@earthlink.net>gallagherd@earthlink.net</a><br>[/sig]