query two tables for records shared LESS THAN 3 TIMES

[newtome]

hello,



am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.

application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.
each patient has an annual physical (once a year), and chooses one doctor per annual physical.
each doctor can only perform three or less annual physicals per year.
it is a small application with less than 1000 records per table.

the query will allow the patient to choose an annual physical doctor once a year from available doctors.

Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.

query for all records NOT shared between Doctors and PatientPhysicalDoctors is:

SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );

big thanks in advance-

[June7]

You want to know which doctors have done less than 3 physicals? Try:

SELECT Doctors.DoctorID FROM Doctors LEFT JOIN PatientPhysicalDoctors ON Doctors.DoctorID=PatientPhysicalDoctors.DoctorIDFK WHERE Year([PhysicalDate])=[enter year] GROUP BY DoctorID HAVING Count(Nz(PatientPhysicalDoctors.IDFK,0)) < 3;

[newtome]

You want to know which doctors have done less than 3 physicals? Try:

SELECT Doctors.DoctorID FROM Doctors LEFT JOIN PatientPhysicalDoctors ON Doctors.DoctorID=PatientPhysicalDoctors.DoctorIDFK WHERE Year([PhysicalDate])=[enter year] GROUP BY DoctorID HAVING Count(Nz(PatientPhysicalDoctors.IDFK,0)) < 3;

fantastic, you solved it! thank-you very much!

i have not used GROUP BY, HAVING, and Count before, but will remember them.

my kludge for uniqueness between year/patient and doctors is as follows:

PatientPhysicalDoctors table has 3 fields: YearIDFK, PatientIDFK, and DoctorIDFK,
and a unique index of YearIDFK/PatientIDFK. if a user gets sloppy, he is stopped with an error.

hardcoding the checking for 2014 and Null on the HAVING line is a kludge (will need to change 2014 to 2015 next year) but i can live with it.

big thanks again!

SELECT Doctors.DoctorID, PatientPhysicalDoctors.YearIDFK
FROM Doctors LEFT JOIN PatientPhysicalDoctors ON Doctors.DoctorID=PatientPhysicalDoctors.DoctorIDFK
GROUP BY DoctorID, YearIDFK
HAVING Count(Nz(PatientPhysicalDoctors.DoctorIDFK,0)) < 3
And (PatientPhysicalDoctors.YearIDFK=2014 OR PatientPhysicalDoctors.YearIDFK Is Null);;

[June7]

I should have thought of including the year in the GROUP BY clause.

Could make the year criteria a dynamic parameter. Build a form with a textbox or combobox for entering the year value. The query can reference the box for the parameter.

Or build a report based on the query and pass filter criteria to the report when it opens.

[newtome]

the dynamic parameter for the query is a great idea.

I had YearIDFK as a field on the form, so I used it:

SELECT Doctors.DoctorID, PatientPhysicalDoctors.YearIDFK
FROM Doctors LEFT JOIN PatientPhysicalDoctors ON Doctors.DoctorID=PatientPhysicalDoctors.DoctorIDFK
GROUP BY DoctorID, YearIDFK
HAVING Count(Nz(PatientPhysicalDoctors.DoctorIDFK,0)) < 3
And (PatientPhysicalDoctors.YearIDFK=[YearIDFK] OR PatientPhysicalDoctors.YearIDFK Is Null);;

[newtome]

i jumped the gun, as this works for the first year (2014) but not for the second year (2015).

in 2014 it works because before a doctor is selected, there are no record(s) to count , BUT PatientPhysicalDoctors.YearIDFK Is Null, so a record is returned.

in 2015 before a doctor is selected, there are no records to count AND PatientPhysicalDoctors.YearIDFK Is NOT Null, so no records are returned.

another point of view is that HAVING Count(Nz(PatientPhysicalDoctors.DoctorIDFK,0)) < 3 does not return a record if the count is 0. that makes sense, and by looking at the query before this HAVING line, there are no records for a doctor that has not been selected to display anyway.

this one is tough. I will settle for any reasonable solution at this point...

[June7]

Shouldn't the YearIDFK criteria be in WHERE clause?

[newtome]

I've tried it there, but it´s really the same problem.

