Access 2003: Extra row in duplicates query

[Guest]

I have a duplicates query ported over from Access 2000 that is showing an
extra record. For testing purposes, I have a table with 2 duplicates and I
am getting them, but I am also getting an extra row populated with only the
two fields I am qualifying on.

Query:
SELECT LoclDMUActions.ActionWord, LoclDMUActions.ActionDocNumber,
LoclDMUActions.ActionBatchId
FROM LoclDMUActions
WHERE (((LoclDMUActions.ActionWord) In (SELECT [ActionWord] FROM
[LoclDMUActions] As Tmp GROUP BY [ActionWord],[ActionDocNumber] HAVING
Count(*)>1 And [ActionDocNumber] = [LoclDMUActions].[ActionDocNumber])))
ORDER BY LoclDMUActions.ActionWord, LoclDMUActions.ActionDocNumber;

Any ideas?

 

[Guest]

Bill, I presume that you are looking to show all rows where two specific
field values are duplicated.
I used two queries shown below. I used Access 2003, but the database was
Access 2000 type.

Here's my test table:
Field1 Field2 Field3
1 2 3
1 2 4
2 2 1
2 2 1
3 3 3

Here's my result:
Field1 Field2 Field3
1 2 3
1 2 4
2 2 1
2 2 1

And the queries I used:

The "Find duplicates for TestDups" query:
SELECT TestDups.Field1, TestDups.Field2, TestDups.Field3
FROM TestDups INNER JOIN [Count duplicates for TestDups 2Fields] ON
(TestDups.Field1 = [Count duplicates for TestDups 2Fields].Field1) AND
(TestDups.Field2 = [Count duplicates for TestDups 2Fields].Field2)
ORDER BY TestDups.Field1, TestDups.Field2;

The "Count duplicates for TestDups 2Fields" query:
SELECT TestDups.Field1, TestDups.Field2, Count(TestDups.Field3) AS DupCount
FROM TestDups
GROUP BY TestDups.Field1, TestDups.Field2
HAVING (((Count(TestDups.Field3))>1));

Hopethishelps,
ccw

 

[Guest]

Thanks, but today the doggone thing is working properly with no changes !!

Part of the issue is that I am running this via Citrix. I did not get the
same behaviour when running my local copy of Access 2003.

Now I wonder is it is server-specific.....

Thanks, but now I think this lies somewhere in Support.

--
Bill Downs

Bill, I presume that you are looking to show all rows where two specific
field values are duplicated.
I used two queries shown below. I used Access 2003, but the database was
Access 2000 type.

Here's my test table:
Field1 Field2 Field3
1 2 3
1 2 4
2 2 1
2 2 1
3 3 3

Here's my result:
Field1 Field2 Field3
1 2 3
1 2 4
2 2 1
2 2 1

And the queries I used:

The "Find duplicates for TestDups" query:
SELECT TestDups.Field1, TestDups.Field2, TestDups.Field3
FROM TestDups INNER JOIN [Count duplicates for TestDups 2Fields] ON
(TestDups.Field1 = [Count duplicates for TestDups 2Fields].Field1) AND
(TestDups.Field2 = [Count duplicates for TestDups 2Fields].Field2)
ORDER BY TestDups.Field1, TestDups.Field2;

The "Count duplicates for TestDups 2Fields" query:
SELECT TestDups.Field1, TestDups.Field2, Count(TestDups.Field3) AS DupCount
FROM TestDups
GROUP BY TestDups.Field1, TestDups.Field2
HAVING (((Count(TestDups.Field3))>1));

Hopethishelps,
ccw

I have a duplicates query ported over from Access 2000 that is showing an
extra record. For testing purposes, I have a table with 2 duplicates and I
am getting them, but I am also getting an extra row populated with only the
two fields I am qualifying on.

Query:
SELECT LoclDMUActions.ActionWord, LoclDMUActions.ActionDocNumber,
LoclDMUActions.ActionBatchId
FROM LoclDMUActions
WHERE (((LoclDMUActions.ActionWord) In (SELECT [ActionWord] FROM
[LoclDMUActions] As Tmp GROUP BY [ActionWord],[ActionDocNumber] HAVING
Count(*)>1 And [ActionDocNumber] = [LoclDMUActions].[ActionDocNumber])))
ORDER BY LoclDMUActions.ActionWord, LoclDMUActions.ActionDocNumber;

Any ideas?