Duplicate Field Script

Hello, i need some help if possible...
i have a payments table with over 500 records
i want to run a query that searches through the table spotting out any
duplicate ID#'s and Dates.
So basically it will run a search spotting out duplicate ID#'s. Than
with in those ID#'s spot out duplicate dates.
I could do this by eye and spend hours spotting out the duplicated
dates, but it would be much easier and funner having a script do it
for you.

Heres my example:

tblPay

ID \ DATE
---------------------
1.\ 123 \ 3-4-2004
2.\ 231 \ 3-4-2004
3.\ 456 \ 3-7-2004
4.\ 123 \ 3-4-2004
5.\ 456 \ 3-8-2004
6.\ 456 \ 3-7-2004
7.\ 124 \ 3-3-2004

the query will pull out the duplicate ID's:
which will end up with:
ID \ DATE
---------------------
1.\ 123 \ 3-4-2004
2.\ 123 \ 3-4-2004
3.\ 456 \ 3-7-2004
4.\ 456 \ 3-8-2004
5.\ 456 \ 3-7-2004

Than with in that it will pull out only the ones with duplicate dates:

ID \ DATE
---------------------
1.\ 123 \ 3-4-2004
2.\ 123 \ 3-4-2004
3.\ 456 \ 3-7-2004
4.\ 456 \ 3-7-2004

Any ideas on a script that can do this?

heres some theory:

Since i alreadt have one table [tblID] with a record of every unique
ID (non-duplicate) i can use that as a reference:
logic:
For x = 0 to EOF 'In tblID
vID= "Select [ID] from [tblID]"
IDcount= "Select Count([ID]) as CountID from [tblPAY] WHERE [ID]= vID"
IF IDcount > 1 Then 'Its a duplicate
arrayID(x) = vID 'Record ID in an Array
End IF
Next X
'Now i run a loop Through the array of ID's and select the dates
matching
For y = array(1) to array(end)
vDate = select date from tblPay where ID = array(1-end)
if vDate is a duplicate Then
'Record the vID and vDate as a record
Next y
' Finally when done i should have 1 table with only duplicated records

re: Duplicate Field Script

giloosh99@hotmail.com (Giloosh) wrote in message news:<ca6b77b6.0404110908.3dbc75c0@posting.google. com>...[color=blue]
> Hello, i need some help if possible...
> i have a payments table with over 500 records
> i want to run a query that searches through the table spotting out any
> duplicate ID#'s and Dates.
> So basically it will run a search spotting out duplicate ID#'s. Than
> with in those ID#'s spot out duplicate dates.[/color]

try this

select [id],[date] from thetable
group by [id],[date]
having count(*)>1

re: Duplicate Field Script

wow that was a big help, now the dates are in order with the ID's. Is
that what u were aiming for it to do? IF so its a big start. Now
instead of looking through it manually i need to code up a script that
will spot out the duplicate records (id = id and date = date)
Thanks
Gil
butlerbob@earthlink.net (Bob Butler) wrote in message news:<fa10fb0.0404111812.36574841@posting.google.c om>...[color=blue]
> giloosh99@hotmail.com (Giloosh) wrote in message news:<ca6b77b6.0404110908.3dbc75c0@posting.google. com>...[color=green]
> > Hello, i need some help if possible...
> > i have a payments table with over 500 records
> > i want to run a query that searches through the table spotting out any
> > duplicate ID#'s and Dates.
> > So basically it will run a search spotting out duplicate ID#'s. Than
> > with in those ID#'s spot out duplicate dates.[/color]
>
> try this
>
> select [id],[date] from thetable
> group by [id],[date]
> having count(*)>1[/color]

re: Duplicate Field Script

giloosh99@hotmail.com (Giloosh) wrote in message news:<ca6b77b6.0404120833.129a3249@posting.google. com>...[color=blue]
> wow that was a big help, now the dates are in order with the ID's. Is
> that what u were aiming for it to do?[/color]

no, that'd be
select [id],[date] from thetable order by [id],[date]
[color=blue]
> IF so its a big start. Now
> instead of looking through it manually i need to code up a script that
> will spot out the duplicate records (id = id and date = date)[/color]

the list you get from the select I posted should already be only those
that are duplicates
[color=blue][color=green]
> > select [id],[date] from thetable
> > group by [id],[date]
> > having count(*)>1[/color][/color]

if you want to guarntee that the list is sorted add
order by [id], [date]

I think Access returns them sorted anyway; sql server doesn't unless
you specify that