Solved

Access string comparison

Posted on 2013-11-10
9
295 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

832 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