Help with subquery

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following union query:
PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 20 6
004320 3/28/1996 30 6
004320 3/30/1996 40 6
004320 3/31/1996 50 6
004320 4/1/1996 60 6
004320 4/1/1996 60
004320 4/2/1996
004320 4/2/1996
004320 4/11/1996 62.6
I need to accomplish the following:
I need to carry on the bodywt to fill in the blank records to look like the
following:

PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 64 20 6
004320 3/28/1996 64 30 6
004320 3/30/1996 64 40 6
004320 3/31/1996 64 50 6
004320 4/1/1996 64 60 6
004320 4/1/1996 60
004320 4/2/1996 60
004320 4/2/1996 60
004320 4/11/1996 62.6

Can someone help.
thanks
Al
 
Dear Al:

I'll assume the "missing" body weight values are null and that this column
is numeric.

There would be a good way of doing this, except that your data is ambiguous.
You have a weight of 64 that has been entered for the missing weight dated
4/1/1996. Why couldn't the missing weight be filled by the 60 value of that
same date? In reality, this might be more realistically accurate. Since
the data you show does not allow me to see any unique ordering of the rows,
so it is hard to see why the 6th row and 7th row could not trade places.

In writing a query to do this, such questions are critical.

In order to retrieve any missing BodyWt values, you would need to use a
subquery. If you will post the SQL text of the query you have for the
sample you showed, I'll try to add the missing portion. If you don't answer
the questions I asked above, I suppose I would just arbitrarily choose one
method out of many possible methods, all of which potentially produce
different results.

Another question would be whether you could ever have two rows with the same
weight on the same date.

Finally, I'd like to suggest you could prorate the BodyWt between the
weights given. In the sample data, the weight would decrease by 4 over the
period of 6 days between the first two measurements, being about .7 per day.
This would be more realistic I expect, but you must then prohibit any
possibility of having 2 weights on the same date.

Tom Ellison
 
Tom, thanks for your respons. first, your assumption is correct bodywt is a
numeric and the missing values are null.
2nd, you are also right about the value. I entered 64 by mistake. this is a
union query and both dates and values are combined from 2 different tables,
as you know. the first tbl is "tblSteroids" the 2nd tbl is "tblVitalSigns"
the date 4/1/1996 existed in both tbls one had the value the other did not.
in that case the value of 60 should be populated in the missing bodywt. the
qry is sorted by dates and unless we have the above case(same date repeated
once with value and once without), the rule should be, bodywt value will
populate missing bodywt values for higher dates. so, if date is sorted
ascending, then 64 will populate missing bodywt down until it finds another
value then this new value will populate missing bodywt down and so on. it is
tricky when you have a situation like in the 4/1/1996 which should have the
value of 60 not 64.
I hope that I clarified your questions.
thank you
Al
 
