Count and record query records

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

Guest

I wish to put a counter field in a query so i can compare the current records
FieldX with the previous records FieldX and have the resulting difference
calculated in FieldY. I cannot use the underlying tables ID field as the
query filters the table and the ID field is not then in sequence.
 
John,

In that case, what is the meaning of "the previous record". You will
have to have a data-based way of identifying which record is the
previous one, for your purposes. Maybe it will help if you can give
some specific examples of the data returned by your query, and what you
want to achieve.
 
Thanks Steve for replying,
I have a table for recording Patients weights each month. The table holds
many different Patients weights and is connected to Patient Details by an ID
Field. The query involved pulls a single Patients weight records from the
table. Therefore all the weight records retrieved in the query have the same
PatientID. What i want to do is compare each weight record with the previous
months weight record for that Patient in the query and have another Field in
the query record the difference. The Counter Field i was refering to was a
way of refering to each record in the query, for identification, to calculate
the differences. I have some code to actually calculate the differences but i
need the index (Counter Field) to correctly identify the previous record in
the query itself. The difference in weight results dont need to be kept, just
made available for forms and reports.
I hope this explains things a bit better. I am only a novice at this.
John
 
John,

So, presumably the records in your query have a date when the wieght was
recorded. Am I right? And when we're talking "previous record", it
would be on the basis of the date, right?

Can you go to the design view of the query, and select SQL from the View
menu, and then copy/paste the SQL view of the query into your reply?
Thanks, that will help show the details of your data.
 
Thanks again Steve,
Here is the code. The section from DlookUp is the code that is supposed to
look back at the last record. If i replace the PatientID field with the
ObsDate field should that work. Instead of ObsDate (-1) the code might have
to reflect an earlier date. Also, the DLookUp section would have to refer to
the query as there will be also many Patients with the same entry date in the
tblPatientWeights. That is why i thought of using a counter field in the
query so i could look back one record in the query itself. All queries and
tables must have a property somewhere to keep track of the record numbers as
shown at the bottom, is it possible to access that?

SELECT tblPatientWeights.PatientID, tblPatientWeights.ObsDate,
tblPatientWeights.Weight, tblPatientWeights.Temp,
tblPatientWeights.HeartRate, tblPatientWeights.Bp, tblPatientWeights.RR,
DLookUp("[Weight]","tblPatientWeights","[PatientID] = " & [PatientID]-1) AS
PrevWeight
FROM tblPatientWeights
ORDER BY tblPatientWeights.PatientID;

John
 
John,

I don't understand. You said "all the weight records retrieved in the
query have the same PatientID". So does that mean that all the data in
tblPatientWeights is for the same patient? And if so, why the "ORDER BY
PatientID" clause in the query?

So, assuming I understand what you want:
You can do this sort of thing with a SubQuery. Without testing, I think
something like this will work...

SELECT tblPatientWeights.PatientID, tblPatientWeights.ObsDate,
tblPatientWeights.Weight, blPatientWeights.Temp,
tblPatientWeights.HeartRate, tblPatientWeights.Bp, tblPatientWeights.RR,
(SELECT TOP 1 [Weight] FROM tblPatientWeights WHERE
[PatientID]=tblPatientWeights.[PatientID] And
[ObsDate]<tblPatientWeights.[ObsDate] ORDER BY [ObsDate] DESC) AS PrevWeight
FROM tblPatientWeights

If you have a lot of records in the query, though, this is likely to be
slow. In which case a User-Defined Function would be better. In a
standard module, it will be something like this...

Public Function FindPrev(MyPatient As Long, ThisObs As Long) As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT Weight FROM
tblPatientWeights WHERE PatientID = " & MyPatient & " And ObsDate < " &
ThisObs & " ORDER BY ObsDate DESC")
FindPrev = rst!Weight
rst.Close
Set rst = Nothing
End Function

Then in your query you can do like this...
SELECT tblPatientWeights.PatientID, tblPatientWeights.ObsDate,
tblPatientWeights.Weight, blPatientWeights.Temp,
tblPatientWeights.HeartRate, tblPatientWeights.Bp, tblPatientWeights.RR,
FindPrev([PatientID],CLng([ObsDate])) AS PrevWeight
FROM tblPatientWeights
 