without any WHERE or HAVING lines, the query will only return the records that exist in the PatientPhysicalDoctors table, or a doctors record that has not been assigned (LEFT JOIN) from Doctors.

the YearIDFK criteria only works if the record exists in PatientPhysicalDoctors, or I add "is Null".

in 2014 the record "is Null" before it is added, then it is in PatientPhysicalDoctors to be counted.
in 2015 it is NOT "is Null" (is in PatientPhysicalDoctors with YearIDFK=2014), and is not in PatientPhysicalDoctors with YearIDFK=2015 to be counted.

good thing this is not "production"

[newtome]

brother you know your stuff (earlier question you solved). is this what I should be doing?
I wish now I had included the Year in the original problem description.

Crosstab Query to include all records from two tables/querys to count no of records.

https://www.accessforums.net/queries...les-42555.html

[June7]

If you want to show a physician/year combination even if there is no data for each pairing in PatientPhysicalDoctors, then yes, use the technique described in the link.

[newtome]

as Crosstab Query to include all records from two tables/querys to count no of records.indicated, I was able to:

1- do a Cartesian join between AnnualPhysical and Doctors (get all possibilities)
2- do a left Inner join between this and PatientPhysicalDoctors (all possibilities plus actual pairs)
3- do a Crosstab query to count all occurences, actual and possible

I was not able to use a dynamic parameter for the year in the Crosstab query. it is a column heading and beyond my skill set.
I have attached the db file with some simple forms for data entry.

I did not combine the queries, as this is all new to me:


carte1:
======
SELECT AnnualPhysicals.YearID, AnnualPhysicals.PatientID, Doctors.DoctorID
FROM AnnualPhysicals, Doctors;


carte1w1:
========
SELECT carte1.*, PatientsPhysicalsDoctors.*
FROM carte1 LEFT JOIN PatientsPhysicalsDoctors ON (carte1.DoctorID=PatientsPhysicalsDoctors.DoctorID FK) AND (carte1.YearID=PatientsPhysicalsDoctors.YearIDFK) AND (carte1.PatientID=PatientsPhysicalsDoctors.Patient IDFK);


carte1w1_Crosstab:
=================
TRANSFORM Count(carte1w1.YearIDFK) AS CountOfYearIDFK
SELECT carte1w1.DoctorID
FROM carte1w1
GROUP BY carte1w1.DoctorID
PIVOT carte1w1.YearID;


qcarte1w1_Crosstab:
=================
SELECT carte1w1_Crosstab.DoctorID, FirstName, LastName
FROM carte1w1_Crosstab INNER JOIN Doctors ON carte1w1_Crosstab.DoctorID=Doctors.DoctorID
WHERE [2014]<3;






[June7]

Maybe this will help for the crosstab http://allenbrowne.com/ser-67.html

[newtome]

the plot thickens...

that is a great page on crosstab queries, and I added column headings as it suggests.

however, it appears the WHERE value (now the static column heading 2014) needs to be enclosed in brackets ("[","]").
"[2014]" works, "((Forms!Patients![AnnualPhysicals subform].Form!YearID)" does not.

it will not produce an error, but returns no records.

i tried with PARAMETER defined as integer, text, etc, no luck. any ideas?


qcarte1w1_Crosstab (does not work inside form, or when prompting for value)
=======
PARAMETERS [Forms].[AnnualPhysicals subform].[YearID] Short;
SELECT carte1w1_Crosstab.DoctorID, Doctors.FirstName, Doctors.LastName
FROM carte1w1_Crosstab INNER JOIN Doctors ON carte1w1_Crosstab.DoctorID=Doctors.DoctorID
WHERE (((Forms.[AnnualPhysicals subform].YearID)<3));



carte1w1_Crosstab (modified)
=============
TRANSFORM Count(carte1w1.YearIDFK) AS CountOfYearIDFK
SELECT carte1w1.DoctorID
FROM carte1w1
GROUP BY carte1w1.DoctorID
PIVOT carte1w1.YearID In (2014,2015,2016);

[June7]

Got me stumped. I've never needed crosstab queries so never investigated them that far. Allen's article shows reference to form control as parameter but I've never tried this.