Resolving Duplicates and Foreign Keys

[postmanphat]

Hi,

I know I may be asking a lot here but here goes...

I've got a db that contains a clients table tblClients, which just stores basic name and address info of people who use a facility.

When they come to the facility, their attendance is recorded by their ClientID being recorded in tblDropIn.

No problem so far.

However, a fair little bit of double entry has crept in where the users haven't been properly searching for users already on there so there is a couple of hundred clients in tblClients that are in there twice.

What I want to create is some functionality to merge duplicate records, and to resolve the FKs in the tblDropIn table.

I've no problem picking out all my duplicate (and possible duplicate entries), its just the merging and FK problem I'm struggling with.

I've had a good look online but haven't really found anyhting to help. I'm using Access 2000.

Any help would be most gratefully received.

Ta

Dave

 

[PWise]

try this code

function CombineClients(oldClientid, newClientid)
dim mydb as database
set mydb=currentdb
mydb.execute "Update tblDropIn set ClientID=" & newClientid & " where Clientid=" & oldClientid

mydb.execute "Delete * from tblClients" & " where Clientid=" & oldClientid




end function
[code]

 

[postmanphat]

I'm being dumb - when I try to run that it just brings up a blank macro box?

Where/how do I need to implement this code?

thanks in advance

Dave

 

[Craig0201]

Dave,

Merging is a complex problem. Do I detect from the terminology DropIn and Client that you're working on a system for mental health in the UK? If so, you probably want to look at the Cosmos model by Martin Fowler et al.

Further, you should have a look at Analysis Patterns by the same author.

My advice......be VERY careful and ensure that whatever is done can be undone. What if you merge then find they shouldn't have been. You need to get back to the unmerged state. Perhaps you could create an alias table and amend your queries.

C

 

[Craig0201]

PS I wouldn't touch pwise's code unless you either have a trivial application or are 100% sure.

C

 

[postmanphat]

Nope, nothing to do with Mental Health. It is a pretty trivial system, and besides, I've got a copy of it to work on that I can break 'til my heart's content!