2

Having a table with devices tracks. Each tracking device has a unique ID, and users upload their GPS tracks to the site, specifying the tracking device ID. This data is stored in a table, so each row is composed from DeviceID, Lat, Lon, and Date columns. Example:

ID          DeviceID       Lon             Lat              Date
----------- -------------- --------------- ---------------- ----------------------- 
1652726     DLX*X-------   -20.56528769     20.68961421      2015-09-01 00:00:02.000
1652727     F4K*T-------   -20.95713512     20.80473833      2015-09-01 00:00:05.000
1652728     F4K*4-------   -20.87060998     20.00037712      2015-09-01 00:00:27.000
1652729     DLX*K-------   -20.87061003     20.00039333      2015-09-01 00:01:04.000
1652730     DLX*7-------   -20.87060189     20.00039248      2015-09-01 00:01:30.000
1652731     F4K*T-------   -20.8706208      20.00037551      2015-09-01 00:01:39.000
1652732     F4K*2-------   -20.87060854     20.00038717      2015-09-01 00:01:51.000
1652733     DLX*7-------   -20.90161642     20.98749573      2015-09-01 00:02:02.000
1652734     F4K*4-------   -20.87060145     20.0003771       2015-09-01 00:02:05.000
1652735     F4K*T-------   -20.0907285      20.00816632      2015-09-01 00:02:10.000
1652736     F4K*4-------   -20.90157325     20.98748467      2015-09-01 00:02:49.000
1652737     F4K*T-------   -20.22212807     20.77575096      2015-09-01 00:02:53.000
1652738     DLX*G-------   -20.87261384     20.00250375      2015-09-01 00:03:09.000
1652739     DLX*D-------   -20.76831339     20.08081263      2015-09-01 00:03:15.000
1652740     F4K*T-------   -20.76913834     20.08311954      2015-09-01 00:03:24.000
1652741     F4K*T-------   -20.53557613     20.78679804      2015-09-01 00:03:57.000
1652742     F4K*2-------   -20.8706104      20.00037474      2015-09-01 00:03:59.000
1652743     F4K*2-------   -20.76919089     20.0830366       2015-09-01 00:04:15.000

There is a need to display the 5 most recent coordinates for each device. I have successfully managed to write a T-SQL query, which does it:

SELECT ss.[DeviceID], cc.*
FROM (SELECT DISTINCT [DeviceID] FROM [Device_Places]) ss
CROSS APPLY (SELECT TOP 5 S.[Date], S.Lat, S.Lon FROM [Device_Places] S
             WHERE S.DeviceID = ss.DeviceID
             ORDER BY S.[Date] DESC) cc

But, unfortunately, this SQL is not valid in Microsoft Access (checked up to MS Access 2016). I tried to translate the above query with CROSS APPLY to a self-join variant. But to no avail. Will be thankful to anyone, who can help making an equivalent of the above query in MS Access.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
sindilevich
  • 398
  • 5
  • 9

1 Answers1

1

I believe I have finally managed to come up with a MS Access query, analogous to the CROSS APPLY variant I had previously posted.

SELECT DP.*
FROM DEVICE_PLACES DP
WHERE DP.ID IN
(
    SELECT TOP 5 ID
    FROM DEVICE_PLACES
    WHERE DeviceID = DP.DeviceID
    ORDER BY [Date] DESC, ID
)

Running, however, the self-join variant over a data set of more than 550,000 rows shows a disastrous performance. It takes more than 4 minutes to reduce the data set to the 5 most recent coordinates for each device.

sindilevich
  • 398
  • 5
  • 9
  • 2
    You could try this approach. From Access '97... https://support.microsoft.com/en-us/kb/153747 – Martin Smith Dec 06 '15 at 19:08
  • If your ACCESS app is only a front end for a SQL Server database then use a pass-through query. Note that I believe ACCESS-SQL was last enhanced in any substantive way in the previous millennium (1995). – Pieter Geerkens Dec 06 '15 at 20:28
  • 1
    @MartinSmith: Method 1 is basically what he has right now, Method 2 would probably perform much worse than that. – Andre Dec 06 '15 at 23:43