Copying field based on other closest matching values.

[JRodko]

I've been trying to come back to this and chip away for months with little progress. It's tough to explain so I've included a sample DB.



What I want to do is compare the 'distance' between the two tables, tblKnown and tblFind.

Using the closest matching 'distance' fields to join the records, the 'dip' value will be copied from tblKnown to the 'NearestDip' in tblFind. The same is needed for 'Dir' and 'NearestDir'. It is important that the project ID be matching as well.

To try to sum this up again... The values in tblFind will be updated using values in tblKnown. Which records are selected is dictated by where 'Distance' in tblKnown most closely matches the 'Distance' in tblFind.

I have filled a few of the values in tblFind as an example. But 'NearestDip' and 'NearestDir' will always be updated by the query (I hope).

If the difference between 'Distance' is split evenly between two records in 'tblKnown', then I don't care which one gets picked. Preferably the one with the lowest 'Distance' value.

I hope that makes sense. I've been scratching my head with DLookup, ELookup, etc. off and on for quite a while. If this can be done without more advanced functions then that would be great too.Nearest1.accdb

[June7]

Need to change Project field in tblKnown to text type. Then consider:

Query1:

SELECT tblFind.ID, tblFind.Project, tblFind.Distance AS FindDist, tblKnown.Distance AS KnownDist, Abs([tblKnown].[Distance]-[tblFind].[Distance]) AS Diff, tblKnown.Dip, tblKnown.Dir
FROM tblKnown INNER JOIN tblFind ON tblKnown.Project = tblFind.Project
ORDER BY tblFind.ID;

Query2:

SELECT Query1.* FROM Query1 WHERE Diff=DMin("Diff","Query1","ID=" & [ID]);

or

SELECT Query1.* FROM Query1 WHERE Diff IN (SELECT Top 1 Diff FROM Query1 AS Dupe WHERE Dupe.ID=Query1.ID ORDER BY Diff);

[JRodko]

I think this works nicely. Thank you very much, June7.

What would be the differences between the two options for 'Query2'?

[June7]

Domain aggregate functions can cause slower performance. But then so can some nested queries. Try both. What happens?

[JRodko]

Results are identical but the latter is much quicker.