Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2017
    Posts
    1

    Question Unanswered: Inner Join, Acos and Radians in Access from SQL

    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. #2
    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,
    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, 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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •