need query

[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

 

[Rod]

oops.... sorry

need query that find row1 and row5

thanks in advance

 

[Jerry Whittle]

SELECT Field1, Field2, Count(Field1) as TheCount
FROM YourTable
GROUP BY Field1, Field2
HAVING Count(Field1) >1 ;

 

[Rod]

thank you,
but row1 and row5 are tobe displayed along with autonumber ID
thanks in advance

 

[Jerry Whittle]

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.

 

[Rod]

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

 

[James A. Fortune]

thank you for patiency.

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

 

[James A. Fortune]

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

corregendum:

the results

James A. Fortune
(e-mail address removed)

patiency:

In logic, the quality of being passive; the condition of being acted
upon by another: the opposite of agency. [Rare.] -- http://www.wordnik.com/words/patiency

 

[Jerry Whittle]

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)));

 

[James A. Fortune]

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.

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

[quoted text clipped - 37 lines]

Help me..
thanks in advance

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]

 

[James A. Fortune]

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)));

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)

 

[Steve]

Hi Rod,

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

Steve
(e-mail address removed)

 

[Rod]

Thank you so much all

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)
.