Concatenating multiple fields and comparing with some variance - best approach?

[RebeccainGA]

I'm trying to compare two data tables. In one, latitude and longitude are concatenated already (DD-MM-SS.SS). In the other, they are split into columns (DD, MM, SS.SS in three fields). I need to compare the two coordinates, and need to be able to (if possible) have some variance tolerance - in other words, if the SS.SS isn't exact, but is within 2 seconds, it'll still return a value. If that part needs to be done separately from the original calculation, that's fine - I will gladly split the comparison into steps (exact match, and non-exact match).



Any help you can provide would be greatly appreciated!

[recyan]

Can you post a few sample data of the two tables and the resulting data that you are looking for.

Thanks

[RebeccainGA]

Can you post a few sample data of the two tables and the resulting data that you are looking for.

Thanks

The examples are something like this: (field 1, 2, and 3 are from table 1 - Field 4 is from table 2)

Field 1 Field 2 Field 3 Field 4 Desired Display
94 42 33.2 94-42-33.4 Match within 1 second
102 17 42.4 102-17-44 No Match
087 22 17.1 87-22-17.1 Match within 1 second

[recyan]

Hi,
just check out if below gives some guidelines :

Sub-query to be saved :
Query1

SELECT 
    Table2.Field4, 
    Left([Field4],InStr(1,[Field4],"-")-1) AS TheFirstPart,
    Mid([Field4],InStr(1,[Field4],"-")+1,(InStrRev([Field4],"-"))-(InStr(1,[Field4],"-")+1)) AS TheSecondPart,
    Mid([Field4],InStrRev([Field4],"-")+1,Len([Field4])-InStrRev([Field4],"-")) AS TheLastPart
FROM 
    Table2;

The final query to be saved & run :
Query2

SELECT 
    Table1.Field1, 
    Query1.TheFirstPart, 
    Table1.Field2, 
    Query1.TheSecondPart, 
    Table1.Field3, 
    Query1.TheLastPart, 
    [Field3]-[TheLastPart] AS TheDifference, 
    IIf([TheDifference]<1,IIf([TheDifference]>-1,"OK","Not Ok"),"Not Ok") AS TheFinalCheck
FROM 
    Table1 
    LEFT JOIN 
    Query1 
    ON 
    (Table1.Field1 = Query1.TheFirstPart) 
    AND 
    (Table1.Field2 = Query1.TheSecondPart);

Thanks

[RebeccainGA]

Hi,
just check out if below gives some guidelines :

Thanks

With that head start, I think I have it working now. Thanks so much!

[recyan]

Glad you found it helpful.

Thanks