Data matching within 10 integers - query

[natalie99]

Hi peeps!

I need to create a query that says two fields match if the numeric values are within 10 units of each other. I.E. not an exact match.

I do not want to 'round', as items could round in opposite directions eg.

15,481.2 and 15,474.5 are within ten increments of each other, but if I round to the nearest ten integers:

15,480 and 15,470 won't come up as matches.

Could someone pretty please detail the SQL to do this using query design layout if possible?

Any help will be very much appreciated :)

Nat

[Stewart Ross]

Hi peeps!

I need to create a query that says two fields match if the numeric values are within 10 units of each other. I.E. not an exact match.

I do not want to 'round', as items could round in opposite directions eg.

15,481.2 and 15,474.5 are within ten increments of each other, but if I round to the nearest ten integers:

15,480 and 15,470 won't come up as matches.

Could someone pretty please detail the SQL to do this using query design layout if possible?

Any help will be very much appreciated :)

Nat

Hi Nat. Rounding the values using the normal Round function (and not your interpretation of it!) is as good a way to go as any. You only need to round the values to a whole number, not to the nearest ten. The two values listed are 15,481.2 and 15,474.5, which round to 15,481 and 15,475 - and these rounded values are indeed within ten units of each other.

You don't tell us your table or field names, so you'll need to fill these in yourself in the generalised SQL below:

SELECT value1, value2 FROM sometable WHERE Abs(Round(value1, 0) - Round(value2, 0))<=10;

The Abs function returns the absolute value of a number (a positive value) regardless of whether it is positive or negative.

-Stewart

[natalie99]

Hi Stewart

Thanks for the code!

SELECT value1, value2 FROM sometable WHERE Abs(Round(value1, 0) - Round(value2, 0))<=10;

I was unsure how to create the plus/minus 10 section, so the Abs function is perfect! So now in my testing only 1 of 48,000 records won't price, and it's a spelling mistake, woohoo success!

You may be able to help with my new problem, (sorry for the assumption!) now I have an auto-pricing inventory tool which exports the results to file etc etc etc.

The last thing I would like to do is either:

a) write an autorun at startup Macro to load the file to be priced into an existing table, (replacing current data in the file, the template is set up and will always match)

or

b) create a form with a borwse button and select a file to load in to be priced(same file, different method and data still needs to be replaced not added)

Any thoughts or suggestions would be lovely, I am such a db novice it is scary :)

Thanks again

Nat

[Stewart Ross]

Hi Nat. I'm delighted you found the SQL solution useful.

On your new query it is better etiquette to create a new thread for a new question that does not relate directly to the old one.

Having said that, there are many possible options you could use to import data. You could use a macro to automate the process of importing an Excel file, say, or you could use a command button on a form to run the DoCmd.TransferSpreadSheet method in VB to do this for you. There are many possible options, but as you provide no details of the file format, file name, location, or contents I can only answer in the most general of terms.

In my opinion the simplest option for a novice is to check out the import options available via File, get external data, then create a macro to automate the process for you, and run this macro when you start the database. You could also consider linking to an Excel sheet (which would have to be the same name and location every time it is re-exported), instead of importing the data each time. With linked rather than imported tables you only have to set up the link once and the linked data is there each time you open the database thereafter.

-Stewart