Table

[Ashley]

I have a serial number table contains Serial numbers.
I have a field on a form lookup the serial number in the serial number
table. The selected serial number and other info on a form are recorded in
another table.
After serial number is selected on the form, I would like it to put an "Yes"
to the same serial number in the "used" field in the serial number table.
I desperately need to get this work. I really appreciate your suggestion.
Thanks

 

[Tim Ferguson]

After serial number is selected on the form, I would like it to put an
"Yes" to the same serial number in the "used" field in the serial
number table.

No need to: the information already exists. If you want a list of unused
serial numbers, try


SELECT SerialNumbers.SerialNumber
FROM SerialNumbers LEFT JOIN OtherTable
ON SerialNumbers.SerialNumber = OtherTable.SerialNumber
WHERE OtherTable.SerialNumber IS NULL
ORDER BY SerialNumbers.SerialNumber


and then it will never be wrong! What are you going to do when the SN table
says a number is free, and the OtherTable says it isn't? You might like to
do some reading on R theory and database design.

Hope that helps


Tim F

 

[Ashley]

Tim,
This work! thank you.
If I may ask a related question.
I have another field call "Modem" on the form. It needs the same thing. I
have one modem number is recorded in Modem_one and an another modem number is
recorded in Modem_two. I need it to exclude all numbers that recorded in
Modem_one and Modem_two field from the list.
I tried below SQL but it didn't work. Any suggestion is greatly appreciated.

SELECT tblModem.Modem_num
FROM tblModem LEFT JOIN tblUnitAssy ON (tblModem.ModemID =
tblUnitAssy.Modem_Two) AND (tblModem.ModemID = tblUnitAssy.Modem_One)
WHERE (((tblUnitAssy.Modem_One) Is Null) AND ((tblUnitAssy.Modem_Two) Is
Null))
ORDER BY tblModem.Modem_num;

 

[Tim Ferguson]

I
have one modem number is recorded in Modem_one and an another modem
number is recorded in Modem_two.

Design problem: fields called something_1, something_2 and so on are
nearly always signs of trouble. You really need to put these numbers in
their own table. What about just putting _all_ the modem numbers into one
table, and then adding a FK for which PC they are allocated to?--

ModemNum SerialN
1044 205495
1045 205495
1046 NULL
1047 209921
1048 NULL


you can get available numbers with a simple IS NOT NULL query; you get
numbers for a PC either with a JOIN or a WHERE SerialN = xxxx and so on.
You can have any number of modems on a PC -- someone, somewhere, is
plotting one with three numbers...

Hope that helps


Tim F