Thanks again Steve,
Sorry, i omitted that the forms and reports i derive from the query are
subforms and subreports, therefore the PatientID has been brought back to the
records of one patient only for the subforms/reports on the main Patient form
i am using. You are right about the Order by PatientID. i didnt realize that
was still there from previous experiments, and it should be Order by ObsDate
anyway for what i want.
I think both examples look good but i would probably go with the function
solution. Not being into SQL do i just type in the extra's in the SQL view?
Also the SELECT TOP 1 i havent come across before, what does it actually do?
Thanking you for your help again,
John

Steve Schapel said:
John,

I don't understand. You said "all the weight records retrieved in the
query have the same PatientID". So does that mean that all the data in
tblPatientWeights is for the same patient? And if so, why the "ORDER BY
PatientID" clause in the query?

So, assuming I understand what you want:
You can do this sort of thing with a SubQuery. Without testing, I think
something like this will work...

SELECT tblPatientWeights.PatientID, tblPatientWeights.ObsDate,
tblPatientWeights.Weight, blPatientWeights.Temp,
tblPatientWeights.HeartRate, tblPatientWeights.Bp, tblPatientWeights.RR,
(SELECT TOP 1 [Weight] FROM tblPatientWeights WHERE
[PatientID]=tblPatientWeights.[PatientID] And
[ObsDate]<tblPatientWeights.[ObsDate] ORDER BY [ObsDate] DESC) AS PrevWeight
FROM tblPatientWeights

If you have a lot of records in the query, though, this is likely to be
slow. In which case a User-Defined Function would be better. In a
standard module, it will be something like this...

Public Function FindPrev(MyPatient As Long, ThisObs As Long) As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT Weight FROM
tblPatientWeights WHERE PatientID = " & MyPatient & " And ObsDate < " &
ThisObs & " ORDER BY ObsDate DESC")
FindPrev = rst!Weight
rst.Close
Set rst = Nothing
End Function

Then in your query you can do like this...
SELECT tblPatientWeights.PatientID, tblPatientWeights.ObsDate,
tblPatientWeights.Weight, blPatientWeights.Temp,
tblPatientWeights.HeartRate, tblPatientWeights.Bp, tblPatientWeights.RR,
FindPrev([PatientID],CLng([ObsDate])) AS PrevWeight
FROM tblPatientWeights

--
Steve Schapel, Microsoft Access MVP
Thanks again Steve,
Here is the code. The section from DlookUp is the code that is supposed to
look back at the last record. If i replace the PatientID field with the
ObsDate field should that work. Instead of ObsDate (-1) the code might have
to reflect an earlier date. Also, the DLookUp section would have to refer to
the query as there will be also many Patients with the same entry date in the
tblPatientWeights. That is why i thought of using a counter field in the
query so i could look back one record in the query itself. All queries and
tables must have a property somewhere to keep track of the record numbers as
shown at the bottom, is it possible to access that?

SELECT tblPatientWeights.PatientID, tblPatientWeights.ObsDate,
tblPatientWeights.Weight, tblPatientWeights.Temp,
tblPatientWeights.HeartRate, tblPatientWeights.Bp, tblPatientWeights.RR,
DLookUp("[Weight]","tblPatientWeights","[PatientID] = " & [PatientID]-1) AS
PrevWeight
FROM tblPatientWeights
ORDER BY tblPatientWeights.PatientID;
Click to expand...
Click to expand...
 
John,

If you look at a query in design view, assuming the standard query
design toolbar has not been disabled, you will see a little combobox,
and the "tooltip" if you hover your mouse over it will say "top values".
You can select one of the items from the list of selections, or enter
anything you like that is not on the list. If you select 1, and if the
query is sorted descending on a field, then you will get the record that
contains the highest value in that sorting field.

