Solved

Access string comparison

Posted on 2013-11-10
9
290 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 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

17 Experts available now in Live!

Get 1:1 Help Now