To find duplicate records in a query that is already finding duplicating records

[Manoj J via AccessMonster.com]

i have a table "X". and it has columns "A" and "B".
"A" and "B" have duplicate records.

so i have created a query "Y" that reduces the duplicate records in column
"A".

now the results that are shown in "Y" have the column "A" with no duplicates
but has "B" having duplicate records.

how should i reduce the duplcuate records in "B" which exist in the query "Y".


when i tried creating a query out of "Y" and run it, it gives me an error
saying "ODBC--call failed".

can someone explain this and give me a solution.

thank you.

 

[Allen Browne]

Define 'duplicate.' Do you mean:
a) Pairs of records that have the same combination of [A] and ?
or:
b) A value that is found in [A], and also found in in any record?

If (a), use an inner join:
Create a query with 2 copies of the table.
(If the table is Table1, Access will alias the 2nd one as Table1_1.)
In the upper pane of query design drag Table1.A onto Table1_1.A.
Then drag Table1.B onto Table1_1.B.
Set criteria under the ID field (the primary key) of:
< Table1_1.ID

If (b), use a UNION ALL query to put the values from both columns into one.
Then create a Totals query into that one, and count the number of values.
Any record of your origianal table that has more than 1 is then a duplicate.
Post back if you need more info on this.

If you have similar data in 2 columns, there might be a better way to design
the table.

 

[jiwatramani via AccessMonster.com]

i think you've got it wrong. im sorry if i have not explained it well earlier.


duplicate here means, there records in column "A" that are similar.
Eg: WORKORDER# is repetetive.

so i have removed them in one query "Y".

but now in query "Y", the column "B" has duplicate values too.

i have created another query "Z" (using "Y" as my base) to remove duplicate
records of "B".

but it gives me the error "ODBC--call failed".

 

[Allen Browne]

Post the query statement that gives the error.

 

[jiwatramani via AccessMonster.com]

SELECT First([Reducing duplicate WO# for Query3].[Workign Days]) AS [Workign
Days Field], Count([Reducing duplicate WO# for Query3].[Workign Days]) AS
NumberOfDups
FROM [Reducing duplicate WO# for Query3]
GROUP BY [Reducing duplicate WO# for Query3].[Workign Days]
HAVING (((Count([Reducing duplicate WO# for Query3].[Workign Days]))>1));

 

[Allen Browne]

That looks okay.

The problem must be deeper down: in one of the queries that feed this one --
even if they work on their own.

I'm not sure what to suggest.

Perhaps someone else has experienced this error.

 

[Manoj J via AccessMonster.com]

ohh man !
ok..........NO WORRIES.

im posting the the base query and the current query.
have a look at it.....and see if you can help me.
thanks anyway.

base query:
SELECT First(Query3.WO_NUMBER) AS [WO_NUMBER Field], Query3.WK_ID_CODE,
Query3.WO_REQUEST_DATE, Query3.WO_CLOSE_DATE, Query3.[Workign Days]
FROM Query3
GROUP BY Query3.WK_ID_CODE, Query3.WO_REQUEST_DATE, Query3.WO_CLOSE_DATE,
Query3.[Workign Days], Query3.WO_NUMBER
HAVING (((Count(Query3.WO_NUMBER))>1));

current:
SELECT First([Reducing duplicate WO# for Query3].[Workign Days]) AS [Workign
Days Field], Count([Reducing duplicate WO# for Query3].[Workign Days]) AS
NumberOfDups
FROM [Reducing duplicate WO# for Query3]
GROUP BY [Reducing duplicate WO# for Query3].[Workign Days]
HAVING (((Count([Reducing duplicate WO# for Query3].[Workign Days]))>1));

 

[Allen Browne]

As expected, there's still more below that (Query3.)

Nothing these stands out though.