Join query causes number of records to go from 389 to 67-URGENT

[capndave]

I have a query containing all of the demographic information for a list of physicians containing 389 records (qPhyDemo). When I create a new query which includes this query (placed as qPhyDemo* on the query grid)and 6 other tables (placed on grid as table*) linked by phyID the resulting query result contains only 67 records. The joins between the qPhyDemo and the other 6 tables is to include all records in qPhyDemo and others that match. I know that not every physician in qPhyDemo is listed in each of the other 6 tables.
What is happening and how can I fix it before I become a very visible failure in my organization? Thanks in advance.
CapnDave

 

[dhookom]

Try use LEFT or RIGHT joins to display all record from qPhyDemo. Add the linking column from all the related tables to find out which PhyIDs are missing.

Duane
Hook'D on Access
MS Access MVP

 

[capndave]

Thanks dhookom-I believe that is what I have done the query is to display all records from qPhyDemo and the records from the other tables that have a matching phyID. NOt sure I understand your second comment?

Thanks

 

[dhookom]

Share your SQL. Are you placing any criteria against fields in other tables?

Duane
Hook'D on Access
MS Access MVP

 

[capndave]

SELECT qPhySum.*, tblCaseMixSrv.*, tblConsultServ.*, tblMedRecSrv.*, tblPhyActSrv.*, tblReadmServ.*, tblSurActSrv.*
FROM tblSurActSrv RIGHT JOIN (tblReadmServ RIGHT JOIN (tblPhyActSrv RIGHT JOIN (tblMedRecSrv RIGHT JOIN (tblConsultServ RIGHT JOIN (tblCaseMixSrv RIGHT JOIN qPhySum ON tblCaseMixSrv.ServCode_CaseMix = qPhySum.PhySpecCode) ON tblConsultServ.ConPhySrvCode = qPhySum.PhySpecCode) ON tblMedRecSrv.MedRecSrvCode = qPhySum.PhySpecCode) ON tblPhyActSrv.AttendPhySrvCode = qPhySum.PhySpecCode) ON tblReadmServ.AttendPhyReadSrvCode = qPhySum.PhySpecCode) ON tblSurActSrv.EpisodeMDSrvCode = qPhySum.PhySpecCode;

There are no criteria in the query.

thanks for taking a look- I have had an issue with "Nz" in the past, but not sure if that is problem or how to apply here. While waiting to solve I am adding all the phy to all of the other tables even though there is not assoc data.

 

[PHV]

What about this ?

SELECT qPhySum.*, tblCaseMixSrv.*, tblConsultServ.*, tblMedRecSrv.*, tblPhyActSrv.*, tblReadmServ.*, tblSurActSrv.*
FROM (((((qPhySum
LEFT JOIN tblSurActSrv ON tblSurActSrv.EpisodeMDSrvCode = qPhySum.PhySpecCode)
LEFT JOIN tblReadmServ ON tblReadmServ.AttendPhyReadSrvCode = qPhySum.PhySpecCode)
LEFT JOIN tblPhyActSrv ON tblPhyActSrv.AttendPhySrvCode = qPhySum.PhySpecCode)
LEFT JOIN tblMedRecSrv ON tblMedRecSrv.MedRecSrvCode = qPhySum.PhySpecCode)
LEFT JOIN tblConsultServ ON tblConsultServ.ConPhySrvCode = qPhySum.PhySpecCode)
LEFT JOIN tblCaseMixSrv ON tblCaseMixSrv.ServCode_CaseMix = qPhySum.PhySpecCode

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[capndave]

Thanks, that worked for giving me all of the 389 records, however I am not getting the data for selected fields in the 6 linkded tables. Any ideas?

 

[dhookom]

capndave,
Are you truly "I am not getting the data for selected fields in the 6 linkded tables" for all records for all fields? Are there any values from the joined tables showing?

If you get some but not all, then you don't have matching PhySpecCode values in the joined tables.

Duane
Hook'D on Access
MS Access MVP