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

Dear Mota:

You may notify me by email if you wish. Though it earns me a lot of spam, I
have continued to post with my real email address here. Since so few do so,
it may be that the volume of spam will decrease some day.

Be advised I will probably only read the messages you post in this group,
and answer here. Do not send me technical details in such an email, just
notification. Please include the title of your original post, the newsgroup
to which you posted ("queries" would be sufficient in the case of this one),
and the date/time of the original post. I can find it most quickly that
way.

I am currently experiencing the delight of the imminent failure of my boot
drive (I have 6 HDs in this computer, but it would be the boot drive that is
failing). I'm likely to get pretty scarce here if I have to rebuild the
system unless it fails completely on a day when I have plenty of time to
work on it. Sympathy cards welcome! There is also some question of how I
will catch up if and when the total failure happens (I'm going to screw
around with the manufacturer's software first to try to bring it back
on-line, but not until I have a replacement drive lined up and ready to go).
All this takes time, expecially trying to recover important, volatile files
from a system drive. I have backups, but this doesn't help so much with
email and newsgroup communications that change every hour. Oh, joy. Maybe
I'll take the extra time to make my system drive mirrored this time. Yuck!

I do look at all new posts in this newsgroup. I don't follow threads in
which I have not been involved, but rarely miss entirely a post in response
to one of my own (except when I totally lose track due to a HD crash or
similar tragedy). The delay in getting my responses is more likely to be
due to my schedule and just how much time I can dedicate to posting here. I
have to limit considerably just how many "open" posts I have, or I'd be
swamped. Once I first answer a post, I realize others may bypass that post
to avoid duplication of effort. So that actually gives me a small feeling
of obligation to the original poster to continue with him to some
conclusion. So, normally, just posting again in here will be as effective
as anything. But with an imminent crash in view, it is quite possible I'll
lose track of what I've read and what I've not read, having to start over
with a new system showing all my posts as "unread". So, please feel free to
email me if I seem to have lost track of you. I will often have 10-15
threads in which I'm involved, and other than the ability to track which
posts I've read and which I've not read, I cannot easily track them.

Tom Ellison


"Mota" <tavassoli68@xxxxxxxxxxx> wrote in message
news:uIrY$hdBGHA.1124@xxxxxxxxxxxxxxxxxxxxxxx
> 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 ... >> Doctor.Medicines of this Prescriptions are saved in ChildTBL as follow: ...
    (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