Add record to a table

  • Thread starter Thread starter vaa571
  • Start date Start date
V

vaa571

I really need help with this please...
I am intentionally leaving the Lot# field on my table empty because it will
slow down the assembly line if they have to enter a five digit lot number
when they are scanning in the “Pack Serial Number†& the “Board Serial
Number†into that table. I am rather enter the lot number at the end of that
lot, so it will save as a lot of time. I am talking about over a thousand
records per lot. I created an update query to enter the lot#, it works if I
only have one lot to update. Let’s say that I
have 2000 records in a table and the first thousand is lot# 12345 and the
remaining thousand should be lot# 67891. I ran the query it ask for input
"starting pack" and "end pack serial number" but when I am trying to run it
to
update the remaining thousand packs it update all 2000 records. How can I
make this works and not loose the information already updated previously?
This table will continue to grow and I really need to keep track of the lot#
for each lot built and shipped.
Here is a copy of my query:

UPDATE Main AS Data SET [Lot Number] = [Enter Lot Number]
WHERE PackSerialNumber Between [Enter Starting Pack Serial Number] And
[Enter Ending Pack Serial Number];

Thank you so much for any help
 
What type of field is Pack Serial Number? Is it a text field or a number
field? If Pack Serial Number is a text field you could be running into a
problem with records getting selected that are alphabetically in range but
numerically out of range. For instance, "12" is between "1" and "2", while 12
(a number) is not between 1 and 2. Or if text "A1257" to "x1257" would
include all records from that started with the letters B to W plus a subset of
the A records and the X records.


UPDATE Main
SET [Lot Number] = [Enter Lot Number]
WHERE PackSerialNumber Between [Enter Starting Pack Serial Number] And
[Enter Ending Pack Serial Number] and [Lot Number] is null




John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I really need help with this please...
I am intentionally leaving the Lot# field on my table empty because it will
slow down the assembly line if they have to enter a five digit lot number
when they are scanning in the “Pack Serial Number†& the “Board Serial
Number†into that table. I am rather enter the lot number at the end of that
lot, so it will save as a lot of time. I am talking about over a thousand
records per lot. I created an update query to enter the lot#, it works if I
only have one lot to update. Let’s say that I
have 2000 records in a table and the first thousand is lot# 12345 and the
remaining thousand should be lot# 67891. I ran the query it ask for input
"starting pack" and "end pack serial number" but when I am trying to run it
to
update the remaining thousand packs it update all 2000 records. How can I
make this works and not loose the information already updated previously?
This table will continue to grow and I really need to keep track of the lot#
for each lot built and shipped.
Here is a copy of my query:

UPDATE Main AS Data SET [Lot Number] = [Enter Lot Number]
WHERE PackSerialNumber Between [Enter Starting Pack Serial Number] And
[Enter Ending Pack Serial Number];

Thank you so much for any help
Click to expand...
Click to expand...
 
Hi John or any one from the community...

I though I have this figure out with your help but I am running into a
different problem now. I forgot to update a lot as soon they finished
scanning the packs and boards SN into the database. Now I have various lots
waiting to be update with the lot# information.
Pack SN AG08EAV0001 thru AG08EAV636 belongs to lot 03331
Pack SN AG08FAV0001 thru AV08FAV700 belongs to lot 03629

Here is a copy of the SQL statement:

UPDATE Main SET [Lot Number] = [Enter Lot Number]
WHERE PackSerialNumber Between [Enter Starting Pack Serial Number] And
[Enter Ending Pack Serial Number] And [Lot Number] Is Null;

When I run the query it is updating all records where the lot# is null, even
when I enter the parameters for the first lot only. Any idea how I can
correct this problem? For clarification Lot# field is text.

Thank you again


John Spencer said:
What type of field is Pack Serial Number? Is it a text field or a number
field? If Pack Serial Number is a text field you could be running into a
problem with records getting selected that are alphabetically in range but
numerically out of range. For instance, "12" is between "1" and "2", while 12
(a number) is not between 1 and 2. Or if text "A1257" to "x1257" would
include all records from that started with the letters B to W plus a subset of
the A records and the X records.


UPDATE Main
SET [Lot Number] = [Enter Lot Number]
WHERE PackSerialNumber Between [Enter Starting Pack Serial Number] And
[Enter Ending Pack Serial Number] and [Lot Number] is null




John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I really need help with this please...
I am intentionally leaving the Lot# field on my table empty because it will
slow down the assembly line if they have to enter a five digit lot number
when they are scanning in the “Pack Serial Number†& the “Board Serial
Number†into that table. I am rather enter the lot number at the end of that
lot, so it will save as a lot of time. I am talking about over a thousand
records per lot. I created an update query to enter the lot#, it works if I
only have one lot to update. Let’s say that I
have 2000 records in a table and the first thousand is lot# 12345 and the
remaining thousand should be lot# 67891. I ran the query it ask for input
"starting pack" and "end pack serial number" but when I am trying to run it
to
update the remaining thousand packs it update all 2000 records. How can I
make this works and not loose the information already updated previously?
This table will continue to grow and I really need to keep track of the lot#
for each lot built and shipped.
Here is a copy of my query:

UPDATE Main AS Data SET [Lot Number] = [Enter Lot Number]
WHERE PackSerialNumber Between [Enter Starting Pack Serial Number] And
[Enter Ending Pack Serial Number];

Thank you so much for any help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top