Join Or IFF Statement?

[allie357]

I have data that pulls information from multiple tables, but the db is
not fully normalized. i cannot change the structure of the db as it is
not mine.

I need to write an iff statement or a join that will match
transactions by invoice number if the Buyer is null and assign the
Buyer name that is associated to the same invoice number where the
names are not null. I also need to assign the PO Number to the
transactions the same way
Here is an example. The first line is missing PO Num and Buyer name
but it should have the same PO Num and Buyer as the second line.

Amount Inv Number Date PO Num Buyer Name
$33.00 SIMCR 575477 02-
Oct-08
$160.00 SIMCR 575477 02-Oct-08 796332 Ford, Rose Marie

 

[MGFoster]

I have data that pulls information from multiple tables, but the db is
not fully normalized. i cannot change the structure of the db as it is
not mine.

I need to write an iff statement or a join that will match
transactions by invoice number if the Buyer is null and assign the
Buyer name that is associated to the same invoice number where the
names are not null. I also need to assign the PO Number to the
transactions the same way
Here is an example. The first line is missing PO Num and Buyer name
but it should have the same PO Num and Buyer as the second line.

Amount Inv Number Date PO Num Buyer Name
$33.00 SIMCR 575477 02-
Oct-08
$160.00 SIMCR 575477 02-Oct-08 796332 Ford, Rose Marie

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need an UPDATE, possibly like this:

UPDATE table_name As T1 INNER JOIN table_name As T2 ON T1.[Inv Number] =
T2.[Inv Number]
SET T1.[PO Num] = T2.[PO Num], T1.[Buyer Name] = T2.[Buyer Name]
WHERE T1.[PO Num] IS NULL
AND T1.[Buyer Name] IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSbCCR4echKqOuFEgEQJAJQCfRE33z42C3GcLaceSBAGMkxMNGjMAn2H1
ANWito8BjLOS1bHVqfJkNHAB
=sG7p
-----END PGP SIGNATURE-----

 

[GSnyder]

In this situation, I would probably do a self-join, where I join the table to
itself. You bring the table into the query twice (it will show up as, for
example, Data_Table and Data_Table1). Then join on the Invoice number and
choose only those records on the left table (Data_Table) where the invoice is
not null and only those records on the right table (Data_Table1) where the
invoice is null. Then update the right table with the information from the
left.

The SQL looks like this:

UPDATE Data_Table INNER JOIN Data_Table AS Data_Table_1 ON Data_Table.[Inv
Number] = Data_Table_1.[Inv Number] SET Data_Table_1.[PO Num] =
[Data_Table]![PO Num], Data_Table_1.[Buyer Name] = [Data_Table]![Buyer Name]
WHERE (((Data_Table.[PO Num]) Is Not Null) AND ((Data_Table_1.[PO Num]) Is
Null));

It seemed to work for me.