Trying to Return a list of duplicates

[homer2002]

Ok heres the thing.

I have 2 tables

tblTempImport
tblArchive
(both have a field called "AccountNumber")

Both table structures are identical (good ol copy and paste)


What I have in these tables are lists of Account Numbers.

A file is imported into tblTempImport
and then transfered into tblArchive

What I need to do is to extact (hopefully into an array)
all the account numbers from tblImport WHERE

The Account Number in tblImport EXISTS in tblArchive &
The Account Number in tblImport DOESNT EXIST in tblArchive.

Does anyone have a DAO solution or anything so I can
get these lists into an array?

Just to clear things up at the start.
Knocking out the duplicates is not a problem, I really do need the lists of account numbers returned to an array.


Cheers Homer

 

[namliam]

How about having them in a recordset?

Dim rst as DAO.recordset
set rst = currentdb.openrecordset("Your double query")

Then using
rst.movefirst
Do until rst.eof
... bla bla more code
rst.movenext
loop

you can do anything...

Regards

The Mailman

 

[homer2002]

Cheers, but can you give me a bit of code in the middle, just so I can list all the duplicates


I get making the connection and moving through the file
but I have no idea on how to manipulate with two tables

somthing like....


rst.movefirst
Do until rst.eof
'If tblImport.AccountNumber = tblQuarter6AccountNumber then
msgbox "This is a duplicate"
end if
rst.movenext
loop

 

[namliam]

Your not telling the whole story or i am not getting it....

You can get all account numbers which are in both tables using SQL, much faster much easier..... You can then use that query to display in table view or a report to show what ever you want....

Maybe you should post an example or type an example of what you want to do (pictures paint more than a 1,000 words)

Regards

The Mailman

 

[homer2002]

I can paint a picture (ironicly) of me sinking

__________________\o/________


Ok, first of all

What I need is a list of duplicated (and a seperate list of non duplicated) account numbers.

This list must be in the form of an array, or somthing that I can use. A simple table or report, unfornualy is no good for me.

Once I have an array of account numbers, I will use this to add/delete or move records depending on a few paramaters.

I don't mind how I get this list into an array (SQL, ADO or DAO).

Somthing like this like would be handy (or somthing that does the same)
_______________________________________-
Dim i as long
Dim ArrayOfAccountNumbers(1 to 9999999)
dim TotalAccountsFound as Long
i = 0
Open tblImport and tblArchive
do while not eof(tblImport)
If tblImport.AccountNumber = tblArchive.AccountNumber then
i=i+1
ArrayOfAccountNumbers(i) = tblImport.AccountNumber
end if
TotalAccountsFound = i -1
loop
________________________________________

This way I have an array that I can use to create dynamic SQL (which I would rather not go into as its long and boring)



Is that any better?

 

[homer2002]

ok, i just worked out what you were going on about


I use the query at the top and get a recordset that I can use.

p.s. maybe I should have said I have NO idea about DAO or ADO.

Anyway I think i'm alright now

i did it the way you suggested and can filter my way through the record set now.

thanks Homer
______________________
Dim rst As DAO.Recordset
Dim i As Integer
i = 0
Set rst = CurrentDb.OpenRecordset("qryDuplicateImports")
rst.MoveFirst
Do Until rst.EOF
i = i + 1
MsgBox rst.Fields(0) & " " & rst.Fields(1)
rst.MoveNext
Loop
______________________