Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access string comparison

Posted on 2013-11-10
9
Medium Priority
?
310 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 2000 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
Stressed Out?

Watch some penguins on the livecam!

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 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