Solved

Access string comparison

Posted on 2013-11-10
9
293 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39637348
pcladylr,

the query i posted gives you what you are looking for..
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now