Re: Need help to find most popular records in 2 related Tables

Ok;Ill try it.
But this may last up to a few days (specially designing Step2 query),and im
afraid i miss you in the group.I wish i had an email from u,or another way
to inform u of my posting the SQLs.
Thanks again for your help.

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:u$IVhJbBGHA.1032@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Need help to find most popular records in 2 related Tables
    ... >>> Yes,most of prescriptions have more than 1 medicine.In addition,2 tables ... >>> PhysicianID field at all.What u commented can find 8 most repeated DRUGS ... >>> Based on the sample data,we faced to 1 sample (Repeated Prescriptions) ...
    (microsoft.public.access.queries)
  • Re: Need help to find most popular records in 2 related Tables
    ... > PhysicianID field at all.What u commented can find 8 most repeated DRUGS ... > PRESCRIPTIONS that are most common for a Doctor.Here are some sample data. ... > another doctor with ID=256.So we can name this repeated Prescriptions as ...
    (microsoft.public.access.queries)
  • Re: Need help to find most popular records in 2 related Tables
    ... "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 ... > for a single parent row. ...
    (microsoft.public.access.queries)
  • Drug discounts
    ... LITTLE ROCK, ARKANSAS - Wal-Mart Stores Inc., the world's largest ... 2006 to provide a 30-day supply of generic prescription drugs for $4. ... list of prescriptions available for $9, ... Target said it will expand its assortment of $4 prescriptions, ...
    (alt.support.arthritis)
  • The Insanity Continues as More U.S. Kids Are Taking Drugs for Behavior and Depression
    ... The Insanity Continues as More U.S. Kids Are Taking Drugs for Behavior ... disorder (ADHD) and depression in children and teens grew steadily ... Researchers found that prescriptions for stimulants used to treat ...
    (sci.med.psychobiology)
Loading