Calculation in a query

  • Thread starter Thread starter SueM
  • Start date Start date
S

SueM

Hi,

I have a form that collects data for a table (table1).

On that form I have a textbox which I want to have return
a value based on previous entries in 4 fields on the form
one of which relates to weight.

I can get the answer in a query, in a way in which can be
represented in a combobox for the usr to choose from, but
the values in one of the fields (weight) will be unlikely
on most occassions to be a direct match to the values
listed in a second table (table2) which contains the
values (kilojoules) which this textbox needs to extract
within it's query.

Is there a calculation I could add in to the query to ask
the query to return the weight value closest to the value
in the weight field on the form?

Many Thanks,

Sue
 
Is there a calculation I could add in to the query to ask
the query to return the weight value closest to the value
in the weight field on the form?
Click to expand...

I'd need to know more about the tables involved, but you could create
a Query with the two tables with no join line; put in a calculated
field defined as

Diff: Abs([Weight] - [Forms!formname!controlname])

Sort by this field, descending, and set the Top Values property of the
query to 1 to retrieve the record with the smallest difference.

This is going to be VERY slow if the tables are at all large. Is there
any way to modify the tables to provide a solid link?
 
Back
Top