Previous record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan Johnson - 21 May 2005 16:33 GMT

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.

Signature

Dan Johnson

Reply to this Message

Allen Browne - 21 May 2005 16:43 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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.

Signature

Dan Johnson

Reply to this Message

Dan Johnson - 22 May 2005 00:47 GMT

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.

Signature

Dan Johnson

> See:
> Referring to a Field in the Previous Record or Next Record
[quoted text clipped - 32 lines]
> I apologize for this lengthy request. Thanks, in advance for any
> suggestions for this problem.

Reply to this Message

Allen Browne - 22 May 2005 03:20 GMT

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen et al,
>
[quoted text clipped - 54 lines]
>> I apologize for this lengthy request. Thanks, in advance for any
>> suggestions for this problem.

Reply to this Message

Allen Browne - 22 May 2005 03:31 GMT

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)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen et al,
>
[quoted text clipped - 54 lines]
>> I apologize for this lengthy request. Thanks, in advance for any
>> suggestions for this problem.

Reply to this Message

Dan Johnson - 22 May 2005 19:47 GMT

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?

Signature

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
[quoted text clipped - 84 lines]
>>> I apologize for this lengthy request. Thanks, in advance for any
>>> suggestions for this problem.

Reply to this Message

Allen Browne - 23 May 2005 02:47 GMT

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.")

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 99 lines]
>>>> I apologize for this lengthy request. Thanks, in advance for any
>>>> suggestions for this problem.

Reply to this Message