[OZAPRS] Microsoft Access question

[Ben Rampling]

Darryl,

If you're using Access with MSDE/Transact-SQL something like the following
will produce your result:

SELECT *
FROM tblPositions
WHERE NOT EXISTS
    (SELECT time
    FROM tblPositions AS innerPositions
    WHERE
    DATEDIFF(second, innerPositions.time, CURRENT_TIMESTAMP)
    < DATEDIFF(second, tblPositions.time, CURRENT_TIMESTAMP)
    AND
    innerPositions.callsign = tblPositions.callsign)

(Replacing CURRENT_TIMESTAMP with the time of interest)

You should be able to convert the query to work on any database supporting
subqueries (Not sure if Access Jet does or not...). Just replace the
DATEDIFF function with anything that will give you the absolute value of
the
difference of dates or times.

Regards,
Ben Rampling VK6TLA

> > People
>
> I am attempting to use Microsoft Acces... And I am attempting to do a
funny
> query... And I need some help. This is for my OziAPRS software...
>
> I have a table called tblPositions. It contains the following fields
> Callsign * Time *
> Lat
> Lon
>
> Callsign and Time are Key Fields. Time is actually combined Date/Time.
>
> What I want to do is to return the Lat, Lon, Callsign of
> __ALL_CALLSIGNS__ __CLOSEST__ to a given time. That is I want to find
> the where all my trackers were at about midday.
>
> This is not as easy as it sounds... Can anyone help?
>
> Darryl


_______________________________________________
ozaprs mailing list
ozaprs at marconi.ics.mq.edu.au
http://marconi.ics.mq.edu.au/cgi-bin/mailman/listinfo/ozaprs