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
PATIENT NAME SSN STREET ADDRESS CITY STATE ZIP SPONSOR SSN SPONSOR NAME
Sheet 2
PATIENT NAME SSN STREET ADDRESS CITY STATE ZIP SPONSOR SSN SPONSOR NAME
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.
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.
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.
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.
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.
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.
It looks like other dopers have already given you a good method to go about this, but I did want to comment that I actually was able to get my work to re-allow my access to message boards just because of situations like this. Where I was able to go to a large group of well informed people for an answer to some work issue. Sure the SDMB has sections that aren’t stricly factual, but there are a lot of other specialty sites that I’m a member of that are just about one program or system. So it may be worth your while to talk to your IT department about it. Good Luck!
If you’re still having trouble with this, please do post your query on the message board (I think in access all you need to do is open up your query and go to SQL view, if that’s not what you’re using).
It’s worth noting, fwiw, that Excel also supports SQL queries for simple tasks. There may be other ways, but once I’ve started using SQL I haven’t wanted to go back.
I wish I could, but it won’t happen. I am a civil servant for Department of the Army and they are REALLY, REALLY clamping down on things like this. I even lost access to a federal employees board because it was considered “chat”.
I brought it up to one of our AMO/IT guys and he told me he was in the same boat. So, there you have it.
