Re: Need help to find most popular records in 2 related Tables
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Tue, 20 Dec 2005 16:13:51 -0600
Dear Mota:
I believe you have made your problem much clearer to me. And I do hope I
may be on track to be of assistance.
The principle of my response will remain the same. Here's an outline you
may want to reference in order to follow along, and to correct any
misconceptions I might have about the details of your setup:
1. Have a totals query by Physician/Drug, showing the correct number of
instances in which the Physician prescribed that drug. This will return
only those columns: Physician/Drug/RepetitionCount.
2. Create a ranking query on the above, to rank the RepetitionCount. Apply
a "tie breaker" if desired so the ranking values are unique.
3. Write a query based on #2 above that filters out those ranked > 8.
4. Add whatever other details are needed in your final result.
Could you attempt the above at this time? Please try to get through at
least step #1 above. Post the SQL of your query and some of its results. I
will then attempt to assist you from that point.
I believe this approach will minimize the effort required by each of us, and
maximize your learning opportunity.
Tom Ellison
"Mota" <tavassoli68@xxxxxxxxxxx> wrote in message
news:egqL26aBGHA.1312@xxxxxxxxxxxxxxxxxxxxxxx
> Dear Tom;
> I hope u check for my reply.Because,regarding ur design,u have a good clue
> for my problem.But there is some misunderestandings in it that returns to
> one of ur questions.So,i have to first answer ur questions:
> "Would you be having multiple drugs on one prescription?"
> Yes,most of prescriptions have more than 1 medicine.In addition,2 tables
> are related thru field PID,not PhysicianID.The childTBL has not a
> PhysicianID field at all.What u commented can find 8 most repeated DRUGS
> of a doctor.But a Prescription is composed of 1 or MORE drugs.And we want
> PRESCRIPTIONS that are most common for a Doctor.Here are some sample data.
> For ParentTBL:
> PID PhysicianID TotalPrice DOP ......
> 1 122 60$ 11.01.05
> 2 256 85$ 11.01.05
> 3 122 140$ 11.01.05
> 4 122 85$ 11.01.05
> 5 256 75$ 11.01.05
>
> This shows that in 1 day,we had 5 Prescriptions,all from 2
> Doctor.Medicines of this Prescriptions are saved in ChildTBL as follow:
> PID dID dNu
> 1 5 10
> 1 14 20
> 2 84 20
> 3 5 10
> 3 14 20
> 4 5 10
> 4 14 20
> 5 84 20
>
> As u see,our Physicians have repeated (from point of their medicine)
> Prescriptions.Prescriptions 1,3 and 4 are just alike,Prescribed by a
> Physician with ID=122.Prescriptions 2 and 5 also are alike prescribed by
> another doctor with ID=256.So we can name this repeated Prescriptions as
> Sample1 for doctor 122,and sample1 for doctor 256.Now we want to find some
> another most repeated Prescriptions and name them as another samples for
> each Physician,and put this samples in a saparate table (SamplesTBL).Than
> I can reffer to this table thru my code,after a user enters a
> PhysicianID,and show him/her a list of most popular Prescriptions that
> this doctor usually prescribes.Now this user can click on 1 sample (that
> matches the drugs of the current Prescription,even orderingly).This click
> adds the selected sample to our ChildTBL (where we store the drugs of each
> prescription),instead of entering the current Prescription's drugs
> manually.This is my goal of making this table.So the SamplesTBL i want to
> fill out using the requested query,would be something like this:
>
> PhysicianID SampleNo dID FrequencyOfThisSample
> 122 1 5 3
> 122 1 14 3
> 256 1 84 2
>
> Based on the sample data,we faced to 1 sample (Repeated Prescriptions) for
> each one of the doctors.Obviously,we can find more samples (my favorite is
> 8 sample for each Doctor),when we UNION Parent and Child tables of the
> last 3 months,and run ur commented query on it.
> I hope i could describe what im looking for.Thank you for ur time and
> attention so much.
>
>
> "Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
> news:OOzl9BHBGHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
>> 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: Conditional Grouping
- Next by Date: Re: Easy ?
- Previous by thread: Re: 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
|
