Solved

Join and query two tables for matching data

Posted on 2014-03-17
5
503 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
[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
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
usage of ms/excell 3d maps in ms/access 2016 6 43
GA Ribbon creator 9 63
Speed up Select Top n... Query 9 37
Convert MS Access Excel Routine to Late Binding 6 25
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 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