DATE COMPARISON How to compare records on two tables

[lucianone41]

Hello everyone, I´m starting to learn Access , SQL and a bit of VB.

My problem is the following: I have two tables, one of them contains the records for missing points on a GPS system (called the "missing" table) ... the second table, contains all the available points on GPS (called the "complete" table). I will put an example

Table: Missing

Field1:Hour
----------------
10:04
10:09

Table2: GPS

Field1:X Field2:Y Field3:Hour
----------- ------------ -----------------
10 20 10:01
11 23 10:02
12 18 10:03
14 20 10:05
13 20 10:06
14 22 10:07
16 21 10:11


What I need to do is the following: go to each record on the "Missing" table and look for the record that is closer so to assign the values of Field1 and Field 2. to it. For example, lets look at the firs registry on table "missing" which is 10:04 .... on the GPS table i can found that there are two records that are close to 10:04, 10:03 and 10:05 ... so i want to average the values of the fields X and Y, and asign that average to a new fields on the missing tables as "AvgX" and "Avg"Y ... on this case it would turn out with the values of 13 for AvgX (average of 12 and 14) and 19 for AvgY (average of 18 and 20), like this:


Table: Missing

Field1:Hour Field2:AvgX Field3:AvgY
---------------- ----------------- -----------------
10:04 13 19



I was hoping to find a way to do it with sql queries, but i cant seem to find a way to compare between records, or the only choice is VB? in the case its VB, could somebody please guide me on same basic code i can apply?

thanks in advance,

[kepston]

While I work on this, I have a couple of questions.

Will you accept fractions?
e.g. your second missing example is 10:09 producing X=15, Y=21.5

What result do you want if the missing time is not exactly half way between known points?
e.g. what values for 10:10? The same as 10:09, or higher, if higher, by 'distance' covered (linear interpolation).

[lucianone41]

While I work on this, I have a couple of questions.

Will you accept fractions?
e.g. your second missing example is 10:09 producing X=15, Y=21.5

What result do you want if the missing time is not exactly half way between known points?
e.g. what values for 10:10? The same as 10:09, or higher, if higher, by 'distance' covered (linear interpolation).

1. Thanks for your help!


2. regard your questions:

a. I accept fractions, there is no problem with that, in fact the data i put as an example was an integer approximation, in fact almost all the coordinates are numbers with fractions, but I put it on integer to simplify it.

b. If the missing time is not exactly half way between known points , then what it would be great is to have the most proximate point to the missing point. Originally i thought of linear interpolation as you said it .... for example:

if the missing point is 10:10 and I have as choices 10:07 and 10:11 ... I think there could be two choices: picking 10:11 because its nearer or making a linear interpolation using 10:07 and 10:11 ..... I believe the first choice is better because for this problem, one minute is really a huge difference, so taking an interpolation might produce a point pretty far away from an approximate point .... but either solution is good, so if you want to try any of this versions is excellent for me ....

again thanks, i appreciate your interest in helping me

[kepston]

if the missing point is 10:10 and I have as choices 10:07 and 10:11 ... I think there could be two choices: picking 10:11 because its nearer or making a linear interpolation using 10:07 and 10:11 ..... I believe the first choice is better because for this problem, one minute is really a huge difference, so taking an interpolation might produce a point pretty far away from an approximate point .... but either solution is good, so if you want to try any of this versions is excellent for me ....

I think you have misunderstood interpolation.
If we look at finding position at 10:10, then position at 10:11 would be better than at 10:07, using nearest known position.
Position at 10:11 would also be better than the average, which in this case would be position at 10:09. i.e. 2 minutes away from known!
Interpolation works out how far between start and end, required point is, and calculates for that position.
10:11-10:07 =4
10:10-10:07 =3
so we calculate position 3/4 of distance from position at 10:07 to 10:11.

(This will be accurate in a two dimensional space at constant speed and direction)

[lucianone41]

I think you have misunderstood interpolation.
If we look at finding position at 10:10, then position at 10:11 would be better than at 10:07, using nearest known position.
Position at 10:11 would also be better than the average, which in this case would be position at 10:09. i.e. 2 minutes away from known!
Interpolation works out how far between start and end, required point is, and calculates for that position.
10:11-10:07 =4
10:10-10:07 =3
so we calculate position 3/4 of distance from position at 10:07 to 10:11.

(This will be accurate in a two dimensional space at constant speed and direction)

I see, i was misunderstandig interpolation, i thought it was more like a weighted average ....
anyway, i think what it would fit best would be choosing , as you said, the nearest point from the missing one, also because i least i can guarantee that the point really exists.

[kepston]

I see, i was misunderstandig interpolation, i thought it was more like a weighted average ....
anyway, i think what it would fit best would be choosing , as you said, the nearest point from the missing one, also because i least i can guarantee that the point really exists.

Choosing an actual point makes sense.
In which case, do you want the point before, or after, or both when midway between known points?

qryNearestTime:

SELECT Missing.Hour AS MHour, Min(Abs([GPS].[Hour]-[mHour])) AS NearestTimeDiff,
[mhour]-[nearestTimeDiff] AS Bef, [mhour]+[nearestTimeDiff] AS Aft
FROM GPS, Missing
GROUP BY Missing.Hour;
 

qryNearestPoint :

SELECT [qryNearestTime].[MHour], [GPS].[Hour], [GPS].[X], [GPS].[Y]
FROM qryNearestTime INNER JOIN GPS ON ([qryNearestTime].[Aft]=[GPS].[Hour]) 
Or ([qryNearestTime].[Bef]=[GPS].[Hour]);
 

qryNearestPoint will currently show both before and after when equidistant