Solved

Join and query two tables for matching data

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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