Solved

Join and query two tables for matching data

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

830 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