Re: Query with mathematics criteria!!?? Help!
From: Tomas (Tomas_at_discussions.microsoft.com)
Date: 11/29/04
- Next message: Jerry C.k: "Re: Differences the date by time"
- Previous message: Nikos Yannacopoulos: "Re: Using values from a message box"
- In reply to: John Vinson: "Re: Query with mathematics criteria!!?? Help!"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 01:49:02 -0800
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:
> On Sun, 28 Nov 2004 20:27:01 -0800, "Tomas"
> <Tomas@discussions.microsoft.com> wrote:
>
> >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.
>
> >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?
>
> ......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].[B] - [TableB].[B])^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
>
- Next message: Jerry C.k: "Re: Differences the date by time"
- Previous message: Nikos Yannacopoulos: "Re: Using values from a message box"
- In reply to: John Vinson: "Re: Query with mathematics criteria!!?? Help!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
