Query

[Chris]

I have a table with item number in one field and upc in
another field.
I want to update these values to another table.
Table 1
Item Number UPC
1212239 021563587852
1212239 021568525685
1212239 021599653525

Table 2
Item number UPC UPC1 UPC2
1212239 021563587852 021568525685 No data

I want to update the UPC fields in Table 2 with the
values in table one. How do I make a query that will look
at the existing values in table 2 (UPC fields) and if it
already has a value in it go to the next UPC field and so
on until it finds an empty UPC field and update it with
the value from Table 1

 

[John Vinson]

I have a table with item number in one field and upc in
another field.
I want to update these values to another table.
Table 1
Item Number UPC
1212239 021563587852
1212239 021568525685
1212239 021599653525

Table 2
Item number UPC UPC1 UPC2
1212239 021563587852 021568525685 No data

I want to update the UPC fields in Table 2 with the
values in table one. How do I make a query that will look
at the existing values in table 2 (UPC fields) and if it
already has a value in it go to the next UPC field and so
on until it finds an empty UPC field and update it with
the value from Table 1

Well, this will be very difficult indeed with a query, because Table2
is badly denormalized. You'll probably need some VBA code to do so.

What's the purpose of Table2? What is it being used for, that you
couldn't use a Crosstab Query for instead?

 

[John Spencer (MVP)]

Bad design. HOWEVER, you might try the following UNTESTED SQL. TEST THIS ON A
COPY of your data.

UPDATE Table2 INNER JOIN Table1
ON Table2.[Item Number] = Table1.[Item Number]
SET Table2.UPC = NZ(Table2.UPC,Table1.UPC),
Table2.UPC1 = IIF(Table2.UPC IS Null,NZ(Table2.UPC1,Table1.UPC)),
Table2.UPC2 = IIF(Table2.UPC IS Null AND Table2.UPC1 Is Null,Null,Table1.UPC)

Of course,
this works for ONLY 3 UPC fields,
and it does REPLACE UPC2 even if it has a value,
and ...

In other words, respond back to John Vinson's questions. In my opinion there
must be a better method - probably involving a restructuting of the tables.