Table Update Query Assistance Needed

[delber...@gmail.com]

Hello and thanks in advance to any assistance.

I have a table called tblImportSN_Temp
This table consist of the following text fields.

* BatchNbr
* MfgCode
* SerialNbr
* Tech
* Logon
* Quadrant

I have an append query that imports a list of scanned serial numbers (from a csv file). This append query also populates the MfgCode, Tech, Logon, and Quadrant fields.

Typically there would be 1 to 6 serial numbers imported in for each scan.
An example of the tblImportSN_Temp could look something like this:

BatchNbr MfgCode SerialNbr Tech Logon Quadrant
X5235 12345678 JS jsmith A1
X5235 23456789 JS jsmith A1
X5235 34567890 JS jsmith A1
Z7654 AA786900 JS jsmith A1
Y9912 00001764 JS jsmith A1
ACS99 MN123098 JS jsmith A1

The next step and what I need assistance with is creating the Batch Number for each group of MfgCode.

Example of the tblImportSN_Temp after Batch Numbers have been assigned would look like this.

BatchNbr MfgCode SerialNbr Tech Logon Quadrant
161107001 X5235 12345678 JS jsmith A1
161107001 X5235 23456789 JS jsmith A1
161107001 X5235 34567890 JS jsmith A1
161107002 Z7654 AA786900 JS jsmith A1
161107003 Y9912 00001764 JS jsmith A1
161107004 ACS99 MN123098 JS jsmith A1

As you can see all the Serial Numbers that have a MfgCode of X5235 have the same batch number, and all the other Serial Numbers have different Batch Numbers because they all have different MfgCode.

I can create the Batch Numbers, I'm just not sure how to go about looping through the MfgCode to find any like codes and grouping them together to insure they get the same Batch Number.

Thanks again for any assistance!

[Ron Weiner]

delber...@gmail.com submitted this idea :

Well one quick easy way would be to run a Update Query after the
umport. Something like this should work:

UPDATE tblImportSN_Temp SET tblImportSN_Temp.BatchNbr =
Format(Now(),"yyyymmddhhnn") & [mfgCode];

As long as the execution of this query did not span more than a minute
you get what you need, with the added feature of knowning when the
records were added.

If the query is likely to take more then a second or two to execute you
could (should) compute the DateTime once and use that value for each
row to be updated. Something like:

strMyDate = Format(Now(),"yyyymmddhhnn")
strSql = "UPDATE tblImportSN_Temp SET tblImportSN_Temp.BatchNbr = " &
strMyDate & " & [mfgCode];"
CurrentDb.Execute strSql, dbFailOnError

[Ulrich Möller]

As you already know how to generate the batch numbers, simple create a
table (tblMfgCodeBatchNbr) with unique MfgCodes and their corresponding
unique batchnumbers.

INSERT INTO tblMfgCodeBatchNbr ( MfgCode, BatchNbr ) SELECT DISTINCT
tblImportSN_Temp.MfgCode, GetUniqueBatchNbr() FROM tblImportSN_Temp;

Then in the second step run an update query something like

UPDATE tblImportSN_Temp AS t1 INNER JOIN tblMfgCodeBatchNbr AS t2 ON
t1.MfgCode = t2.MfgCode SET t1.BatchNbr = t2.BatchNbr;

Ulrich

[delber...@gmail.com]

Thank you both for the replies. It appears I was trying to make this hard than it really is. Thank you so much!!