problem in update where duplicates in both files exist

I have a table of raw data containing some records with identical data. I
have a second table with those records after a transaction number has been
assigned. I want to update the first table with the transaction numbers.
However, I keep getting only one transaction number fort each duplicate.
The only difference in the records containing identical data is the
transaction number.
How can I get each of the "duplicate" records to update with it's own
transaction number?

Hi,


The following is a kind of a "hack" and works only with Jet.


If Table25, initially is like

Table25
f1 f2

-null- a

-null- a

-null- b




to be updated, and Table25A is like

Table25A
f1 f2

a1 a
a2 a
b1 b




then, to update Table25.f1 with unique numbers from Table25A, ASSUMING
THERE IS an index not allowing duplicated values, on (f1, f2), in table25
(see included bitmap if that does not ring a bell to you),


run the following query:

UPDATE Table25A AS b INNER JOIN Table25 AS a ON b.f2 = a.f2 SET a.f1 = b.f1
WHERE a.f1 Is Null and b.f2 NOT IN(SELECT c.f2 FROM table25 AS c WHERE
c.f1=b.f1 AND c.f1 IS NOT NULL) ;



It produces a warning, ignore it. Once done, the updated Table25 now looks
like:

Table25
f1 f2

a1 a
a2 a
b1 b




If you forget to make the unique index on (f1, f2), in table25, you would
rather got:, after update:

Table25
f1 f2

a1 a
a1 a
b1 b





It is not portable to MS SQL Server, since, among other things, the All
Mighty does not support multiple NULLs in a "unique values" (no dup)
index, and would roll back the whole update if an error occur (Jet keeps
what is "good" and just don't modify what would generate the error).



Hoping it may help,
Vanderghast, Access MVP


"Gene" wrote in message
...
I have a table of raw data containing some records with identical data. I
have a second table with those records after a transaction number has been
assigned. I want to update the first table with the transaction numbers.
However, I keep getting only one transaction number fort each duplicate.
The only difference in the records containing identical data is the
transaction number.
How can I get each of the "duplicate" records to update with it's own
transaction number?

Thanks, Vanderghast. I am essentially a neophyte. I don't know Jet. Should
that concern me?
If I've deciphered it correctly, I'm going to try your suggestion today.
I'll let you know if it works.

Gene
Neophyte User

"Gene" wrote:

I have a table of raw data containing some records with identical data. I
have a second table with those records after a transaction number has been
assigned. I want to update the first table with the transaction numbers.
However, I keep getting only one transaction number fort each duplicate.
The only difference in the records containing identical data is the
transaction number.
How can I get each of the "duplicate" records to update with it's own
transaction number?

Hi,


Jet is the "database engine" that is behind Access. Access itself is a
big application around Jet. Access can also be used with MS SQL Server as
database engine, either directly (project adp) either in a mdb file, but
through linked tables. If you use a dot-mdb, you use Jet, even if you were
not aware of the fact :-)


Vanderghast, Access MVP




"Gene" wrote in message
...
Thanks, Vanderghast. I am essentially a neophyte. I don't know Jet.
Should
that concern me?
If I've deciphered it correctly, I'm going to try your suggestion today.
I'll let you know if it works.

Gene
Neophyte User

"Gene" wrote:

I have a table of raw data containing some records with identical data.
I
have a second table with those records after a transaction number has
been
assigned. I want to update the first table with the transaction numbers.
However, I keep getting only one transaction number fort each duplicate.
The only difference in the records containing identical data is the
transaction number.
How can I get each of the "duplicate" records to update with it's own
transaction number?