If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table
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 |
#2
|
|||
|
|||
=?Utf-8?B?QXNobGV5?= wrote in
: 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 |
#3
|
|||
|
|||
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; "Ashley" wrote: 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 |
#4
|
|||
|
|||
=?Utf-8?B?QXNobGV5?= wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SIMULTANEOUSLY access to a table?! | Julia | General Discussion | 1 | November 5th, 2004 10:02 PM |
Access & OleDb - generating schema changes, problem with identity/counter fields. | Thomas Tomiczek [MVP] | Database Design | 9 | November 5th, 2004 10:32 AM |
Automatic filling of fields in table two from table one | Jim Kelly | Database Design | 1 | September 27th, 2004 10:16 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |