Last Record

[Keith]

Is it possible to find the value of a field in the previous record entered?
I have a database which I update, but the value of one field is dependant on
the previous record.

So if Field 1 of Record1 is 7, then Field 1 of the next record could be 8 or
0 depending on the value of field 2, if field 2 of record 2 is a 0 then
Field 1 becomes 0, however if Field 2 is 1 then Field 1 of record 2 would be
8

Field 1 Field 2
1 7
1 8
1 9
0 0
0 0
1 1
1 2
1 3

etc
Hope this makes sense and someone can help?

Thanks

 

[John]

You need to write a procedure in the forms OnCurrent event. You need to use
the RecordsetClone property to create a clone of the form record set and
then use GoToRecord method to get the value in the previous record for use
in the control

John

 

[John Vinson]

Is it possible to find the value of a field in the previous record entered?
I have a database which I update, but the value of one field is dependant on
the previous record.

Then your table is not in normal form. A properly normalized Table's
fields depend ONLY on that record's primary key, not on any other
record. There is no such thing as a "previous record" or "last
record", any more than there is a "previous potato" or "last potato"
in a wheelbarrow full of potatoes.

So if Field 1 of Record1 is 7, then Field 1 of the next record could be 8 or
0 depending on the value of field 2, if field 2 of record 2 is a 0 then
Field 1 becomes 0, however if Field 2 is 1 then Field 1 of record 2 would be
8

Field 1 Field 2
1 7
1 8
1 9
0 0
0 0
1 1
1 2
1 3

etc
Hope this makes sense and someone can help?

I'm sorry, no, it doesn't make sense at all. I can't see how you
decide what's a vlid value for Field2 - it must be greater than some
other record's value of Field2 if Field1 is zero, and must be zero if
field1 is zero?

What's the REAL LIFE entity being modeled by this table?

John W. Vinson[MVP]

 

[Vincent Johns]

Then your table is not in normal form. A properly normalized Table's
fields depend ONLY on that record's primary key, not on any other
record. There is no such thing as a "previous record" or "last
record", any more than there is a "previous potato" or "last potato"
in a wheelbarrow full of potatoes.




I'm sorry, no, it doesn't make sense at all. I can't see how you
decide what's a vlid value for Field2 - it must be greater than some
other record's value of Field2 if Field1 is zero, and must be zero if
field1 is zero?

What's the REAL LIFE entity being modeled by this table?

John W. Vinson[MVP]

It didn't make lots of sense to me, either, but I did find a way to do
what I think you asked about.

Note: Your description and your example didn't match very well -- you
talked about a record in which [Field 1] = 7, but the only values you
showed in the example for [Field 1] were 0 or 1.

By "previous" you may have meant the record one earlier in the current
sorting order, or the record that was most recently changed or added. In
either case, you can set up a sorting order by including an indexed
field, and in my following example that field is
[tblStudents].[WhenAdded], representing some date (I'm assuming no
duplicate dates here) by which the list of students can be ordered.

So my Table, called [tblStudents], contains the following records:

tblStudentsID Name WhenAdded
------------- ----- ---------
2 Jane 9/2/2005
4 Marv 9/6/2005
7 Greg 9/7/2005
8 Sally 9/1/2005

I define a query [Q_PrevStudentDate] as...

SELECT tblStudents.tblStudentsID, Max(tblStudents_1.WhenAdded)
AS MaxOfWhenAdded
FROM tblStudents, tblStudents AS tblStudents_1
WHERE (((tblStudents_1.WhenAdded)<[tblStudents].[WhenAdded]))
GROUP BY tblStudents.tblStudentsID, tblStudents.Name,
tblStudents.WhenAdded;

and when run it produces these records:

MaxOfWhenAdded tblStudentsID
-------------- -------------
9/1/2005 2
9/2/2005 4
9/6/2005 7


If [Q_tblStudents] is defined as ...

