Solved

Access string comparison

Posted on 2013-11-10
9
305 Views
Last Modified: 2013-11-29
I need to compare data in two tables. Table 1 has the full, 9-digit social security number, but Table 2 only has the last four digits. I know I could extract the last four digits of the first table in Excel using Text to Columns, but it seems like there would be a way to do this when I create the subset of data for Table 1. I'm not real fluent in SQL, but understand the principles. This will be used on a monthly basis verify vendor statements, so I'd like to automate as much as possible now.

Appreciate an answer to this one.

Cathy
0
Comment
Question by:pcladylr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39637326
you can do this in access using a query like this

select table1.*,  table2.*
from table1, table2
where right([Table1].[ssn],4)=table2.[ssn]
0
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 39637332
You can use the Right function on the table 1 field:

Right(9-Digit SSN,4)

You can then compare that to Table 2

Flyster
0
 

Author Closing Comment

by:pcladylr
ID: 39637343
I chose your answer because it shows me how to create this four-digit field in the initial query where I am creating Table 1.

Thank you!
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39637348
pcladylr,

the query i posted gives you what you are looking for..
0
 

Author Comment

by:pcladylr
ID: 39637493
And, thanks to both of you for responding so quickly.
0
 

Author Comment

by:pcladylr
ID: 39685609
Capricorn1: Sorry, I just now saw your comment and realize that you indeed did provide the correct answer. I'm not very SQL literate and didn't recognize it. You certainly deserve points. Is there a way I can remedy this? Thank you.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39685622
I have no problems with splitting points. I forgot to refresh and didn't see Capricorn1's response!
0
 

Author Comment

by:pcladylr
ID: 39685628
Thanks, Flyster. How do I go about doing that?
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39685658
That's new territory for me. I would try the request attention link under your original post. That will get to one of the moderators.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question