Crossmatching addresses by similar co-ordinates

[rosieb]

I’m doing a project to crossmatch addresses from various internal and external systems.

I have two tables containing addresses, each address has an ID and a grid reference - two fields, Easting and Northing (these represent the distance in metres from a zero point).

A third table holds the matching information ie the IDs of the matched records.

I want to take the records which have not been matched in table 1 and identify any records in table 2 where the Easting and Northings are the both the same +/- 15

I can’t think where to start on this – can anyone suggest an approach?


Rosie
"Don't try to improve one thing by 100%, try to improve 100 things by 1%

 

[SkipVought]

Hi,

How about Between???

Between [Field]-var And [Field]+var

where var is 15 in your request.


Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[rosieb]

Hi Skip

I just can't visualise how to join the two separate address tables as there is no common field, just this fuzzy match between the Northings and Eastings.

I've got a solution for comparing one record at a time which works in a similar way to your solution, but I'm trying to find a way to get a batch of results which I can then validate by eye.

Am I being really dense here?

Rosie
"Don't try to improve one thing by 100%, try to improve 100 things by 1%

 

[SkipVought]

Not knowing your tables, I can't say. Need more information to help, I'm afraid.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[rosieb]

I think I've got it - I just had a flash of the blindingly obvious...

A cartesian product join with a where:

SELECT qryUniformAddresses.ADDRESS, qryUniformAddresses.MAP_EAST, qryUniformAddresses.MAP_NORTH, qryAPointData.Addr, qryAPointData.Easting, qryAPointData.Northing
FROM qryUniformAddresses, qryAPointData
WHERE (((qryAPointData.Easting) Between [map_East]-15 And [map_East]+15) AND ((qryAPointData.Northing) Between [map_north]-15 And [map_north]+15));

It may not be the most elegant solution, but it looks as if it's working.

Thanks for taking the trouble to look at this Skip.

Rosie
"Don't try to improve one thing by 100%, try to improve 100 things by 1%