How to link lookup table with another table in Access 1

[error123]

Hi,

I have a lookup table that contains all of my PCPs. I also have the PCP's in another table.
I would like to see which PCP's do I have ofr the month of January 1-31 and which PCP does not appear in the lookup table, yet.

-We add them manually after finding them in the other table.-

Please take a look at my query so far:

SELECT DISTINCT Last(Cath_Extension.PCP) AS LastOfPCP, Event_Cath.Date_of_Cath, LU_ReferringMDList.ReferringMD
FROM LU_ReferringMDList LEFT JOIN (Cath_Extension INNER JOIN Event_Cath ON Cath_Extension.SS_Event_Cath_ID = Event_Cath.SS_Event_Cath_ID) ON LU_ReferringMDList.ReferringMD = Cath_Extension.PCP
GROUP BY Event_Cath.Date_of_Cath, LU_ReferringMDList.ReferringMD
HAVING (((Last(Cath_Extension.PCP)) Is Not Null) AND ((Event_Cath.Date_of_Cath) Between #1/1/2007# And #1/31/2007#))
ORDER BY Last(Cath_Extension.PCP), LU_ReferringMDList.ReferringMD;

Thank you!!!!!

 

[BSman]

Try this different approach (using the query builder):

First, build a query that selects all of the data from your main data table, limiting the date range to your selected date range. I'll call that qry1.

Second, build a second query that links qry1 to your lookup table by the PCP, set up to return all records in qry1. Show the PCP from both tables, but the criteria for the LU table should be that PCP is null. If there are multiple instances of the same PCP in the main table, make this a grouping query (and set the second PCP to a where not a group by). The result should be all PCP values missing from your lookup table.

Once the result from the second query is correct, you can change that query to an append query and append the missing PCP values to the lookup table, avoiding any manual entries.

Bob

 

[error123]

Thank you! I did something like that! But I missed the criteria when a Referral MD or PCP field is actually empty.

First query:

SELECT DISTINCT d.Last_Name, d.First_Name, d.Patient_ID, c.Date_of_Cath, ce.Report_Attending, c.Referring_MD, lu.PCPGroup AS ReferringMDCardGroup, ce.PCP, cp.Procedure_Name, ce.Insurance, lu.Market
FROM demographics AS d, event_cath AS c, cath_extension AS ce, cath_procedures AS cp, lu_referringMDlist AS lu
WHERE (((c.Date_of_Cath) Between [Start_date] And [End_Date]) AND ((cp.SchedulingType)='Performed' Or (cp.SchedulingType)='Scheduled & Performed') AND ((d.SS_Patient_ID)=[c].[ss_patient_id]) AND ((c.SS_Event_Cath_ID)=[ce].[ss_event_cath_id] And (c.SS_Event_Cath_ID)=[cp].[ss_event_cath_id]) AND ((lu.ReferringMD)=[ce].[PCP]))
ORDER BY d.Last_Name, d.First_Name, c.Date_of_Cath;

Second query:
SELECT Demographics.Patient_ID, Cath_Extension.PCP, LU_ReferringMDList.PCPGroup, Last(Event_Cath.Date_of_Cath) AS LastOfDate_of_Cath
FROM LU_ReferringMDList INNER JOIN ((Demographics RIGHT JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) INNER JOIN Cath_Extension ON Event_Cath.SS_Event_Cath_ID = Cath_Extension.SS_Event_Cath_ID) ON LU_ReferringMDList.ReferringMD = Cath_Extension.PCP
GROUP BY Demographics.Patient_ID, Cath_Extension.PCP, LU_ReferringMDList.PCPGroup
HAVING (((Last(Event_Cath.Date_of_Cath)) Between [Start_Date] And [End_Date]))
ORDER BY Cath_Extension.PCP;
Final query:

SELECT AD_Cathlabpat.*, AD_cathlabpat2.PCPGroup
FROM AD_Cathlabpat INNER JOIN AD_cathlabpat2 ON AD_Cathlabpat.Patient_ID = AD_cathlabpat2.Patient_ID;

How could I incorporate the empty fields as well?

And how would I make this an append query?

THANK YOU!

 

[BSman]

Bring up the query designer, select Query from the top menu and then append. It will then ask you what table to append data to...select your lookup table.

In the query design grid you what column in the lookup table will be the destination for each column returned by the query. I assume you just want to add the PCP column values, but if you want other values from your main table to be added, include them in the query.

I don't understand your question about incorporating the empty fields. If there is a different column (ReferralMD?) that you need to add to (another?) lookup table also, then just create a different set of queries using the ReferralMD column as the linking column where there is a null value in your lookup table.

Bob

 

[error123]

Thanks Bob.

The append query worked!!!