Need Help Troubleshooting this Database!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm inexperienced in Access, but I do know the very basics. I've gone though
numerous lessons online to solve this problem, but to no avail. So here's
the situation:
I have two tables.
1) Past Award Winners
2) Event RSVP List

Both of these tables contain only two fields each: First Name, Last Name.
Listed in each table are the names of various peoples by their first and last
names. My job is to find out how many Past Award Winners are attending the
event--i.e. they would be listed in the Event RSVP List table.

My Problem.
I've tried to set up queries that display the first and last names of people
on both lists. I started this by creating a relationship between the First
Name field of one table and the First Name field of another, and doing the
same with both Last Name Fields. The query then is set to display both the
First Name fields of both tables and the Last Name fields of both tables.
However when I run the query my results are wrong. While I should have 8
names popping up (this being verified manually by comparing the two
spreadsheets by eye), I can only get these three names to pop up. I know
part of this problem has to do with the fact that in certain spots in the
RSVP list, a persons First Name might include an extraneious character (like
a middle initial) after it, so it doesn't match up exactly. All the Last
Names are input in the same format and without errors . . .

I hope this makes sense. If you were tackling this task, is there a more
efficient way of doing this query? I'll need to perform this on a much
larger set of tables, and so I'd like to work out the kinks with these
smaller ones first.

Please let me know if something needs clarification in this request, and I
would very much appreciate any help or advice one could give me.

Thank you.
 
You now know the importance of having a 'key field' that identifies a
'people' then using that through the database to link the tables rather than
names.

Hopefully the "Past Award Winners" list is not too long!!!


Problem: You have NO way to separate (John Smith, the winner) from John
Smith (the loser) ;>
(case both last and first names of the two people are the same).

If you are SURE the last names are the same, you're half way there, you can
find all that match on the last name only, then handle the rest as an
exception.

If you have an address and/or phone number that will help a lot, failing
that:

In general you need to add unique key fields to each table, then build a
table of ID's from the Winners list, with a field for the matching Id from
the RSVP list.

When you have all the matches you can find your done and have to manually
look at the non-matched records to make sure you have not 'missed them'

Identify any records in the Past Award Winners with duplicate last names (a
grouping query, grouped on last name only with a count of ID's >1 will find
them)
Identify an records in the RSVP with duplicate Last Names (same)

Now you know what records you may need to manually check for non-matches.


Now find the complete matches (last name and first name are the same)
Now for the records that have not been matched match on Last Name and some
number of characters (5) of the first name :: FirstPortion:left([first
name],5)
Now for the records that have not been matched match on Last Name and one
less character.

When you're done you will be able to:

1. Find the records that failed all matches -- sorry your going to manually
work through these (not matched because of bad record validation, or not
coming to the party)
2. Find the records in the 'Past award winners' with more than one
atch --- you will probably have to manually resolve these.

I just spent a couple of days 'cleaning' a dataset, I assure you people will
make some very strange errors if they are allowed to enter 'free text'

Smith --> Smithe
Smith John H --> Smith John H. Jr.

Hope this helps and good luck

Ed Warren
 
Back
Top