SELECT tblStudents.Name, tblStudents_1.Name AS PreviousStudent
FROM (tblStudents LEFT JOIN Q_PrevStudentDate
ON tblStudents.tblStudentsID = Q_PrevStudentDate.tblStudentsID)
LEFT JOIN tblStudents AS tblStudents_1
ON Q_PrevStudentDate.MaxOfWhenAdded = tblStudents_1.WhenAdded
ORDER BY tblStudents.Name;

then running it produces the following list, in which each student's
name is associated with the one ahead of him in line:

Name PreviousStudent
---- ---------------
Greg Marv
Jane Sally
Marv Jane
Sally

Sally, being first in the list, has no predecessor, which is why I used
a LEFT OUTER JOIN instead of an INNER JOIN in the SQL; otherwise, she
wouldn't have been listed.

Incidentally, besides wondering why you'd want to keep track of things
in this way, I also wonder why you'd want to put the results into a
Table. That would mean that updating one record could make other
records become invalid. A Query like the one I showed you here would be
immune to that kind of trouble.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Keith]

Maybe I should have made it a bit clearer:-
I have a column which records whether our sales dept hit their daily target
(1) or not (0), what I wanted to do is keep a tally of the number of
consecutive days they had HIT and MISSED their targets, so for example, if
we had HIT the target for 7 conecutive days, then if the next day's result
was a HIT, the consecutive 'HITS' would now be 8 (but also the previous day
should now change to zero as the consecutive run is still ongoing), however
if we 'MISSED' the target, then the 'MISSED' counter should now start (1, 2
etc) and the 'HIT' counter should remain on zero until we started HITTING
again.
It is important for the previous day to change to zero if the consecutive
run is ongoing, or it would give me dupliacte numbers when I run the query
to see the total consecutive counts (eg a run of 8 would also see a run of
7, 6, 5 etc) where this is not the case.

phew!

hope this makes a bit more sense?

Then your table is not in normal form. A properly normalized Table's
fields depend ONLY on that record's primary key, not on any other
record. There is no such thing as a "previous record" or "last
record", any more than there is a "previous potato" or "last potato"
in a wheelbarrow full of potatoes.




I'm sorry, no, it doesn't make sense at all. I can't see how you
decide what's a vlid value for Field2 - it must be greater than some
other record's value of Field2 if Field1 is zero, and must be zero if
field1 is zero?

What's the REAL LIFE entity being modeled by this table?

John W. Vinson[MVP]

It didn't make lots of sense to me, either, but I did find a way to do
what I think you asked about.

Note: Your description and your example didn't match very well -- you
talked about a record in which [Field 1] = 7, but the only values you
showed in the example for [Field 1] were 0 or 1.

