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

Ok,will do so.
Thank you for all ur helps.

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:uRtlNsoBGHA.1092@xxxxxxxxxxxxxxxxxxxxxxx
> 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: More musings about the state of this group
    ... denegration of this newsgroup since my so-called departure generated ... Gosh, just look at those posting tallies for the past four months, ... Your response is the ultimate in hypocrisy. ...
    (rec.music.beatles)
  • Re: More musings about the state of this group
    ... denegration of this newsgroup since my so-called departure generated ... Gosh, just look at those posting tallies for the past four months, ... Your response is the ultimate in hypocrisy. ...
    (rec.music.beatles)
  • Re: Old Duke is a TROLL
    ... the majority of cases you are technically correct. ... "Don't post binaries in a text newsgroup. ... They may or may not be reading into your text - as you may be posting them ... response to the postings you do read - they have to make a choice whether or ...
    (microsoft.public.windowsxp.hardware)
  • Re: Microsoft JDBC Driver Performance Degradation
    ... posting using a registered alias) are guaranteed a response and you're not ... posting from a registered alias. ... this is not the proper newsgroup ... newsgroup and you won't get a response to your post. ...
    (microsoft.public.sqlserver.connect)
  • Re: CString to double conversion
    ... I figure if someone cares about the original post then they can ... Joe does a nice job of inline posting with judicious use of flower ... Tsk, tsk, Joe. ... Joe gave a response virtually ...
    (microsoft.public.vc.mfc)