# Should I use Access or Excel to do this?

I am trying to automate a process for some staff in my office.

Basically, what I need to do is compare a list of names, SSNs, and addresses to another source and find the matches. I’m trying to determine if sponsor in sheet 2 is already enrolled in program that we may enroll names in sheet one into.

The common field or match would be the SSN. End result would be to put the matching sponsor names and SSNs into two additional columns. The sponsor ssn would be the same as SSN (which I’ve already split from FMP/SSN). We use the sponsor’s SSN as the FMP/SSN.

I tried doing an inner join in Access, but I couldn’t get it to work.

I know the solution is simple, but I seem to have developed a huge brain fart.

Sample of sheets or tables:

Sheet 1

Sheet 2

Any help would be appreciated. I apologize if someone has already done this, but I couldn’t find it.

If the solution is to use Excel, I’d appreciate a sample formula.

First off, use the SSN as your key.

Then combine both spreadsheets into one and sort on the respective SNN to find duplicates.

You can also use the “MATCH” function from one spreadsheet to the next. It looks like this:

=MATCH(H26,[Workbook2]Sheet1!\$I\$11:\$I\$23,0)

where H26 would be the SSN you are looking up, “Workbook2” is the workbook containing the other numbers, and \$I\$11:\$I\$23 is the range in Workbook2 containing the numbers. The “0” at the end of the formula means look for an exact match.

w.

I’d use Access in a heartbeat for this. I don’t know why your inner join isn’t working, unless one of your SSNs is perhaps being stored as a numeric and the other one is text. Look at your table definitions

Are you just “drag-and-drop” joining in the Access query, or are you writing it in SQL?

I agee with the index advice above, but the inner join should even work without it. It would just run more inefficiently.

If it’s already in access then I’d stay with that but you could do it with Vlookup in Excel pretty easy.

FORMULA IN CELL B1 =VLOOKUP(A1,C1:C3,1,0)

A______B_______C
1__JOHN__JOHN___AMBER
2__ANDY
#N/A___JOHN
3__SUE
_#N/A___GEORGE

A1 IS THE CELL YOU WANT TO COMPARE FROM THE 1ST SOURCE
C1:C3 IS THE RANGE OF THE DATA YOU WANT TO COMPARE IN THE 2ND SOURCE
1 IS THE COLUMN YOU WANT INFORMATION FROM.
0 IS A TRIGGER FOR EXACT MATCH.

Note that for VLOOKUP, you need to first sort the data by the column containing the SSN numbers …

w.

Ditto what Kilvert’s Pagan wrote.

Here’s an additional idea, using Access: Copy everything from Sheet1 into a new table, and Paste Append eveything from Sheet2 into that same new table. Then do a Query on the new table, and make two columns, both of the SSN field. Press the “Totals” (the Greek letter sigma) button. For the first column, the Total line will be “Group By”. For the second column, the Total line will be “Count” and the Criteria will be “>1”. This query will show you all the SSNs which show up more than once anywhere in the two lists.

Not in Magiver’s example; the last parameter is a zero, which means exact match, so unsorted data is fine. It’s only with the “closest match” option that you need the column to be sorted.

From an Access Help page

I got wrapped up in some things and couldn’t get back in here until tonight. I’ll try the inner join and again and double check the data definition before I actually attemp the join again.

If I still come up with problems, I’ll try the others.

Since they’ve now blocked access to the message boards portion of the Dope at work, I can’t get in and check during the day any more.