Solved

Join and query two tables for matching data

Posted on 2014-03-17
5
498 Views
Last Modified: 2014-03-21
Good Day Expert,

I am trying to create a query that joins the two tables (FACMatch and CSWMatch) on FirstName, LastName, and Certificate #, with email address from one or both of the tables with matching results.

I am looking for members who moved to FACMatch who are also in the ‘CSWMatch.  I am trying to searching by cert number in FACMatch and the Cardholder id in CSWMatch.  Also the cardholder id in CSWMatch should have the last two digits removed.

Here is what I have so far, but not quite sure how to join the tables? I tried the wizard but because the tables are not joined the information I would like is not coming out.

Any assistance you can provide would be greatly appreciated.

Thanks

TRANSFORM Count(CSWMatch.[customer id]) AS [CountOfcustomer id]
SELECT CSWMatch.[lname], CSWMatch.[fname], CSWMatch.[cardholder id (rxclaim member id)], Count(CSWMatch.[customer id]) AS [Total Of customer id]
FROM CSWMatch
GROUP BY CSWMatch.[lname], CSWMatch.[fname], CSWMatch.[cardholder id (rxclaim member id)]
PIVOT CSWMatch.[email];
0
Comment
Question by:Beeyen
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39934273
Start with the joined table

SELECT * FROM
    FACMatch f JOIN CSWMatch c
    ON (f.fname=c.fname AND f.lname=c.lname)

After that, I kinda lost you.

Is the certnumber field in FACMatch the same number as the cardholder ID in CSWMatch? If so, you want to add that in your JOIN clause, like this:

SELECT * FROM
    FACMatch f JOIN CSWMatch c
    ON (f.fname=c.fname AND f.lname=c.lname AND f.certnumber=c.cardholderid)

This query will show people who are in both tables. It is not possible to determine if someone moved from one table to another unless you have data for that.

If you need to trim the last two digits off of cardholder and the field is an integer, you can simply divide by 100, or use LEFT(c.cardholderid, LEN(c.cardholderid)-2)
0
 

Author Comment

by:Beeyen
ID: 39934403
Thank you for responding.  You are correct. The certnum field in FACMatch is the same number as the cardholderid in CSWMatch.  I tried using the code below and am receiving a syntax error in From clause.

SELECT * FROM
    FACMatch f JOIN CSWMatch c  
    ON (f.fname=c.fname AND f.lname=c.lname AND f.certnum=c.cardholderid)

Also where in the design view can I use the statement LEFT(c.cardholderid, LEN(c.cardholderid)-2) to remove the last two digits of the cardholderid
0
 

Author Comment

by:Beeyen
ID: 39934410
I have attached the accdb
CSWMatch.accdb
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39938714
This query worked for me.

SELECT *
FROM FACMatch INNER JOIN CSWMatch ON
((FACMatch.CertNum = int(CSWMatch.[cardholder id] / 100))
AND (FACMatch.[Mbr First Name] = CSWMatch.fname)
AND (FACMatch.[Mbr Last Name] = CSWMatch.lname))

If you want to do this in designer, just draw lines between the first name and last name fields. I'm not sure how you'd do the math for certnum, though.
0
 

Author Closing Comment

by:Beeyen
ID: 39945079
Thanks for getting back.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

920 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