Hi,
I have two tables; one is called SERIAL NUMBERS and the other is called SN HISTORY.
SERIAL NUMBERS has information on parts in an inventory with unique serial numbers which includes the S/N, description, and current location. It is important to know where the part is.
SN HISTORY needs to track the location changes in the serial numbered part. The fields are S/N, date and location.
For example if I have a part with the serial number KG005 and it's in Boston and then I send it to New York then I want the current location in the SERIAL NUMBERS to be New York and in the SN HISTORY table I want two records. The first says it was in Boston on date A and in New York on date B.
I want to have a form for changing the location where you input the new location, date and serial number. The form would have to query the SERIAL NUMBERS table and look for the serial number that the user inputs. Then it would replace the current location in that table with the new location in the form. It would also add all of the things entered in the form as a new record in SN HISTORY. So my question is: is it possible to link one entry in a form to two fields in two different tables?