By "previous" you may have meant the record one earlier in the current
sorting order, or the record that was most recently changed or added. In
either case, you can set up a sorting order by including an indexed field,
and in my following example that field is [tblStudents].[WhenAdded],
representing some date (I'm assuming no duplicate dates here) by which the
list of students can be ordered.

So my Table, called [tblStudents], contains the following records:

tblStudentsID Name WhenAdded
------------- ----- ---------
2 Jane 9/2/2005
4 Marv 9/6/2005
7 Greg 9/7/2005
8 Sally 9/1/2005

I define a query [Q_PrevStudentDate] as...

SELECT tblStudents.tblStudentsID, Max(tblStudents_1.WhenAdded)
AS MaxOfWhenAdded
FROM tblStudents, tblStudents AS tblStudents_1
WHERE (((tblStudents_1.WhenAdded)<[tblStudents].[WhenAdded]))
GROUP BY tblStudents.tblStudentsID, tblStudents.Name,
tblStudents.WhenAdded;

and when run it produces these records:

MaxOfWhenAdded tblStudentsID
-------------- -------------
9/1/2005 2
9/2/2005 4
9/6/2005 7


If [Q_tblStudents] is defined as ...

SELECT tblStudents.Name, tblStudents_1.Name AS PreviousStudent
FROM (tblStudents LEFT JOIN Q_PrevStudentDate
ON tblStudents.tblStudentsID = Q_PrevStudentDate.tblStudentsID)
LEFT JOIN tblStudents AS tblStudents_1
ON Q_PrevStudentDate.MaxOfWhenAdded = tblStudents_1.WhenAdded
ORDER BY tblStudents.Name;

then running it produces the following list, in which each student's name
is associated with the one ahead of him in line:

Name PreviousStudent
---- ---------------
Greg Marv
Jane Sally
Marv Jane
Sally

Sally, being first in the list, has no predecessor, which is why I used a
LEFT OUTER JOIN instead of an INNER JOIN in the SQL; otherwise, she
wouldn't have been listed.

Incidentally, besides wondering why you'd want to keep track of things in
this way, I also wonder why you'd want to put the results into a Table.
That would mean that updating one record could make other records become
invalid. A Query like the one I showed you here would be immune to that
kind of trouble.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Maybe I should have made it a bit clearer:-
I have a column which records whether our sales dept hit their daily target
(1) or not (0), what I wanted to do is keep a tally of the number of
consecutive days they had HIT and MISSED their targets, so for example, if
we had HIT the target for 7 conecutive days, then if the next day's result
was a HIT, the consecutive 'HITS' would now be 8 (but also the previous day
should now change to zero as the consecutive run is still ongoing), however
if we 'MISSED' the target, then the 'MISSED' counter should now start (1, 2
etc) and the 'HIT' counter should remain on zero until we started HITTING
again.
It is important for the previous day to change to zero if the consecutive
run is ongoing, or it would give me dupliacte numbers when I run the query
to see the total consecutive counts (eg a run of 8 would also see a run of
7, 6, 5 etc) where this is not the case.

phew!

hope this makes a bit more sense?

OK, yes it does, but I *still* wouldn't want to record stuff like that
in a Table, as it can be computed. (Sorry, I don't have time to do that
right now, maybe tomorrow if nobody else does.) Does the example I gave
you help at all? In Query Design View, you open the group functions via
the little capital sigma on the toolbar. Good luck.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

[...]

By "previous" you may have meant the record one earlier in the current
sorting order, or the record that was most recently changed or added. In
either case, you can set up a sorting order by including an indexed field,
and in my following example that field is [tblStudents].[WhenAdded],
representing some date (I'm assuming no duplicate dates here) by which the
list of students can be ordered.

So my Table, called [tblStudents], contains the following records:

tblStudentsID Name WhenAdded
------------- ----- ---------
2 Jane 9/2/2005
4 Marv 9/6/2005
7 Greg 9/7/2005
8 Sally 9/1/2005

I define a query [Q_PrevStudentDate] as...

SELECT tblStudents.tblStudentsID, Max(tblStudents_1.WhenAdded)
AS MaxOfWhenAdded
FROM tblStudents, tblStudents AS tblStudents_1
WHERE (((tblStudents_1.WhenAdded)<[tblStudents].[WhenAdded]))
GROUP BY tblStudents.tblStudentsID, tblStudents.Name,
tblStudents.WhenAdded;

and when run it produces these records:

MaxOfWhenAdded tblStudentsID
-------------- -------------
9/1/2005 2
9/2/2005 4
9/6/2005 7


If [Q_tblStudents] is defined as ...

SELECT tblStudents.Name, tblStudents_1.Name AS PreviousStudent
FROM (tblStudents LEFT JOIN Q_PrevStudentDate
ON tblStudents.tblStudentsID = Q_PrevStudentDate.tblStudentsID)
LEFT JOIN tblStudents AS tblStudents_1
ON Q_PrevStudentDate.MaxOfWhenAdded = tblStudents_1.WhenAdded
ORDER BY tblStudents.Name;

then running it produces the following list, in which each student's name
is associated with the one ahead of him in line:

Name PreviousStudent
---- ---------------
Greg Marv
Jane Sally
Marv Jane
Sally

Sally, being first in the list, has no predecessor, which is why I used a
LEFT OUTER JOIN instead of an INNER JOIN in the SQL; otherwise, she
wouldn't have been listed.

Incidentally, besides wondering why you'd want to keep track of things in
this way, I also wonder why you'd want to put the results into a Table.
That would mean that updating one record could make other records become
invalid. A Query like the one I showed you here would be immune to that
kind of trouble.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.