and the epitable is collected in house.
Job One is to set up persons, And lookup / add new person in the data collection.
Put some validation in there, or rationalise the case to some standard. Post Code look up would be a good thing to have as well, you'd have everything address wise except teh house number. You might want to thing about splitting thisngs up more to cope with name and address chnages if you want to get real serious.
Either way, you've got new epicases, or new labcases, and initially an empty persons table.
First thing to do is to get all the ones you can match programatically out of the way
Having wangled PersonID into the cases table. Any unmatched cases have a person ID of null
So Matching only operates on them
Update EpiCases set PersonID = (Select PersonID From Persons Where Person.FirstName = EpiCases.FirstName and Persons.LastName = Epicases.LastName and ....) and PersonID is NULL
If you want to make it cases insensitive (can't remeber whether access is by default)
then it's
Where UpperCase(Person.FirstName) = UperCase(EpiCases.FirstName)....
There's a SQl function called Soundex that comes up with anumber you can use for comparison, based on consonants and their order Smth = Smoth or Smath, soundex wise, you might find useful.
Once you've done all you dare your matching form will be
a List of existing persons, a list of unmatched cases.
User selects one of each, hit's the match button, that sets PersonID in cases, query/filter (only PersonID is null), kicks it off the form.
Last you'll be left with new cases for new persons
****
Given PersonID is an autonumber in Persons
Insert Into Persons(FirstName, LastName, DOB...)
Select Distinct FirstName,LastName,DOB... From Cases where PersonID is Null)
will put them in then, you just run the exact match query at the top again.
There are lot's of other ways you could do this, hopefully this gives you a few clues.
But the real trick with this sort of thing, is to break the task up into easily understandable bits, and approach a solution. It will never be perfect.
Day one the 'guy' has to just put them all in, Day 2 list went from a 100 names to match to 50. Do an improvemnt, now there's thirty. They have less of the mind numbimgly boring task to do, you look like you are being more and more helpful, everybody wins...
****
Just thought, it may be possible you have the same person in new cases with two different spillengs :p. So you'll want to use your matching criteria to make them all the same, before you put them in persons.
I think Access it might be Select DistinctRow as well, another strange non standard funny from MS in there.
