Need help to find most popular records in 2 related Tables

[Mota]

Hi;
In a medical system we store all prescriptions in 2 related tables.The
parent Tbl has Fields likeID (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.

 

[Tom Ellison]

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]

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.

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

Hi;
In a medical system we store all prescriptions in 2 related tables.The
parent Tbl has Fields likeID (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.

 

[Tom Ellison]

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

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.

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

Hi;
In a medical system we store all prescriptions in 2 related tables.The
parent Tbl has Fields likeID (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.

 

[Mota]

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.

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

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.

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


Hi;
In a medical system we store all prescriptions in 2 related tables.The
parent Tbl has Fields likeID (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.

 

[Tom Ellison]

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

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.

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

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.


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


Hi;
In a medical system we store all prescriptions in 2 related tables.The
parent Tbl has Fields likeID (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.

 

[Mota]

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

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

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.

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


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.


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


Hi;
In a medical system we store all prescriptions in 2 related
tables.The
parent Tbl has Fields likeID (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.