This is I expected. The problem here is that a Union query does not combine
the data from the two tables. It creates an individual row for each row in
each table.
Create an Append query, depending on your needs. that has these columns:
[PatientID] [TreamentmentDate] [BodyWt] [Dose]
For the BodyWt and Dose columns, you will need a Calculated field that will
call these functions which will return the previous value until a new value
is encountered. The functions go in a standard module
The Feild row in the query builder would look like this:
BdyWt: CarryOverWt([BodyWt]) and TheDose: CarryOverDose([Dose])
Public Function CarryOverWt(varNewWt as Variant) as Variant
Static varOldWt as Variant
If Not IsNull(varNewWt) Then
varOldWt = varNewWt
End If
CarryOverWt = varOldWt
End Function
Public Function CarryOverDose(varNewDose as Variant) as Variant
Static varOldDose as Variant
If Not IsNull(varNewDose) Then
varOldDose = varNewDose
End If
CarryOverDose = varOldDose
End Function
Al said:
Here it is,
SELECT PatientID, BodyWt, "" As [Dose], DateofExam from tblVitalSigns
UNION ALL select PatientID, "" As [BodyWt], Dose, DateofTreatment from
tblMedication
ORDER BY DateofExam;
Al
:
The problem is how you have the query constructed. Could you please post the
SQL for the union query and I will take a look at it.
:
Data is entered via a forms in 2 different tables. Date and Dose in one table
called tblMedication and Date and BodyWT is entered in another table called
tblVitalSigns. each table has its own form for data entry. what I have here
is the data combined using union query and then sent into a new table called
tblDosage. This new table (tblDosage) will be put behind a report. I hope
that this clarify your question.
Al
:
Al,
You need to provide more information to get an answer. How are you entering
the data in the records? with a form, a query, ?
:
I have a table with the following fields:
Treatmentdate BodyWt Dose
5/16/1996 92.75 130
5/17/1996 140
5/20/1996 140
5/28/1996 140
6/19/1996 110 140
6/25/1996 140
6/26/1996
6/29/1996 110
7/17/1996 113
I am having trouble carrying over the missing values in the body wt and
dose. What I need to do in the above example is as follow:
BodyWt value (92.75) needs to be carried over from date (5/16/1996) till
date (5/28/1996) and value (110) needs to be carried over from date
(6/19/1996) till date (6/26/1996).
Dose Value (140) needs to be carried over from date (6/25/1996) till
(7/17/1996). Any Ideas
Thanks
