Re: Previous record

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


"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:OjTd4ZnXFHA.3280@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan Johnson" <dan_johnson@xxxxxxxxxxx> wrote in message
> news:e%23Rhk9lXFHA.3728@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>>
>> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
>> news:eDMbLvhXFHA.1256@xxxxxxxxxxxxxxxxxxxxxxx
>>> See:
>>> Referring to a Field in the Previous Record or Next Record
>>> at:
>>> http://support.microsoft.com/default.aspx?scid=kb;en-us;210504
>>>
>>>
>>> "Dan Johnson" <dan_johnson@xxxxxxxxxxx> wrote in message
>>> news:O1fnxphXFHA.612@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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
>
>


.

Relevant Pages

  • Re: Previous record
    ... more of the syntax for the query portion of this record. ... Table is another vehicle. ... > battery changes per day in a group of 28 batteries. ...
    (microsoft.public.access.queries)
  • Re: Previous record
    ... If you try to store it, you must respond to every insert, edit, or delete, ... > I already have another query that orders the list of vehicles down to the ... I need the Last Battery field populated with Battery 028 ... > from the battery that should be in the vehicle currently. ...
    (microsoft.public.access.queries)
  • Re: Previous record
    ... stores the date and time of the battery change. ... If that field is named "ChangeDateTime", you could create a query and type ... > in the Table is another vehicle. ...
    (microsoft.public.access.queries)
  • Re: Enhanced mobility for infantry
    ... the enemy as to the actual battery position, but the Germans are Germans, ... FOOs move in vehicles of the same type as the force they are ... the same vehicle they could communicate - which is actually horsecrap, ...
    (sci.military.naval)
  • Re: What happens when your scooter Breaksdowns / loses power outside ?
    ... >> You will damage the battery!! ... >> just google mobility scooter breakdown service ... So its essentially the same as AA or RAC for your car. ... or mechanical breakdown of your vehicle. ...
    (uk.people.disability)