need query

  • Thread starter Thread starter Rod
  • Start date Start date
R

Rod

Hi all

I have the data as shown below and I need query to find duplicates

Field1 Field2
row1 1 a
row2 2 a
row3 1 b
row4 2 b
row5 2 a

I need a query to find out the duplicate row5
Help me..
thanks in advance
 
SELECT Field1, Field2, Count(Field1) as TheCount
FROM YourTable
GROUP BY Field1, Field2
HAVING Count(Field1) >1 ;
 
You didn't mention the Autonumber! ;-)

Also providing us with the real field and table names would help us to help
you.

SELECT Field1,
Field2,
Count(Field1) as TheCount,
Min(AutonumberField) as TheFirst,
Max(AutonumberField) as TheLast
FROM YourTable
GROUP BY Field1, Field2
HAVING Count(Field1) >1 ;

However there is a problem with the above. If the Count of Field1 is greater
than 2, then only the smallest and largest autonumbers will show up. You may
need use a sub-query if this is a problem.
 
thank you for patiency.

id Field1 Field2
1 1 a
2 2 a
3 1 b
4 2 b
5 2 a

As you said it may need sub-query. table name is table1

I need result as shown below
id Field1 Field2
2 2 a
5 2 a

once again thanks for your patience. help me
 
thank you for patiency.
Click to expand...

My patiency is waning :-).

You should use LINQ in C# to get he results you seek :-). See:

http://groups.google.com/group/micr...ges.csharp/browse_frm/thread/f57a989b5f2ad650

"Wazza" said:

"I'll second the linq idea by Arnold.

I would add that If I had my way I would make "renamed access
databases as a save file format" a criminal offence [sic].

Seriously, too many startups do this and it causes nothing but strife.
Especialy if your [sic] selling the aplication [sic] to mom and pop /
soho clients. You will quickly find many things go wrong here that you
did not test for and many different machine configurations out there
in the wild will cause you greif [sic]. You do not need the support
overheads and crap that typicaly [sic] comes with this kind of
shenadiganry [sic].

An aditional [sic] advantage of LINQ is tha [sic] ability to store
your data to XML with brutal simplicity and rip it out again just as
easy [sic]. You may find you can completly [sic] decouple yourself
from access, which is always good.

Also your development time with LINQ code is much[,] much quicker if
your [sic] a good programmer. If you are constantly debuging [sic]
your code however LINQ may get in your way.

Just my $0.00 and wild thoughts based on a small glimse [sic] of your
situation."

James A. Fortune
(e-mail address removed)

Main Entry: Janus–faced
Pronunciation: \-Ö½fÄst\
Function: adjective
Date: 1682
: having two contrasting aspects; especially : duplicitous, two-faced
-- http://www.merriam-webster.com/dictionary/janus faced
 
Try this:

Select A.ID, A.FIELD1, A.FIELD2
FROM TABLE1 As A
WHERE A.FIELD1 IN (SELECT TABLE1.FIELD1
FROM TABLE1
GROUP BY TABLE1.FIELD1, TABLE1.FIELD2
HAVING (((Count(TABLE1.FIELD2))>1)))
AND A.FIELD2 IN (SELECT TABLE1.FIELD2
FROM TABLE1
GROUP BY TABLE1.FIELD1, TABLE1.FIELD2
HAVING (((Count(TABLE1.FIELD2))>1)));
 
Try this:

SELECT DISTINCT T1.id, T1.Field1, T1.Field2
FROM table1 AS T1, table1 AS T2
WHERE T1.Field1 = T2.Field1
AND T1.Field2 = T2.Field1
AND T1.id <> T2.id;

Ken Sheridan
Stafford, England


thank you for patiency.
Click to expand...
id Field1 Field2
1 1 a
2 2 a
3 1 b
4 2 b
5 2 a
Click to expand...
As you said it may need sub-query. table name is table1
Click to expand...
I need result as shown below
id Field1 Field2
2 2 a
5 2 a
Click to expand...
once again thanks for your patience. help me
Click to expand...
[quoted text clipped - 37 lines]
Help me..
thanks in advance
Click to expand...
Click to expand...
Click to expand...


Probably T1.Field2 = T2.Field2. I can relate. Most of my mistakes
are cut-and-paste errors :-).

James A. Fortune
(e-mail address removed)

My most recent music CD purchase:

Description:
http://www.dreyfusrecords.com/album_ns.php?d=181&a=85&lang=0

Amazon:
http://www.amazon.com/What-Differen...=sr_1_2?ie=UTF8&s=music&qid=1266272527&sr=8-2

[French women singing Disco lyrics in English to bossa nova acoustic
guitar]
 
Try this:

Select A.ID, A.FIELD1, A.FIELD2
FROM TABLE1 As A
WHERE A.FIELD1 IN (SELECT TABLE1.FIELD1
FROM TABLE1
GROUP BY TABLE1.FIELD1, TABLE1.FIELD2
HAVING (((Count(TABLE1.FIELD2))>1)))
AND A.FIELD2 IN (SELECT TABLE1.FIELD2
FROM TABLE1
GROUP BY TABLE1.FIELD1, TABLE1.FIELD2
HAVING (((Count(TABLE1.FIELD2))>1)));
Click to expand...

Note: I took the sample table data and added an additional row with
Field1 = 1 and Field2 = b. The Field1 = 2 and Field2 = b row came up
in the results using your SQL.

James A. Fortune
(e-mail address removed)
 
Hi Rod,

Go to Queries and click on New. Use the Find Duplicates Wizard to find the
duplicate row.

Steve
(e-mail address removed)
 
Thank you so much all

James A. Fortune said:
Note: I took the sample table data and added an additional row with
Field1 = 1 and Field2 = b. The Field1 = 2 and Field2 = b row came up
in the results using your SQL.

James A. Fortune
(e-mail address removed)
.
Click to expand...
 
Back
Top