Software

Ask something
Discussion Locked

Access Query Question

0 helpful
5 replies

by ganwold

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a set of numbers in 2 tables that I'm trying to match, but there is a certain amount of error between them. Basically it's a GPS log that I'm comparing to a data file to find the real location and of course there is an error.

Is it possible to build the error correction directly into the query or would I have to compare them in VB through an array?

5 replies

by Member 34793965

If you have a set range of comparison, say plus or minus 5, then this might work:

Create a query on the second table called qryRangeResults. The query would contain two calculated fields as well as some identifier such as the primary key field. The SQL view would look something like this:

SELECT ID, (NumberField - 5) AS LowerRange, (NumberField + 5) AS UpperRange
FROM tblNumberTwo;

Now create another query that looks something like this:

SELECT tblNumberOne.ID AS FirstID, qryRangeResults.ID AS SecondID
FROM tblNumberOne, qryRangeResults
WHERE tblNumberOne.NumberField BETWEEN qryRangeResults.LowerRange AND qryRangeResults.UpperRange;

When the second query is run, it should show a list of ID numbers from both tables where the number field from the first table is within the specified value (in this case 5) either side of the number field value in the second table.

If you wanted to get fancy, you could create a parameter query that prompted for the amounts above and below.

by dpj

Query's and Sub Query's take additional resources. Perhaps create a function, we'll call this "calcLoc" in a Public Function (in a Module). This Function would look something like this...
Public Function calcLoc(GPScoord As String)
Dim GPSloc As Integer '(I am not familiar with coordinates, but it may need to be Long)
Dim GPShgh As Integer '(same comment as before)
Dim GPSlow As Integer '(same comment as before)

GPSloc = 'this should be the field name of the data you are checking in your table...for our purposes we will call this GPScoord
GPShgh = GPSloc + 5
GPSlow = GPSloc - 5

If GPSloc <= GPShgh AND GPSloc >= GPSlow Then
'what ever you need it to do
Else
'you should always use this to trap an error
End If
End Funtion

Next, in the QBE (in your "Query By Example" pane) simply call the function. For exmample, Exp1: calcLoc([GPScoord]). Please note, that you may add a parameter either from a form, query, report, etc. to this also, as you would for any query.

I have not tested this, but I hope that this helps. I am very new to all of this, so if anyone can make this better, GREAT!!!

Good Luck,

Don

by trmcdougle

Just of the top of my head how about :-

SELECT FL.ItemName, GPS.X, GPS.Y
FROM GPS LEFT JOIN FixedLocations AS FL
WHERE (ABS(FL.X-GPS.X)<=5) AND (ABS(FL.Y-GPS.Y)<=5)

This would provide multiple returns if several fixed locations are within "5" of the GPS figure, with a NULL ItemName if no locations are.
The WHERE clause on this might be more efficient if it avoids the function, specifically :-

WHERE (FL.X >= (GPS.X-5)) AND (FL.X <=(GPS.X+5)) AND (FL.Y >= (GPS.Y-5)) AND (FL.Y <=(GPS.Y+5))

Alternatively to get the nearest match (but probably more slowly) :-

SELECT (
SELECT TOP 1 FL.ItemName FROM FixedLocations AS FL ORDER BY (ABS(FL.X-GPS.X)+ABS(FL.Y-GPS.Y)) DESC
), GPS.X, GPS.Y FROM GPS

by trmcdougle

The nearest could be even more accurate by using Pythag, i.e. if the ORDER BY was :-
SQRT(SQR(FL.X-GPS.X)+SQR(FL.Y-GPS.Y))

by barry.edmund.wright

Any question on MS-Access should be posted to the following newgroup:
comp.databases.ms-access

Questions are usually answered within a couple of hours of posting.

This discussion is closed to new replies.