Query to match data from two tables

[mpreston14]

I have multiple sources of data that I import into a referee database, because they are all disparate systems, they send all sorts of different data for the same referee's. In order to match up everyone correctly I have a UserID Table that matches to the unique Ref ID from the Ref Table. This enables me to import the data and ensure it is credited appropriately.

Every season we get hundreds of new refs that need to be added. The Ref Table is easy to get get sorted, but the UserID's have been a real PITA. I have been hand keying, but I've had enough of that...

The UserID's are formatted as: First 2 char of First Name,Last Name, last 2 of birth year. For example JOSMITH02 would equal John Smith 2002.



To make my life easier...I created a new table "ImportUN" with a breakout of the user ID into four fields: Username, FirstName (just the first 2 characters), LastName, BDayYear
The Ref Table has the full demographics and each entry is given a Ref ID.

I would like to be able to match up the Username in ImportUN Table to the person in Ref Table.

I built a simple select query, joining the lastname fields and it did pretty good...but where there are referee's with the same last name we run into trouble.

How can get a query to match the last name, and evaluate the first two characters of the first name (from the Ref Table) to the LastName and FirstName fields in the ImportUN Table, and use the BDayYear field as the final criteria?

Clear as mud?

Thanks!

Mike

[June7]

That's not a foolproof unique ID. It is possible to have more than one John Smith born in 2002.

A query can link tables on more than one field - compound key.

[mpreston14]

I don't want to create a User ID from the data I have, I want to take a User ID and match it to a referee that is already defined in a table. Does that make more sense?

[June7]

I didn't say you want to create a new ID, just pointing out that the ones you are using are flawed.

Build a query from the Ref table that constructs the unique ID with an expression. Then build another query that joins that query to the ImportUn table.

[mpreston14]

Thanks June that worked!

I found some info on expressions, constructed the UserID from the Referee Table, then compared it to the ImportUN table, used the primary key from the ref table to link it all together and then appended the Username Table with the new UserID's and linked them to the Ref ID. Round about way to get there but it worked! I imagine there is a way to code what I wanted in one shot, but as a volunteer, I don't have the time to learn that for this application. I could of hand keyed all of this in by now but next season will be a breeze!