Need help to make a complex Query to find most frequented items

[Mota]

Hi;
In a medical system we store all prescriptions in 2 related tables.The
parent Tbl has Fields likeID (or PrescriptionID that is a unique Long
Number assigned to each new Prescription,and is the table's PK and the ONE
side of Relation),PhysicianID(that is the prescriber of this Prescription.we
have all Doctors and their unique IDs in another table),DOP(or Date Of
Prescription),TotalPrice,PatientName and so on.The child table in the other
hand contains fields:RecID(a unique autonumber field as the PK),PID(or
PrescriptionID that is the foreighn key or MANY side of this
relation),DID(Drug ID that shows each medicine of Prescription with this PID
in each record),DNu(quantity of this medicine),RetailPrice and so on.
After a few months we found that most prescribers have many repeated
Prescriptions,relative to their speciality.For example a physician with
ID=122 may have up to 70% of his Prescriptions,just like one of the 8 sample
we have already made from most frequented Prescriptions of him.
So i need help to make one or more query to find 8 most frequented
Prescription for each physician,if any.Then i will run this query system at
the end of each month and put this list of Prescriptions in a separate
table.
Hence,after a user enters PhysicianID in the form,program shows 8 most
repeated Prescriptions of this Doctor.Now user may select the matching
sample,or press ESC if the current Prescription is not in the list,and enter
its medicines manually.Its a big help for my users.
To make it easier,you can begin from the point that i have queried both
tables for 1 prscriber,so i have to make a SQL statement to find 8 most
repeated Prescriptions,from this Query.The code would be like this:
Set DB=CurrentDB
Set Rs=DB.OpenRecordset("Select Distinct Row PhysicianID From ParentTBL")
With Rs
Do Until .EOF
StrSql="Select ChildTBL.PID,DID,DNu From ParentTBL inner join ChildTBL on
ParentTBL.PID=ChildTBL.PID" & _
" Where PhysicianID=" !PhysicianID
SQLFindRepeated="...Campare all Prescriptions and Select 8 (or less) most
repeated of them,if any..."
...Using DAO,Put this list of 8 Prescriptions and their PhysicianID in a
reference Table,to be looked up in the future...
.MoveNext
Loop
End with

As you may guess,when comparing Prescriptions,order of items in each sample
is important,but quantity of medicines is not.Because correcting numbers
thru the form is not difficult for users.So a Prescription with
{MedicineA,MedicineB} differs from {MedicineB,MedicineA}.These are of 2
samples.But {MedA:N=10,MedB:N=20} must be counted in the cathegory that may
contains{MedA:N=30,MedB:N=10}.
What I need is SQLFindRepeated,that can be more than 1 SQL statement.
Can anyone please help me to make this query system?I appreciate your help
and thank you in advance so much.

 

[John Nurick]

Hi Mota,

I'm pretty sure that this can all be done with SQL, with no need for
recordset operations. So I've cross-posted this to the Queries newsgroup
where the SQL experts hang out.

The first stage is a query that returns, for each record in the child
table, PhysicianID, PID, DID and (because you say that the order in
which items appear within a prescription is significant) a calculated
field that is 1 for the first item, 2 for the second and so on,
restarting with each PID. It will look something like this (though your
table names are different):

SELECT P.PhysicianID, C.PID,
(SELECT COUNT(1)
FROM MotaPrescriptionDetails AS B
WHERE (B.RecID <= C.RecID) AND (B.PID = C.PID)
) AS ItemNumber,
C.DID
FROM MotaPrescriptions AS P INNER JOIN MotaPrescriptionDetails AS C
ON P.PID = C.PID
ORDER BY P.PhysicianID, C.PID, C.RecID;

In principle you can use this query as the source for a crosstab query
which returns one row for each Prescription, with columns for
PhysicianID, PID, and the first, second, third etc. item in the
prescription. So each row shows the various DIDs in each prescription in
order. (I say in principle because I can't make it work in practice
unless I use the first query to make a temporary table with the
sequential ItemNumbers: otherwise the crosstab query seems to choke on
the calculated field. But maybe a SQL guru will sort that out for us.)

Once you've got the crosstab query, it can serve as the basis of a
totals query, which groups by PhysicianID and all the ItemNumber columns
and returns the count of PID. From this you can extract the most popular
prescriptions written by each physician.