0
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?
Thanks!
Hurst Gannon
Posted 2018-05-31T16:42:04.070
Reputation: 101
you can use Sub Query (two Select commands) and get TOP 1 record in Descending order. Or create Query & sort date in Descending order for top 1 record. – Rajesh S – 2018-05-31T17:08:00.453
Would this be iterative (i.e. every record return the info from the record that happened before it)? – Hurst Gannon – 2018-05-31T17:12:34.700
This can be easily achieved on date like previous date, last week or year. – Rajesh S – 2018-05-31T17:17:11.510
Forgive me for asking for clarification, but I'm not sure I follow how to actually do it. Unfortunately, I'm not very good at writing SQL - I typically use the query builder! Given the above, can you explain how to write the query? – Hurst Gannon – 2018-05-31T17:18:53.147
This will get previous date using current date, =Date - weekday(date(),2)+1 - iif(weekday(date(),2) = 1, 7,0) AND < Date() – Rajesh S – 2018-05-31T17:25:19.703