How to get this table updated with a result from another query

[Jennifer888]

Hi, what I need to do is:

query "a": select tbl1.name, tbl1.acctno from tbl1
group by tbl1.name, tbl1.acctno
having (count(*)>1);
this query identifies all the names and acctno that have duplicate
entries in tbl1

Next,

I want to update tbl2 set tbl2.indicator="Dup" if tbl2.name= the name
selected from query "a" and tbl2.acctno= the acctno selected from
query "a".

Here is an example:

in tbl1
Name AcctNo SubNo
John Doe 12345 abcd
John Doe 12345 defg
John Doe 12345 ghik

This John Doe has duplicate SubNo. The query "a" would select this
record (John Doe, 12345).

in tbl2
Name AcctNo SubNo Date
Indicator
John Doe 12345 abcd 1/1/07 Dup
John Doe 12345 abcd 2/4/07
Dup
John Doe 12345 abcd 6/5/06
Dup
John Doe 12345 defg 2/8/06
Dup
John Doe 12345 defg 4/3/06
Dup
John Doe 12345 ghik 6/5/06
Dup
John Doe 12345 ghik 12/8/03 Dup
John Doe 12345 ghik 4/25/06 Dup
John Doe 67890 tdzk 12/18/06
John Doe 67890 tdzk 4/25/06


the update query will set [indicator]="Dup" for those name and acctno
(John Doe, 12345)= the ones selected by query "a", not others (John
Doe, 67890).

I know if I create a table from query "a", I can update tbl2 with a
update query easily. but i don't want to create another table if it's
not necessary. Can I by pass the make-table query in this case? Any
help is much appreciated.

 

[Haggr via AccessMonster.com]

do you want those duplicate records?

Hi, what I need to do is:

query "a": select tbl1.name, tbl1.acctno from tbl1
group by tbl1.name, tbl1.acctno
having (count(*)>1);
this query identifies all the names and acctno that have duplicate
entries in tbl1

Next,

I want to update tbl2 set tbl2.indicator="Dup" if tbl2.name= the name
selected from query "a" and tbl2.acctno= the acctno selected from
query "a".

Here is an example:

in tbl1
Name AcctNo SubNo
John Doe 12345 abcd
John Doe 12345 defg
John Doe 12345 ghik

This John Doe has duplicate SubNo. The query "a" would select this
record (John Doe, 12345).

in tbl2
Name AcctNo SubNo Date
Indicator
John Doe 12345 abcd 1/1/07 Dup
John Doe 12345 abcd 2/4/07
Dup
John Doe 12345 abcd 6/5/06
Dup
John Doe 12345 defg 2/8/06
Dup
John Doe 12345 defg 4/3/06
Dup
John Doe 12345 ghik 6/5/06
Dup
John Doe 12345 ghik 12/8/03 Dup
John Doe 12345 ghik 4/25/06 Dup
John Doe 67890 tdzk 12/18/06
John Doe 67890 tdzk 4/25/06

the update query will set [indicator]="Dup" for those name and acctno
(John Doe, 12345)= the ones selected by query "a", not others (John
Doe, 67890).

I know if I create a table from query "a", I can update tbl2 with a
update query easily. but i don't want to create another table if it's
not necessary. Can I by pass the make-table query in this case? Any
help is much appreciated.

 

[John Spencer]

PERHAPS the following will work for you

UPDATE Tbl2
SET Tbl2.Indicator = "Dup"
WHERE Tbl2.[Name] & Tbl2.AcctNo
IN (SELECT Tbl1.[Name] & Tbl1.AcctNo
FROM Tbl1
GROUP BY Tbl1.Name & Tbl1.AcctNo
HAVING Count(*) > 1)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..