Sorry forgot to paste the sql. here it is:
*************************************
SELECT [PatientID],[st_DateofTreatment] as [EventDate],"" as
[BodyWt],[st_MedicationVol] as [DailyDose(ml)],[st_MedicationFrequancy] as
[Frequancy]
FROM tblSteroids
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[st_DateofTreatment]>= [Forms]![frmSteroidsMain]![ProtocolStartDate] and
[st_DateofTreatment]<[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
UNION ALL SELECT [PatientID],[DateofExam] as [EventDate],[BodyWt],"" as
[DailyDose(ml)],"" as [Frequancy]
FROM tblVitalSigns
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and [DateofExam]>=
[Forms]![frmSteroidsMain]![ProtocolStartDate] and [DateofExam]<
[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
ORDER BY [EventDate];
*********************************************
 
Dear Al:

I see that the BodyWt values all come from tblVitalSigns, while the
DailyDose and MedicationFrequancy come only form tblSteroids. The solution
may be easier without the UNION query.

In order to form the type of JOIN I have in mind, we need to find the
DateOfTreatment from tblSteroids for each row in tblVitalSigns. That date
will be the same date, or the most recent previous date. I believe that
would be:

SELECT PatientID, DateOfExam, st_MedicationVol, st_MedicationFrequancy,
(SELECT MAX(DateOfExam) FROM tblVitalSigns V
WHERE V.PatientID = S.PatientID AND V.DateOfExam < S.DateOfExam) AS
LDate
FROM tblSteroids S

Does this query correctly return the date for the VitalSigns exam to be
associated with each Steroids record?

I am concerned there may be missing dates. This could reflect a condition
where there are rows for a patient in tblSteroids for which there is no
previously dated row in tblVitalSigns. According to your instructions, as I
understand them, this would be expected if that were to happen.

I will refer to this as Query1, assuming you will save that query.

You can then JOIN the tblSteroids to Query1 to get everything you need.

SELECT Q.PatientID, Q.st_DateofExam, Q.st_MedicationVol,
Q.st_MedicationFrequancy,
V.DateOfExam, V.BodyWt
FROM Query1 Q
INNER JOIN tblVitalSigns V
ON V.PatientID = Q.PatientID AND V.DateOfExam = Q.LDate

After you get it working, add to this query your forms based filters and the
sorting.

Does this begin to produce what you wanted? Please let me know if this
helped, and if you need any other assistance.

Tom Ellison


Al said:
Sorry forgot to paste the sql. here it is:
*************************************
SELECT [PatientID],[st_DateofTreatment] as [EventDate],"" as
[BodyWt],[st_MedicationVol] as [DailyDose(ml)],[st_MedicationFrequancy] as
[Frequancy]
FROM tblSteroids
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[st_DateofTreatment]>= [Forms]![frmSteroidsMain]![ProtocolStartDate] and
[st_DateofTreatment]<[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
UNION ALL SELECT [PatientID],[DateofExam] as [EventDate],[BodyWt],"" as
[DailyDose(ml)],"" as [Frequancy]
FROM tblVitalSigns
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and [DateofExam]>=
[Forms]![frmSteroidsMain]![ProtocolStartDate] and [DateofExam]<
[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
ORDER BY [EventDate];
*********************************************

Tom Ellison said:
Dear Al:

I'll assume the "missing" body weight values are null and that this
column
is numeric.

There would be a good way of doing this, except that your data is
ambiguous.
You have a weight of 64 that has been entered for the missing weight
dated
4/1/1996. Why couldn't the missing weight be filled by the 60 value of
that
same date? In reality, this might be more realistically accurate. Since
the data you show does not allow me to see any unique ordering of the
rows,
so it is hard to see why the 6th row and 7th row could not trade places.

In writing a query to do this, such questions are critical.

In order to retrieve any missing BodyWt values, you would need to use a
subquery. If you will post the SQL text of the query you have for the
sample you showed, I'll try to add the missing portion. If you don't
answer
the questions I asked above, I suppose I would just arbitrarily choose
one
method out of many possible methods, all of which potentially produce
different results.

Another question would be whether you could ever have two rows with the
same
weight on the same date.

Finally, I'd like to suggest you could prorate the BodyWt between the
weights given. In the sample data, the weight would decrease by 4 over
the
period of 6 days between the first two measurements, being about .7 per
day.
This would be more realistic I expect, but you must then prohibit any
possibility of having 2 weights on the same date.

Tom Ellison
Click to expand...
Click to expand...
 
Hi Tom,
Thanks for the help. This method got close but not quite. when you link the
queries (Query1 and the second query) it becomes a many to many relationship.
I will eventually make a report to calculate total dosages and this
relationship created many duplicates. I am going to follow up with you later
today.
thanks
Al

Tom Ellison said:
Dear Al:

I see that the BodyWt values all come from tblVitalSigns, while the
DailyDose and MedicationFrequancy come only form tblSteroids. The solution
may be easier without the UNION query.

In order to form the type of JOIN I have in mind, we need to find the
DateOfTreatment from tblSteroids for each row in tblVitalSigns. That date
will be the same date, or the most recent previous date. I believe that
would be:

SELECT PatientID, DateOfExam, st_MedicationVol, st_MedicationFrequancy,
(SELECT MAX(DateOfExam) FROM tblVitalSigns V
WHERE V.PatientID = S.PatientID AND V.DateOfExam < S.DateOfExam) AS
LDate
FROM tblSteroids S

Does this query correctly return the date for the VitalSigns exam to be
associated with each Steroids record?

I am concerned there may be missing dates. This could reflect a condition
where there are rows for a patient in tblSteroids for which there is no
previously dated row in tblVitalSigns. According to your instructions, as I
understand them, this would be expected if that were to happen.

I will refer to this as Query1, assuming you will save that query.

You can then JOIN the tblSteroids to Query1 to get everything you need.

SELECT Q.PatientID, Q.st_DateofExam, Q.st_MedicationVol,
Q.st_MedicationFrequancy,
V.DateOfExam, V.BodyWt
FROM Query1 Q
INNER JOIN tblVitalSigns V
ON V.PatientID = Q.PatientID AND V.DateOfExam = Q.LDate

After you get it working, add to this query your forms based filters and the
sorting.

Does this begin to produce what you wanted? Please let me know if this
helped, and if you need any other assistance.

Tom Ellison


Al said:
Sorry forgot to paste the sql. here it is:
*************************************
SELECT [PatientID],[st_DateofTreatment] as [EventDate],"" as
[BodyWt],[st_MedicationVol] as [DailyDose(ml)],[st_MedicationFrequancy] as
[Frequancy]
FROM tblSteroids
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[st_DateofTreatment]>= [Forms]![frmSteroidsMain]![ProtocolStartDate] and
[st_DateofTreatment]<[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
UNION ALL SELECT [PatientID],[DateofExam] as [EventDate],[BodyWt],"" as
[DailyDose(ml)],"" as [Frequancy]
FROM tblVitalSigns
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and [DateofExam]>=
[Forms]![frmSteroidsMain]![ProtocolStartDate] and [DateofExam]<
[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
ORDER BY [EventDate];
*********************************************

Tom Ellison said:
Dear Al:

I'll assume the "missing" body weight values are null and that this
column
is numeric.

There would be a good way of doing this, except that your data is
ambiguous.
You have a weight of 64 that has been entered for the missing weight
dated
4/1/1996. Why couldn't the missing weight be filled by the 60 value of
that
same date? In reality, this might be more realistically accurate. Since
the data you show does not allow me to see any unique ordering of the
rows,
so it is hard to see why the 6th row and 7th row could not trade places.

In writing a query to do this, such questions are critical.

In order to retrieve any missing BodyWt values, you would need to use a
subquery. If you will post the SQL text of the query you have for the
sample you showed, I'll try to add the missing portion. If you don't
answer
the questions I asked above, I suppose I would just arbitrarily choose
one
method out of many possible methods, all of which potentially produce
different results.

Another question would be whether you could ever have two rows with the
same
weight on the same date.

Finally, I'd like to suggest you could prorate the BodyWt between the
weights given. In the sample data, the weight would decrease by 4 over
the
period of 6 days between the first two measurements, being about .7 per
day.
This would be more realistic I expect, but you must then prohibit any
possibility of having 2 weights on the same date.

Tom Ellison


I have the following union query:
PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 20 6
004320 3/28/1996 30 6
004320 3/30/1996 40 6
004320 3/31/1996 50 6
004320 4/1/1996 60 6
004320 4/1/1996 60
004320 4/2/1996
004320 4/2/1996
004320 4/11/1996 62.6
I need to accomplish the following:
I need to carry on the bodywt to fill in the blank records to look like
the
following:

PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 64 20 6
004320 3/28/1996 64 30 6
004320 3/30/1996 64 40 6
004320 3/31/1996 64 50 6
004320 4/1/1996 64 60 6
004320 4/1/1996 60
004320 4/2/1996 60
004320 4/2/1996 60
004320 4/11/1996 62.6

Can someone help.
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
Dear Al:

I'm not sure what you mean "link the queries." It was intended that the
second query be self contained and complete as written.

Start with what I called Query1. Does this have the same number of rows as
tblSteroids? Does it show the proper date from tblVitalSigns?

And again, is the key PatientID/DateOfExam unique in tblVitalSigns?

Tom Ellison


Al said:
Hi Tom,
Thanks for the help. This method got close but not quite. when you link
the
queries (Query1 and the second query) it becomes a many to many
relationship.
I will eventually make a report to calculate total dosages and this
relationship created many duplicates. I am going to follow up with you
later
today.
thanks
Al

Tom Ellison said:
Dear Al:

I see that the BodyWt values all come from tblVitalSigns, while the
DailyDose and MedicationFrequancy come only form tblSteroids. The
solution
may be easier without the UNION query.

In order to form the type of JOIN I have in mind, we need to find the
DateOfTreatment from tblSteroids for each row in tblVitalSigns. That
date
will be the same date, or the most recent previous date. I believe that
would be:

SELECT PatientID, DateOfExam, st_MedicationVol, st_MedicationFrequancy,
(SELECT MAX(DateOfExam) FROM tblVitalSigns V
WHERE V.PatientID = S.PatientID AND V.DateOfExam < S.DateOfExam) AS
LDate
FROM tblSteroids S

Does this query correctly return the date for the VitalSigns exam to be
associated with each Steroids record?

I am concerned there may be missing dates. This could reflect a
condition
where there are rows for a patient in tblSteroids for which there is no
previously dated row in tblVitalSigns. According to your instructions,
as I
understand them, this would be expected if that were to happen.

I will refer to this as Query1, assuming you will save that query.

You can then JOIN the tblSteroids to Query1 to get everything you need.

SELECT Q.PatientID, Q.st_DateofExam, Q.st_MedicationVol,
Q.st_MedicationFrequancy,
V.DateOfExam, V.BodyWt
FROM Query1 Q
INNER JOIN tblVitalSigns V
ON V.PatientID = Q.PatientID AND V.DateOfExam = Q.LDate

After you get it working, add to this query your forms based filters and
the
sorting.

Does this begin to produce what you wanted? Please let me know if this
helped, and if you need any other assistance.

Tom Ellison


Al said:
Sorry forgot to paste the sql. here it is:
*************************************
SELECT [PatientID],[st_DateofTreatment] as [EventDate],"" as
[BodyWt],[st_MedicationVol] as [DailyDose(ml)],[st_MedicationFrequancy]
as
[Frequancy]
FROM tblSteroids
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[st_DateofTreatment]>= [Forms]![frmSteroidsMain]![ProtocolStartDate]
and
[st_DateofTreatment]<[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
UNION ALL SELECT [PatientID],[DateofExam] as [EventDate],[BodyWt],""
as
[DailyDose(ml)],"" as [Frequancy]
FROM tblVitalSigns
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[DateofExam]>=
[Forms]![frmSteroidsMain]![ProtocolStartDate] and [DateofExam]<
[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
ORDER BY [EventDate];
*********************************************

:

Dear Al:

I'll assume the "missing" body weight values are null and that this
column
is numeric.

There would be a good way of doing this, except that your data is
ambiguous.
You have a weight of 64 that has been entered for the missing weight
dated
4/1/1996. Why couldn't the missing weight be filled by the 60 value
of
that
same date? In reality, this might be more realistically accurate.
Since
the data you show does not allow me to see any unique ordering of the
rows,
so it is hard to see why the 6th row and 7th row could not trade
places.

In writing a query to do this, such questions are critical.

In order to retrieve any missing BodyWt values, you would need to use
a
subquery. If you will post the SQL text of the query you have for the
sample you showed, I'll try to add the missing portion. If you don't
answer
the questions I asked above, I suppose I would just arbitrarily choose
one
method out of many possible methods, all of which potentially produce
different results.

Another question would be whether you could ever have two rows with
the
same
weight on the same date.

Finally, I'd like to suggest you could prorate the BodyWt between the
weights given. In the sample data, the weight would decrease by 4
over
the
period of 6 days between the first two measurements, being about .7
per
day.
This would be more realistic I expect, but you must then prohibit any
possibility of having 2 weights on the same date.

Tom Ellison


I have the following union query:
PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 20 6
004320 3/28/1996 30 6
004320 3/30/1996 40 6
004320 3/31/1996 50 6
004320 4/1/1996 60 6
004320 4/1/1996 60
004320 4/2/1996
004320 4/2/1996
004320 4/11/1996 62.6
I need to accomplish the following:
I need to carry on the bodywt to fill in the blank records to look
like
the
following:

PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 64 20 6
004320 3/28/1996 64 30 6
004320 3/30/1996 64 40 6
004320 3/31/1996 64 50 6
004320 4/1/1996 64 60 6
004320 4/1/1996 60
004320 4/2/1996 60
004320 4/2/1996 60
004320 4/11/1996 62.6

Can someone help.
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
Dear Tom,
I meant linking the tblSteroid with query 1. However, I realized the mistake
I was making. the st_DateofExam in the 2nd query should have been
st_DateofTreatment. Now It works very nicely. thank you very much Tom you
were a great help.
Al

Tom Ellison said:
Dear Al:

I'm not sure what you mean "link the queries." It was intended that the
second query be self contained and complete as written.

Start with what I called Query1. Does this have the same number of rows as
tblSteroids? Does it show the proper date from tblVitalSigns?

And again, is the key PatientID/DateOfExam unique in tblVitalSigns?

Tom Ellison


Al said:
Hi Tom,
Thanks for the help. This method got close but not quite. when you link
the
queries (Query1 and the second query) it becomes a many to many
relationship.
I will eventually make a report to calculate total dosages and this
relationship created many duplicates. I am going to follow up with you
later
today.
thanks
Al

Tom Ellison said:
Dear Al:

I see that the BodyWt values all come from tblVitalSigns, while the
DailyDose and MedicationFrequancy come only form tblSteroids. The
solution
may be easier without the UNION query.

In order to form the type of JOIN I have in mind, we need to find the
DateOfTreatment from tblSteroids for each row in tblVitalSigns. That
date
will be the same date, or the most recent previous date. I believe that
would be:

SELECT PatientID, DateOfExam, st_MedicationVol, st_MedicationFrequancy,
(SELECT MAX(DateOfExam) FROM tblVitalSigns V
WHERE V.PatientID = S.PatientID AND V.DateOfExam < S.DateOfExam) AS
LDate
FROM tblSteroids S

Does this query correctly return the date for the VitalSigns exam to be
associated with each Steroids record?

I am concerned there may be missing dates. This could reflect a
condition
where there are rows for a patient in tblSteroids for which there is no
previously dated row in tblVitalSigns. According to your instructions,
as I
understand them, this would be expected if that were to happen.

I will refer to this as Query1, assuming you will save that query.

You can then JOIN the tblSteroids to Query1 to get everything you need.

SELECT Q.PatientID, Q.st_DateofExam, Q.st_MedicationVol,
Q.st_MedicationFrequancy,
V.DateOfExam, V.BodyWt
FROM Query1 Q
INNER JOIN tblVitalSigns V
ON V.PatientID = Q.PatientID AND V.DateOfExam = Q.LDate

After you get it working, add to this query your forms based filters and
the
sorting.

Does this begin to produce what you wanted? Please let me know if this
helped, and if you need any other assistance.

Tom Ellison


Sorry forgot to paste the sql. here it is:
*************************************
SELECT [PatientID],[st_DateofTreatment] as [EventDate],"" as
[BodyWt],[st_MedicationVol] as [DailyDose(ml)],[st_MedicationFrequancy]
as
[Frequancy]
FROM tblSteroids
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[st_DateofTreatment]>= [Forms]![frmSteroidsMain]![ProtocolStartDate]
and
[st_DateofTreatment]<[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
UNION ALL SELECT [PatientID],[DateofExam] as [EventDate],[BodyWt],""
as
[DailyDose(ml)],"" as [Frequancy]
FROM tblVitalSigns
where PatientID = [Forms]![frmSteroidsMain]![PatientID] and
[DateofExam]>=
[Forms]![frmSteroidsMain]![ProtocolStartDate] and [DateofExam]<
[Forms]![frmSteroidsMain]![ProtocolWithdrawlDate]
ORDER BY [EventDate];
*********************************************

:

Dear Al:

I'll assume the "missing" body weight values are null and that this
column
is numeric.

There would be a good way of doing this, except that your data is
ambiguous.
You have a weight of 64 that has been entered for the missing weight
dated
4/1/1996. Why couldn't the missing weight be filled by the 60 value
of
that
same date? In reality, this might be more realistically accurate.
Since
the data you show does not allow me to see any unique ordering of the
rows,
so it is hard to see why the 6th row and 7th row could not trade
places.

In writing a query to do this, such questions are critical.

In order to retrieve any missing BodyWt values, you would need to use
a
subquery. If you will post the SQL text of the query you have for the
sample you showed, I'll try to add the missing portion. If you don't
answer
the questions I asked above, I suppose I would just arbitrarily choose
one
method out of many possible methods, all of which potentially produce
different results.

Another question would be whether you could ever have two rows with
the
same
weight on the same date.

Finally, I'd like to suggest you could prorate the BodyWt between the
weights given. In the sample data, the weight would decrease by 4
over
the
period of 6 days between the first two measurements, being about .7
per
day.
This would be more realistic I expect, but you must then prohibit any
possibility of having 2 weights on the same date.

Tom Ellison


I have the following union query:
PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 20 6
004320 3/28/1996 30 6
004320 3/30/1996 40 6
004320 3/31/1996 50 6
004320 4/1/1996 60 6
004320 4/1/1996 60
004320 4/2/1996
004320 4/2/1996
004320 4/11/1996 62.6
I need to accomplish the following:
I need to carry on the bodywt to fill in the blank records to look
like
the
following:

PatientID EventDate BodyWt DailyDose(ml) Frequancy
004320 3/26/1996 64
004320 3/27/1996 64 20 6
004320 3/28/1996 64 30 6
004320 3/30/1996 64 40 6
004320 3/31/1996 64 50 6
004320 4/1/1996 64 60 6
004320 4/1/1996 60
004320 4/2/1996 60
004320 4/2/1996 60
004320 4/11/1996 62.6

Can someone help.
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help with duplicates 1
Please help with extracting records 2
Help with carry over a value 7
Union query help 2
Help with Query (or Macro??) 3
League age chart 4
A big problem for me to synchronizate the data in excel 1
Totalling Formulas 10

Back
Top