"find duplicates" sql query

[David]

Access from Office 2000

"Hans" <hansb@sorry.nospam.com> wrote in message
news:eer8XdbMEHA.628@TK2MSFTNGP11.phx.gbl...

> Hi!
>
> I don't know which database you are using but something like this might

work

> (there may be some typos of course)
>
> Select formtype, entrydate, ipaddress from formdate group by formtype,
> entrydate, ipaddress having count(*) >1 order by ipaddress
>
> Regards
> /Hans
>
>

[David]

Sorry - forgot to include original post:-

I want to do a query that displays duplicate records in the database based
on IP address. What syntax should I use in the WHERE********** bit please?

Thanks,

set rsData = con.execute("SELECT formtype, entrydate, ipaddress FROM
formdata WHERE ************ ORDER BY ipaddress")
arData = rsData.getrows

[Aaron Bertrand - MVP]

Please pick *ONE* newsgroup to post messages to. This is the correct one;
asp.general is not.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"David" <david.brown@tesco.net> wrote in message
news:O4Nlc.287$dl3.242@newsfe1-win...

> Sorry - forgot to include original post:-
>
> I want to do a query that displays duplicate records in the database based
> on IP address. What syntax should I use in the WHERE********** bit please?
>
> Thanks,
>
> set rsData = con.execute("SELECT formtype, entrydate, ipaddress FROM
> formdata WHERE ************ ORDER BY ipaddress")
> arData = rsData.getrows
>
>

[Phillip Windell]

Well I'll take a "stab" since no one else has. I think GROUP BY might be
better than ORDER BY but decide that for yourself.

To show all entries:
SELECT formtype, entrydate, ipaddress FROM formdata GROUP BY ipaddress

To show a specific IP#
SELECT formtype, entrydate, ipaddress FROM formdata WHERE ipaddress = "<your
input>" GROUP BY ipaddress"


--

Phillip Windell [MCP, MVP, CCNA]
[url]www.wandtv.com[/url]


"David" <david.brown@tesco.net> wrote in message
news:O4Nlc.287$dl3.242@newsfe1-win...

> Sorry - forgot to include original post:-
>
> I want to do a query that displays duplicate records in the database based
> on IP address. What syntax should I use in the WHERE********** bit please?
>
> Thanks,
>
> set rsData = con.execute("SELECT formtype, entrydate, ipaddress FROM
> formdata WHERE ************ ORDER BY ipaddress")
> arData = rsData.getrows
>
>

[Gary Jones]

SELECT formtype, entrydate, fd.ipaddress FROM formdata fd
INNER JOIN
(SELECT ipaddress, [count] = count(*)-1 FROM formdata GROUP BY by ipaddress)
dup
AND dup.ipaddress = fd.ipaddress AND dup.[count] > 0


"David" <david.brown@tesco.net> wrote in message
news:O4Nlc.287$dl3.242@newsfe1-win...

> Sorry - forgot to include original post:-
>
> I want to do a query that displays duplicate records in the database based
> on IP address. What syntax should I use in the WHERE********** bit please?
>
> Thanks,
>
> set rsData = con.execute("SELECT formtype, entrydate, ipaddress FROM
> formdata WHERE ************ ORDER BY ipaddress")
> arData = rsData.getrows
>
>