Re: Need help to find most popular records in 2 related Tables
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Mon, 19 Dec 2005 01:48:02 -0600
Dear Mota:
The general approach would be to start with a correlated subquery that
returns the TOP 8 prescriptions for the current physician. Place this in an
IN clause and build that into your filter.
Let's look at how to build this up.
SELECT TOP 8 [Drug ID]
FROM ChildTBL
WHERE PhysicianID = XXXXX
GROUP BY PhysicianID, DrugID
ORDER BY COUNT(*) DESC
I used COUNT instead of sum, as you said you wanted the 8 most "repeated"
prescriptions. For testing, put in an actual PhysicianID. Next, we're
going to correlate on PhysicianID.
SELECT C.PhysicianID, DID, DNu
FROM ParentTBL P
INNER JOIN ChildTBL C
ON C.PhysicianID = P.PhysicianID
WHERE DID IN(
(SELECT TOP 8 DID
FROM ChildTBL C1
WHERE C1.PhusicianID = P.PhysicianID
GROUP BY PhysicianID, DID
ORDER BY COUNT(*) DESC))
I'm not sure I've followed all your column naming. In fact, I'm not sure I
understand your table design. Would you be haiving multiple drugs on one
prescription? If not, how would it be you have multiple child rows for a
single parent row. Does the parent row represent one prescription?
Anyway, the principle of what I'm trying to show you is there. With any
luck, what I've coded may be fairly close. To be sure, you may need to
study what I've done and figure out how it's supposed to work. You can
possibly then fix it up for what you want.
Please let me know if this helped. If it's too far off, please give some
sample data and the desired results, so I can figure it out better.
Tom Ellison
"Mota" <tavassoli68@xxxxxxxxxxx> wrote in message
news:e6bXjtABGHA.2356@xxxxxxxxxxxxxxxxxxxxxxx
> Hi;
> In a medical system we store all prescriptions in 2 related tables.The
> parent Tbl has Fields like:PID (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.
>
>
>
.
- Follow-Ups:
- References:
- Prev by Date: Re: Moving column data from one table to another
- Next by Date: Re: Query Bug
- Previous by thread: Need help to find most popular records in 2 related Tables
- Next by thread: Re: Need help to find most popular records in 2 related Tables
- Index(es):
Relevant Pages
|
Loading
