I'm working in an access database with the following fields (and others):

Asset; Asset_Date; Start_Date; Start_Time; Office_Stop.

Asset is a # field, Asset_Date is text; Start_Date is Date/Time; Start_Time is Date/Time; and Office_Stop is # (1 or 0).

Asset numbers are unique vehicle identifiers and all of the data are from GPS software. Using the GPS data and ArcGIS, I added the binary "Office_Stop" so I could tell whether each point was an "Office_Stop" or not.

I would like to know where each truck started (Office_Start = 1 or 0).

Logically, the start would be where the truck stopped last. I know I can export to excel, do a (B2=A1) type formula and move on. The problem is I have to import new data on a regular basis and I want to avoid multiple excel files and imports/exports in Access - I have too many working files as it is.

Is there a way to have access query the "previous record" for each asset?


Ps. I can read some SQL, but I'm really not great writing it.

  • 1
    Hi and welcome to the forum - take the tour and visit the help centre. Then read (and follow as best you can the instructions in) this and this. Try and give us some DDL, some DML with sample data and the result you want! An explanation of the logic you used to get to that result would be helpful. These sites are a great resource and you will get all the more out of them if you follow the guidelines. – Vérace May 31 '18 at 18:13
  • Before getting to the SQL, please identify what you mean by "previous record". If I were to guess what that means from the info you provided I would choose the [Start_Date] and [Start_Time] fields to identify the most recent record. But in that case, the [Office_Stop] field seems either redundant or unnecessary. What is the appropriate interpretation? – C Perkins Jun 1 '18 at 16:18
  • C Perkins - you have the same thought process I do, but there are a few complicating factors that someone other than me might find easy to get around. The [Start_Date] and [Start_Time] fields would have to be for the right [Asset] (I concatenated [Asset_Date] to try to deal with that). [Office_Stop] identifies whether the truck stopped at an office or not; I need to know where that truck started (I need to populate [Office_Start]). I'm not looking for the most recent record, but hoping to populate one record with info from the previous record. – Hurst Gannon Jun 28 '18 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.