Query with mathematics criteria!!?? Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am designing a database with a rather simple flat structure to hold a set
of measurement data. There are ten input fields named A-J plus one field to
hold a description. The idea with the database is to first match a new set of
A-J with all old A-J, and then to get the description from the old A-J set
that was the best match.

This matching must be done according to some mathematical formula. The
formula might change during the development while I am experimenting to see
whech gives the best result. My first assumptions are two:

1. Let's call the new A-J An-Jn and the old ones Ao-Jo. The simple formula
then is Sum = |An-Ao| + |Bn-Bo| + |Cn-Co|... + |Jn-Jo| (|number| is the
absolute value of a number. The absolute value of |62-64| = 2, i.e. you
perform the subtraction and then remove any negative sign).

The matching is to find te smallest Sum, and to sort the entries according
to Sum in ascending order, with the smallest Sum first.

2. This is slightly more complicated, but not much. Sum = (An-Ao)^2 +
(Bn-Bo)^2 + (Cn-Co)^2 ... + (Jn-Jo)^2 (^2 means squared).

How can this be done?

Best,

Tomas
 
I am designing a database with a rather simple flat structure to hold a set
of measurement data. There are ten input fields named A-J plus one field to
hold a description. The idea with the database is to first match a new set of
A-J with all old A-J, and then to get the description from the old A-J set
that was the best match.
Click to expand...
2. This is slightly more complicated, but not much. Sum = (An-Ao)^2 +
(Bn-Bo)^2 + (Cn-Co)^2 ... + (Jn-Jo)^2 (^2 means squared).

How can this be done?
Click to expand...

......ssssslllllloooowwwwwlllllyyyyy.... <g>

Create a Cartesian join query by adding TableA and TableB to the query
grid. Use NO join line. This will give you every possible combination
of records between the two tables. If you can do this one record at a
time by using criteria on TableA it may be tolerably fast - but if you
want to find the closest match for each of 1000 records in TableA to
the 1000 records in TableB, Access will need to do a million
comparisons.

Create a calculated field in the Query by typing

Sum: ([TableA].[A] - [TableB].[A])^2 + ([TableA]. - [TableB].)^2

and so on and so on. Sort ascending on this field. The first record in
the query will be the closest match (between the single record in
TableA and all the records in TableB, or between any record in TableA
and any record in TableB if you have multiple TableA records). You can
set the Top Values property of the query to 1 to show only the closest
match. Note that there might be ties (two records equally close in
this ten-dimensional space).


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John,

Thanks!

For now I don't have to match 1000 records with themselves, but only the
newest record (1 record with 10 values) with all old (<1000 records with 10
values). I might not even add the new record to the database if it doesn't
meet some criteria, but instead just have the 10 values in 10 search fields
and do the match against the old ones. Depending on what comes up during the
search, I might then add the new record or just discard it.

Would I still do it the same way you suggested? How do I create the quesry
if the new set only exists in 10 search fields and not in the database as a
record?

Best,

Tomas

"John Vinson" skrev:
I am designing a database with a rather simple flat structure to hold a set
of measurement data. There are ten input fields named A-J plus one field to
hold a description. The idea with the database is to first match a new set of
A-J with all old A-J, and then to get the description from the old A-J set
that was the best match.
Click to expand...
2. This is slightly more complicated, but not much. Sum = (An-Ao)^2 +
(Bn-Bo)^2 + (Cn-Co)^2 ... + (Jn-Jo)^2 (^2 means squared).

How can this be done?
Click to expand...

......ssssslllllloooowwwwwlllllyyyyy.... <g>

Create a Cartesian join query by adding TableA and TableB to the query
grid. Use NO join line. This will give you every possible combination
of records between the two tables. If you can do this one record at a
time by using criteria on TableA it may be tolerably fast - but if you
want to find the closest match for each of 1000 records in TableA to
the 1000 records in TableB, Access will need to do a million
comparisons.

Create a calculated field in the Query by typing

Sum: ([TableA].[A] - [TableB].[A])^2 + ([TableA]. - [TableB].)^2

and so on and so on. Sort ascending on this field. The first record in
the query will be the closest match (between the single record in
TableA and all the records in TableB, or between any record in TableA
and any record in TableB if you have multiple TableA records). You can
set the Top Values property of the query to 1 to show only the closest
match. Note that there might be ties (two records equally close in
this ten-dimensional space).


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Would I still do it the same way you suggested? How do I create the quesry
if the new set only exists in 10 search fields and not in the database as a
record?
Click to expand...

You could use explicit form references; there would be no self join,
just a single table query. The expression would be something like

Sum: ([TableA].[A] - [Forms]![FormName]![txtA])^2 + ([TableA]. -
[Forms]![FormName]![txtB])^2 + ...


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thanks!

That should do it.

Best,

Tomas

"John Vinson" skrev:
Would I still do it the same way you suggested? How do I create the quesry
if the new set only exists in 10 search fields and not in the database as a
record?
Click to expand...

You could use explicit form references; there would be no self join,
just a single table query. The expression would be something like

Sum: ([TableA].[A] - [Forms]![FormName]![txtA])^2 + ([TableA]. -
[Forms]![FormName]![txtB])^2 + ...


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top