Add data only if it resides in another table

I have a database to track the serial numbers on phones coming in and going
out.
I have created two tables (Serials_IN and Serials_OUT)

Table Name: Serials_IN
DEC_SN -Text
SN_IN_ID - autonumber
TimeStamp

Serials_OUT
DEC_SN - Text
SN_OUT_ID - Autonumber
TimeStamp

I have forms set up for people to scan a serial number in and a separate
form to scan a serial out.
I want to set up the Serials_OUT table so that you cannot scan a serial
number out that is not already in the Serials_IN table. In other words you
can't sell the phone if you don't have it in inventory.

If you were limited to using a spreadsheet, you might set up a pair of
'sheets, one for IN and one for OUT.

But Access is a relational database, not a spreadsheet.

In a well-normalized relational database, you could/probably would use only
a single table. It might look something like (since I don't know your
specific particulars, this is speculative):

tblPhone
PhoneID (?equivalent to your [SN_ID])
DEC_SN (?a description of ??)
Added (a date/time field to record when the phone was added to
inventory)
Sold (a date/time field to record when the phone left inventory)
MIA (a yes/no field for those rare occasions when a phone just goes
missing)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"meileigh" wrote in message
...
I have a database to track the serial numbers on phones coming in and going
out.
I have created two tables (Serials_IN and Serials_OUT)

Table Name: Serials_IN
DEC_SN -Text
SN_IN_ID - autonumber
TimeStamp

Serials_OUT
DEC_SN - Text
SN_OUT_ID - Autonumber
TimeStamp

I have forms set up for people to scan a serial number in and a separate
form to scan a serial out.
I want to set up the Serials_OUT table so that you cannot scan a serial
number out that is not already in the Serials_IN table. In other words you
can't sell the phone if you don't have it in inventory.