Add record to a table

[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

 

[NeonSky via AccessMonster.com]

To not update the information previously updated is there any
differnentiating criteria between the records to be updated, and those that
have already been updated? For instance the updated rows that you do not want
to update again have a field that has data in it, and the ones you do want to
update do not have data in that same field? If so you can use "IS NULL" or
"IS NOT NULL" criteria for any given field to differntiate between the
records you want to update and those you have already updated.

For example

Field 1 Field 2 Field3
Joe 12Lane 54556
Joe 12Lane


Then you could run your update and add the criteria for field three "IS NULL"
so as to not update the other record that already has data in it.

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

 

[John Spencer]

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

 

[vaa571]

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

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