Previous record

[Dan Johnson]

Hello,

I have a rather lengthy question in regard to updating a field based on the Previous record's input. We have electric vehicles that utilize batteries for their operation. There are 13 vehicles with approximately 2 battery changes per day in a group of 28 batteries. As an operator changes the battery in the vehicle, I would like to automatically input that current battery into the now Last battery field within a form for the data entry.

I have been able to get two queries written to isolate down to the current record. Based on the form's current record, the first query filters to ALL of that specific vehicle's records. The second query isolates down to the ID (AutoNumber) of the current vehicle. This is the point at which I am stumped

I haven't been able to figure out how to go to the Previous Battery for that record (vehicle battery change) in an effort to do a later copy via macro.

I currently have the form updating with the Last Battery but that doesn't work because the Form needs to have the ability to make corrections on older records. Since one of the older records happened, in some cases, a month before the Last Battery was installed, it actually needs to be that record's Previous record.

Unfortunately, I am not proficient in VB and am limited to the wizards for queries.

I apologize for this lengthy request. Thanks, in advance for any suggestions for this problem.

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a rather lengthy question in regard to updating a field based on the
Previous record's input. We have electric vehicles that utilize batteries
for their operation. There are 13 vehicles with approximately 2 battery
changes per day in a group of 28 batteries. As an operator changes the
battery in the vehicle, I would like to automatically input that current
battery into the now Last battery field within a form for the data entry.

I have been able to get two queries written to isolate down to the current
record. Based on the form's current record, the first query filters to ALL
of that specific vehicle's records. The second query isolates down to the ID
(AutoNumber) of the current vehicle. This is the point at which I am stumped

I haven't been able to figure out how to go to the Previous Battery for that
record (vehicle battery change) in an effort to do a later copy via macro.

I currently have the form updating with the Last Battery but that doesn't
work because the Form needs to have the ability to make corrections on older
records. Since one of the older records happened, in some cases, a month
before the Last Battery was installed, it actually needs to be that record's
Previous record.

Unfortunately, I am not proficient in VB and am limited to the wizards for
queries.

I apologize for this lengthy request. Thanks, in advance for any suggestions
for this problem.

 

[Dan Johnson]

Allen et al,

This is not exactly what I am looking for, I don't believe or I may need
more of the syntax for the query portion of this record. The end result was
the previous record of the Form's current record. I need the previous record
of the resultant query (not the table) because the previous record in the
Table is another vehicle. I need the previous record of the Form's current
vehicle's battery.

This is extremely difficult where I am headed with this form. I apologize
for being so vague. Perhaps the following will help make it clearer:

ID Vehicle Battery Last Battery

101 002 028 006
102 005 005 010
103 007 013 025
104 002 004 (this need to auto update
to 006 from 101 above) Current form record

Thanks for any additional help.

 

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

 

[Allen Browne]

Okay, you are on the right track by having LastBattery as a calculated
field, i.e. it is not stored in the table, but calculated as needed, so that
if another record is inserted it, will update automaticallly.

