Re: Need help to find most popular records in 2 related Tables
- From: "Mota" <tavassoli68@xxxxxxxxxxx>
- Date: Wed, 21 Dec 2005 01:13:45 +0330
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:
- Re: Need help to find most popular records in 2 related Tables
- From: Tom Ellison
- Re: Need help to find most popular records in 2 related Tables
- References:
- Need help to find most popular records in 2 related Tables
- From: Mota
- Re: Need help to find most popular records in 2 related Tables
- From: Tom Ellison
- Need help to find most popular records in 2 related Tables
- Prev by Date: RE: Conditional Grouping
- Next by Date: Re: Conditional Grouping
- 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
|
