Duplicate Field Script

[Giloosh]

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

[Bob Butler]

gi*******@hotmail.com (Giloosh) wrote in message news:<ca**************************@posting.google. com>...

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.

try this

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

[Giloosh]

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
bu*******@earthlink.net (Bob Butler) wrote in message news:<fa*************************@posting.google.c om>...

gi*******@hotmail.com (Giloosh) wrote in message news:<ca**************************@posting.google. com>...

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.

try this

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

[Bob Butler]

gi*******@hotmail.com (Giloosh) wrote in message news:<ca**************************@posting.google. com>...

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?
no, that'd be
select [id],[date] from thetable order by [id],[date]
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)

the list you get from the select I posted should already be only those
that are duplicates

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

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