Solved

Join and query two tables for matching data

Posted on 2014-03-17
5
494 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

16 Experts available now in Live!

Get 1:1 Help Now