You don't have to type into the SQL window if you don't want to. You
can do it in design view. For example, if you make the function I
suggested, then in design view of the query, you would just replace your
existing...
PrevWeight: DLookUp("[Weight]","tblPatientWeights","[PatientID] = " &
[PatientID]-1)
.... with...
PrevWeight: FindPrev([PatientID],CLng([ObsDate]))
 
Hi Steve,
hope you might be still watching this thread. I still am in bother. I tried
both your examples and couldnt get either to work for me. The first one didnt
have anything in the field PrevWeight: when the query ran. I might be a bit
slow but i dont quite get how this one works. It seems to me that it is
picking the heaviest weight in the individual patients records. Please tell
me if i am wrong with that thought. What i want in the end is to display the
difference (be it - or +) in a patients weight from one measurement to the
next. The Function got stuck at the line
Dim rst As DAO.Recordset
with 'rst As DAO.Recordset' highlighted in the debugger and the error
message "user defined type not defined" or something similar. Im not sure
what the go there is. I have to then delete the function out of the Public
Declarations module then before i can do anything else. Any help here would
be much appreciated.
Going back to another point i mentioned in an earlier post about the row
number generated in the query/table by the system. When you open either and
you click on a record, in the navigation buttons down the bottom it shows
what record you are on and how many in the query/table. Is it not possible to
get access to that through a function/expression and actually store that in a
field in your query?
My original line of thinking was if i had a counter, (FieldX), in the
Query, then used the DLookUp to get the previous record(in
query),(FieldY),Weight, then do a simple calculation using the current
records (in query) Weight, then the result end up in (FieldZ). I would only
show FieldZ, out of these 3 in the forms/reports.
Having said all that i cant see why the function wouldnt do the job but it
wont compile as i mentioned before.
Thanks very much for your help so far and hope you catch this thread
John




Steve Schapel said:
John,

If you look at a query in design view, assuming the standard query
design toolbar has not been disabled, you will see a little combobox,
and the "tooltip" if you hover your mouse over it will say "top values".
You can select one of the items from the list of selections, or enter
anything you like that is not on the list. If you select 1, and if the
query is sorted descending on a field, then you will get the record that
contains the highest value in that sorting field.

You don't have to type into the SQL window if you don't want to. You
can do it in design view. For example, if you make the function I
suggested, then in design view of the query, you would just replace your
existing...
PrevWeight: DLookUp("[Weight]","tblPatientWeights","[PatientID] = " &
[PatientID]-1)
.... with...
PrevWeight: FindPrev([PatientID],CLng([ObsDate]))

--
Steve Schapel, Microsoft Access MVP
Thanks again Steve,
Sorry, i omitted that the forms and reports i derive from the query are
subforms and subreports, therefore the PatientID has been brought back to the
records of one patient only for the subforms/reports on the main Patient form
i am using. You are right about the Order by PatientID. i didnt realize that
was still there from previous experiments, and it should be Order by ObsDate
anyway for what i want.
I think both examples look good but i would probably go with the function
solution. Not being into SQL do i just type in the extra's in the SQL view?
Also the SELECT TOP 1 i havent come across before, what does it actually do?
Thanking you for your help again,
John
Click to expand...
Click to expand...
 
John,

Please forget the 'Counter' idea. While it is theoretically possible to
pursue this idea, implementing it is much uglier and more difficult than
what we are trying so far.

I am guessing that you are using Access 2000. Go to the VB Editor
window (any code module), select 'References' from the Tools menu, and
then, find 'Microsoft DAO 3.6 Object Library' and put a tick in the box.
The function should now work (assuming I am right about the cause of
the problem!).

No, the subquery expression should not result in the heaviest weight in
the patient's records. It should return the weight from the most recent
ObsDate. Here is the subquery expression I gave you...
SELECT TOP 1 [Weight] FROM tblPatientWeights WHERE
[PatientID]=tblPatientWeights.[PatientID] And
[ObsDate]<tblPatientWeights.[ObsDate] ORDER BY [ObsDate] DESC
Translated into English is something like this...
Find all the records for this patient, with ObsDates less than the
current record, with the most recent ObsDate at the top. Take the top
record (i.e. most recent), and show me the Weight.
At this stage, I don't know why you are not getting anything returned.
 