The piece of the puzzle that looks to be missing is a date/time field that
stores the date and time of the battery change. This lets you add another
record later (which probably gets a higher ID value, but still specify that
the record fits between 2 others based on the date/time.

If that field is named "ChangeDateTime", you could create a query and type
something like this into the Field row to get the calculated field named
"LastBattery":
LastBattery: ELookup("Battery", "MyTable", "([Vehicle] = """ & [Vehicle] &
") AND ([ChangeDateTime] < " & Format([ChangeDateTime], "\#mm\/dd\/yyyy\#")
& ")", "[ChangeDateTime] DESC")

Note that the requirement to order records differently than the ID field
means DLookup() is not up to the task, and you need the ELookup() from this
link:
http://allenbrowne.com/ser-42.html
Replace "MyTable" with the name of your table.

Another approach would be to use a subquery. This will be way faster, but
will give read-only results. The expression to type into your quey will be
something like this:

LastBattery: (SELECT TOP 1 Battery FROM MyTable AS Dupe WHERE (Dupe.Vehicle
= MyTable.Vehicle) AND (Dupe.ChangeDateTime < MyTable.ChangeDateTime) ORDER
BY Dupe.ChangeDateTime, Dupe.ID)

 

[Dan Johnson]

Allen,

I already have another query that orders the list of vehicles down to the
current record's vehicle. such that the query results are as follows based
on the Table data in the original post:

ID Vehicle Battery Last Battery
101 002 028 006
104 002 004

Having said that, I need the Last Battery field populated with Battery 028
from the battery that should be in the vehicle currently. Am I trying to
make this too difficult?

--
Dan Johnson

Okay, you are on the right track by having LastBattery as a calculated
field, i.e. it is not stored in the table, but calculated as needed, so
that if another record is inserted it, will update automaticallly.

The piece of the puzzle that looks to be missing is a date/time field that
stores the date and time of the battery change. This lets you add another
record later (which probably gets a higher ID value, but still specify
that the record fits between 2 others based on the date/time.

If that field is named "ChangeDateTime", you could create a query and type
something like this into the Field row to get the calculated field named
"LastBattery":
LastBattery: ELookup("Battery", "MyTable", "([Vehicle] = """ & [Vehicle] &
") AND ([ChangeDateTime] < " & Format([ChangeDateTime],
"\#mm\/dd\/yyyy\#") & ")", "[ChangeDateTime] DESC")

Note that the requirement to order records differently than the ID field
means DLookup() is not up to the task, and you need the ELookup() from
this link:
http://allenbrowne.com/ser-42.html
Replace "MyTable" with the name of your table.

Another approach would be to use a subquery. This will be way faster, but
will give read-only results. The expression to type into your quey will be
something like this:

LastBattery: (SELECT TOP 1 Battery FROM MyTable AS Dupe WHERE
(Dupe.Vehicle = MyTable.Vehicle) AND (Dupe.ChangeDateTime <
MyTable.ChangeDateTime) ORDER BY Dupe.ChangeDateTime, Dupe.ID)

 

[Allen Browne]

Yes: you are making it too difficult by trying to store the value instead of
asking Access to get it when you need it.

If you try to store it, you must respond to every insert, edit, or delete,
to ensure that every other entry is still correct. By using the calculated
field as indicated, the results can never be wrong. (One of the basic rules
of data design is not to store dependent data. For more info, search on
"normalization.")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I already have another query that orders the list of vehicles down to the
current record's vehicle. such that the query results are as follows based
on the Table data in the original post:

ID Vehicle Battery Last Battery
101 002 028 006
104 002 004

Having said that, I need the Last Battery field populated with Battery 028
from the battery that should be in the vehicle currently. Am I trying to
make this too difficult?

--
Dan Johnson

Okay, you are on the right track by having LastBattery as a calculated
field, i.e. it is not stored in the table, but calculated as needed, so
that if another record is inserted it, will update automaticallly.

The piece of the puzzle that looks to be missing is a date/time field
that stores the date and time of the battery change. This lets you add
another record later (which probably gets a higher ID value, but still
specify that the record fits between 2 others based on the date/time.

If that field is named "ChangeDateTime", you could create a query and
type something like this into the Field row to get the calculated field
named "LastBattery":
LastBattery: ELookup("Battery", "MyTable", "([Vehicle] = """ & [Vehicle]
& ") AND ([ChangeDateTime] < " & Format([ChangeDateTime],
"\#mm\/dd\/yyyy\#") & ")", "[ChangeDateTime] DESC")

Note that the requirement to order records differently than the ID field
means DLookup() is not up to the task, and you need the ELookup() from
this link:
http://allenbrowne.com/ser-42.html
Replace "MyTable" with the name of your table.

Another approach would be to use a subquery. This will be way faster, but
will give read-only results. The expression to type into your quey will
be something like this:

LastBattery: (SELECT TOP 1 Battery FROM MyTable AS Dupe WHERE
(Dupe.Vehicle = MyTable.Vehicle) AND (Dupe.ChangeDateTime <
MyTable.ChangeDateTime) ORDER BY Dupe.ChangeDateTime, Dupe.ID)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen et al,

This is not exactly what I am looking for, I don't believe or I may need
more of the syntax for the query portion of this record. The end result
was the previous record of the Form's current record. I need the
previous record of the resultant query (not the table) because the
previous record in the Table is another vehicle. I need the previous
record of the Form's current vehicle's battery.

This is extremely difficult where I am headed with this form. I
apologize for being so vague. Perhaps the following will help make it
clearer:

ID Vehicle Battery Last Battery

101 002 028 006
102 005 005 010
103 007 013 025
104 002 004 (this need to auto
update to 006 from 101 above) Current form record

Thanks for any additional help.

--
Dan Johnson


See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504


Hello,

I have a rather lengthy question in regard to updating a field based on
the Previous record's input. We have electric vehicles that utilize
batteries for their operation. There are 13 vehicles with approximately
2 battery changes per day in a group of 28 batteries. As an operator
changes the battery in the vehicle, I would like to automatically input
that current battery into the now Last battery field within a form for
the data entry.

I have been able to get two queries written to isolate down to the
current record. Based on the form's current record, the first query
filters to ALL of that specific vehicle's records. The second query
isolates down to the ID (AutoNumber) of the current vehicle. This is
the point at which I am stumped

I haven't been able to figure out how to go to the Previous Battery for
that record (vehicle battery change) in an effort to do a later copy
via macro.

I currently have the form updating with the Last Battery but that
doesn't work because the Form needs to have the ability to make
corrections on older records. Since one of the older records happened,
in some cases, a month before the Last Battery was installed, it
actually needs to be that record's Previous record.

Unfortunately, I am not proficient in VB and am limited to the wizards
for queries.

I apologize for this lengthy request. Thanks, in advance for any
suggestions for this problem.