finding the Closest match

vaidehikumar

Registered User.
Local time
Yesterday, 21:09
Joined
Apr 17, 2012
Messages
25
Hi

I have a table called Fish Data with anglers name and fish weight. I want to run a parameter query where the user will enter fish weight and the query should bring the closest match from the Fish data table. How do you get the closest number match. The fish weights are entered with two decimals like 2.54. Thank you for your help
 
This type of thing comes to mind:

SELECT TOP 1 *
FROM TableName
ORDER BY Abs(DesiredWeight - FishWeight)

Where DesiredWeight is the parameter entered and FishWeight is the field in your table.
 
Thank you for your reply. It worked, however I do not want other fields to show up in the ressults. I have angler name, different fish types, their wt. I want only the angler name and mystery wt field to be returned. I want only angler name and the Mystery weight to show in the results.

Here is the qry I used (the one you gave) and the results. Thank you again for your help

SELECT TOP 1 *
FROM [Fish Data]
ORDER BY Abs([enter fish weight] - [Mystery wt])
Query1Record IDAngler NameRed FishTroutFlounderKing MackerelSpanish MackerelSpotsMystery Wt8Wright,Eric4.205.307.002.609.208.002.50
 
So specify those fields in the SELECT clause:

SELECT TOP 1 AnglerName, [Mystery wt]
FROM [Fish Data]
ORDER BY Abs([enter fish weight] - [Mystery wt])

By the way, the spaces in your names will prove more trouble than they're worth in the long run.
 
Hi pbaldy,

What are the reasons for not using spaces in field names?

I know that when writing code you end up having to enclose field names with spaces using square brackets. This alone will become quite tedious over time!

I have used CamelCase for field names for a long time, but am just interested.
 
That would be enough for me! That's probably the major reason. They are typically put in the same category as reserved words and symbols. You can get away with them, but they'll often bite you where it counts. :p
 
Thank you Paul. It worked. I'll remove the spaces in the field names. I should have known better. Thank you again.
 
Really sorry to bother you again, If I use a form to get the mystery wt, how to pass that value into this query to use in the enter parameter field.
 
Instead of [enter fish weight]:

Forms!FormName.TextboxName
 
Thank you, but it didn't work.
Here is the code:

SELECT TOP 1 AnglerName, [MysteryWt]
FROM [Fish Data]
ORDER BY Abs([Forms!Enter Mystery Weight. Mystery Wt]-[MysteryWt]);

Error message says,

" is not a valid name. Make sure itdoesnot include an invalid name, punctuation that it is not too long.

My form name(Enter Mystery Weight) and the text box name (Mystery Wt) are correct. I double checked it.
 
Well, nobody mentioned brackets around the whole bit, which makes Access think it's a field name. You need it like I have it, but with brackets around the form name and around the text box name, since they both have the spaces. ;)
 
I changed the form name and txtbox name to without spaces, Query ran, but returned all the entries instead of closest match. Here is the code:

SELECT TOP 1 [Fish Data].AnglerName, [Fish Data].[MysteryWt]
FROM [Fish Data]
ORDER BY Abs(Forms!EnterMysteryWeight.MysteryWt-[MysteryWt]);
Thank you.
 
Query1AnglerNameMysteryWtBaggs,Wade1.30Shelton,Craig7.00Jolly,Doug 5.00Sweeney,Robert1.10Engle,Tina12.00Dyer,Jim5.70Thayer,Jason4.30McDow,John7.80Wright,Eric2.50Richardson,Douglas1.20Davis,Brianna2.80Armstrong,Robin7.20Bastura,Patrick3.40Allen,Bobby2.80
 
Make sure you've tabbed out of the text box on the form. If that's not it, can you post the db here?
 
The query is only returning one record for me (Dyer, Jim 5.70). Did you make sure to tab off the textbox?
 
Sorry I didn't understand when you say tab off the txt box. Are you saying tab space after the form name?
When I use the form to enter wt and open the query 1 , it has all the records.
 
I mean to hit the tab or enter key after entering a value so focus leaves that text box, or at least the value gets committed. If you just type in a number and then run the query, the value in the text box will not be seen.
 

Users who are viewing this thread

Back
Top Bottom