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
