1. Registered User
Join Date
Jun 2017
Posts
1

Hello I have to translate a query from SQL to Access query.
This is the SQL query:

SELECT ABS(timestampdiff(minute,p.tempo,p2.tempo)) as tempoDifferita, ABS(6372.7954775979997 (Acos( Sin(RADIANS(p.lat)) Sin(RADIANS(p2.lat)) + Cos(RADIANS(p.lat)) Cos(RADIANS(p2.lat)) Cos(ABS(RADIANS(p.lon) - RADIANS(p2.lon)))))*1000) AS distanza,p.idutenza,p2.idutenza as idutenza2, p.tempo,p2.tempo as tempo2, p.lon,p.lat, p2.lon as lon2,p2.lat as lat2, p.velocita,p2.velocita as velocita2, p.indirizzo,p2.indirizzo as indirizzo2, p.nsat,p2.nsat as nsat2, p.direzione,p2.direzione as direzione2, p.flags,p2.flags as flags2 FROM punti as p INNER JOIN ( SELECT FROM punti AS p2 WHERE p2.idutenza = 1 AND p2.tempo>=#2017-01-31 09:15:20# AND p2.tempo<=#2017-05-24 13:01:31# AND p2.flags&1=1 AND NOT (p2.Lat=0 AND p2.Lon=0) ) as p2 WHERE p.idutenza = 2 AND p.tempo>=#2017-01-31 09:15:20# AND p.tempo<=#2017-05-24 13:01:31# AND p.flags&1=1 AND NOT (p.Lat=0 AND p.Lon=0) AND ABS(timestampdiff(minute,p.tempo,p2.tempo))<= 60 AND ABS(6372.7954775979997 (Acos( Sin(RADIANS(p.lat)) Sin(RADIANS(p2.lat)) + Cos(RADIANS(p.lat)) Cos(RADIANS(p2.lat)) * Cos(ABS(p.lon - p2.lon))))*1000) <= 500

It works perfeclty in MySQL.

In Access it gives error in the INNER JOIN, Acos and Radians and really I don't know how to translate everything in order to work with Access.
Someone could help me? Thanks a lot!

2. Registered User
Join Date
Aug 2017
Posts
3

## Self joining how?

Looks like you are trying to join the table punti to itself, but the query does not identify the columns used to to join.
However I think you are trying to select 2 individual records into one record. So the query should be:

SELECT ABS(timestampdiff(minute,p.tempo,p2.tempo)) as tempoDifferita,
p.idutenza,
p2.idutenza as idutenza2,
p.tempo,
p2.tempo as tempo2,
p.lon,
p.lat,
p2.lon as lon2,
p2.lat as lat2,
p.velocita,
p2.velocita as velocita2,
p.indirizzo,
p2.indirizzo as indirizzo2,
p.nsat,
p2.nsat as nsat2,
p.direzione,
p2.direzione as direzione2,
p.flags,
p2.flags as flags2
FROM punti as p, punti as p2
WHERE p2.idutenza = 1
AND p2.tempo>=#2017-01-31 09:15:20#
AND p2.tempo<=#2017-05-24 13:01:31#
AND p2.flags&1=1
AND NOT (p2.Lat=0 AND p2.Lon=0)
WHERE p.idutenza = 2
AND p.tempo>=#2017-01-31 09:15:20#
AND p.tempo<=#2017-05-24 13:01:31#
AND p.flags&1=1
AND NOT (p.Lat=0 AND p.Lon=0)
AND ABS(timestampdiff(minute,p.tempo,p2.tempo))<= 60