Back again Steve,
we are winning though. The function now works, thankyou, but now i am
getting a VB error 'no current record'. If i press end in debug window it
will then fill the rest of the fields correctly, except occassionly it will
throw errors in 1 or 2 more records for some reason, which i put down to the
first error throwing a spanner in the works. I know, guess, that the first
record in the query it looks back for doesnt exist. I tried putting an 'If
Then' statement around the 'FindPrev = rst!Weight' line with >0 expression
but no go. also tried the NZ in front of Weight in the select statement but
all to no avail. Not sure what to try next. Have a feeling it has to do with
getting a Null return on that first call.
Thank you
John

Steve Schapel said:
John,

Please forget the 'Counter' idea. While it is theoretically possible to
pursue this idea, implementing it is much uglier and more difficult than
what we are trying so far.

I am guessing that you are using Access 2000. Go to the VB Editor
window (any code module), select 'References' from the Tools menu, and
then, find 'Microsoft DAO 3.6 Object Library' and put a tick in the box.
The function should now work (assuming I am right about the cause of
the problem!).

No, the subquery expression should not result in the heaviest weight in
the patient's records. It should return the weight from the most recent
ObsDate. Here is the subquery expression I gave you...
SELECT TOP 1 [Weight] FROM tblPatientWeights WHERE
[PatientID]=tblPatientWeights.[PatientID] And
[ObsDate]<tblPatientWeights.[ObsDate] ORDER BY [ObsDate] DESC
Translated into English is something like this...
Find all the records for this patient, with ObsDates less than the
current record, with the most recent ObsDate at the top. Take the top
record (i.e. most recent), and show me the Weight.
At this stage, I don't know why you are not getting anything returned.

--
Steve Schapel, Microsoft Access MVP
Hi Steve,
hope you might be still watching this thread. I still am in bother. I tried
both your examples and couldnt get either to work for me. The first one didnt
have anything in the field PrevWeight: when the query ran. I might be a bit
slow but i dont quite get how this one works. It seems to me that it is
picking the heaviest weight in the individual patients records. Please tell
me if i am wrong with that thought. What i want in the end is to display the
difference (be it - or +) in a patients weight from one measurement to the
next. The Function got stuck at the line
Dim rst As DAO.Recordset
with 'rst As DAO.Recordset' highlighted in the debugger and the error
message "user defined type not defined" or something similar. Im not sure
what the go there is. I have to then delete the function out of the Public
Declarations module then before i can do anything else. Any help here would
be much appreciated.
Going back to another point i mentioned in an earlier post about the row
number generated in the query/table by the system. When you open either and
you click on a record, in the navigation buttons down the bottom it shows
what record you are on and how many in the query/table. Is it not possible to
get access to that through a function/expression and actually store that in a
field in your query?
My original line of thinking was if i had a counter, (FieldX), in the
Query, then used the DLookUp to get the previous record(in
query),(FieldY),Weight, then do a simple calculation using the current
records (in query) Weight, then the result end up in (FieldZ). I would only
show FieldZ, out of these 3 in the forms/reports.
Having said all that i cant see why the function wouldnt do the job but it
wont compile as i mentioned before.
Thanks very much for your help so far and hope you catch this thread
John
Click to expand...
Click to expand...
 
John,

I feel your diagnosis is correct, and my apologies for failing to cover
off this possibility.

Easiest way, I suppose, is to change the function like this...

Public Function FindPrev(MyPatient As Long, ThisObs As Long) As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT Weight FROM
tblPatientWeights WHERE PatientID = " & MyPatient & " And ObsDate < " &
ThisObs & " ORDER BY ObsDate DESC")
With rst
If .RecordCount Then
FindPrev = !Weight
Else
FindPrev = 0
End If
.Close
End With
Set rst = Nothing
End Function

This should result in the PrevWeight in your query coming out as 0 in
the case of the first record for the given patient, which I suppose
would be ok for your purposes?
 
Back
Top