Matching records from two different Access tables

By naomi.boxall ·
Hi all,

Bearing in mind I'm a relative newbie (esp. with VBA) could you possibly suggest a simple (or at least describe it in a simple manner) solution to this problem.



Now, there are likely to be things like mis-spellings etc, so what I need to do is show a form that has the information from table 1 in it, and asks "is this case the same as" and displays the information from table 2 in it. Then, when I say "yes, these two are the same" (i.e. some form of mapping etc.) a link is made that knows the information refers to one person.

Er. How???

Any ideas? ANY?

Many, MANY thanks in advance for any help or direction provided.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

You need to setup a relationship

by LarryD4 In reply to Matching records from two ...

First off what are you using in each table as an index? You need to create a relationship between the tables using what I would assume would be a case number, which should be your unique case number. You would then have to setup a relationship between the tables. So that you can create a form that will display all data associated with the case across both tables.

Collapse -

Is this something you ended up with ?

by Tony Hopkinson In reply to Matching records from two ...


Okay is this a one off of job, or are you getting this drivel once a day or some such?

How are you getting it, from an import?

Is every time you get it new data, or could you get the same cases again?

If you match John Smith 21/6/78 with Jonh Smith 21/6/78
Would you expect either of these triplets to appear again. Can you automate, ie assume that any time they do appear again, you can use the same match?

Indeed can you afford to assume John Smith 21/6/78 is John Smith 21/6/78?

I certainly would be leery of doing so.

How on earth is the poor chump doing this task, going to actually say yes that's the same bloke?

Click on the match, yes but based on what?
Manually checking some other system?

Can you have a labcase without an epicase or vice versa, or both.
Is there any link other than a person between LabCase and EpiCase?

When you are coming up with similar persons how similar do the have to be.

Exactly equal is one,
Same but different cases John and john

Sounds like
John and Jon

Soundex John and Jhn

Two out of three exact or case insensitive?

I'd want to seriously hurt the person who dropped this one on me.

Probably murder the person responsible for creating the mess in the first place.

If you can come up with a few answers I can point you in a decent direction, aside from the local gun shop.

For instance


Then link Labcases and epi cases to that.

Then given certain assumptions, once you start building up Persons you could automatch entries from LabCases and EpiCases, and you'd be matching each with Persons. Good Table vs Bad Table as opposed to Bad Table vs Bad Table which is effectively a non starter as far as I can see.

Collapse -

Tables vs. homicide

by naomi.boxall In reply to Is this something you end ...

Right, I see what you mean.

It's sort of inherited, and I'm sort of making it up as I go along. The problem is that we will have cases that have come through the lab, and cases that we've heard about through the grapevine (and still, probably mroe cases that we never ever hear about).

So. My lab table is an import. It only gets new cases appended to it. Hurrah.
They have a unique LabID.

My epi table is being created as data is entered. It, too, only gets new cases appended to it. If the same person gets the same disease later on, then they will be considered a 'new case'. They have a unique CaseID

You say that they are the same person based on the addresses (postcodes) being the same, the DOB being the same, the names being the same, and the lab case having come in around the same time period as the epi case.

So - you visualise the data for each.

LAB: Matthew Jenkins, 18/02/06, PR1 U specdate 21/12/08

Matches to

CASE: Mathew Jenkin, 18/02/06, PR1 T, interview date 27/12/08

If that makes sense. It's crap, but it's the only way to do it without people having ID numbers. :)

The spelling mistakes really will depend on the handwriting (and reading abilities) of those filling in submission forms or questionnaires, and those interpreting them for data entry. the people are NEVER (in my experience, from looking at the data in two tables) going to be 1=1. They'll be 'sounds like' for the most part. But spellings like DeSouza and DSouza are the same person...

As for the person responsible.......It's TEAMS of people who have refused for the last 25yrs to work together. So yeah - we have to patch it together apres-the fact. Imperfect, but hey, it's the real world.

I think your third table idea is a great one. And thanks loads for asking more questions to better identify the angle for your ensuing "where to from here" I look forward to reading it very much! :)


Collapse -

Well if you like persons

by Tony Hopkinson In reply to Tables vs. homicide

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 . 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.

Collapse -

No waiting around

by naomi.boxall In reply to Well if you like persons

I'm not going to wait until I've put this plan into action before I say thanks for all this, it's going to be ever so helpful. I've printed it, am going through it bit by bit and will figure it out I'm sure - with different problems now going on to produce different questions in the main level of the forum! :)

Thanks Tony, really!

Collapse -

So you can give me a thumbs up now then

by Tony Hopkinson In reply to No waiting around

Being stuck on Nelson's column is making me as nervous as when I needed one answer for my hundred.

He says doing a Dicky Bird jig behind his chair.

Related Discussions

Related Forums