Column Closest number

[editolis]

Hi All,

I Need a help with a qry.

I have 2 tables and they are connected by nID. The table 2 is made by previous qry.

The first table have 200 rows and the second have 50.000 rows.

Each one have a column named DiffA with double numbers like: 2,02 – 7,89 – 15,01 etc…

I Want from the table 1 to find the 3 closest numbers from the table 2 for all 200 rows (nID's).

For example table 1 with nID: 100 and DiffA: 3,04

The result must be from table 2 with nID: 100 to show me 3 results like: 3,04, 3,03 and 3,05. (if exist) or (if they don’t exist) the closest numbers like 3,08 etc.

I Think that I need a Public Function but I don’t know how to do it.

Thank you in advance.

 

[Galaxiom]

Try this untested idea. I think it should provide up to three records for each nID.

Create a query from the two tables with a join on nID.

Derive a field to find the difference between the values in each table. Abs() finds the magnitude of the difference disregarding whether negative or positive. Insert this field three times. Lets call them Difference1, Difference2 and Difference3.

DifferenceX: Abs(table1.DiffA-table2.DiffA)

In the criteria of the Difference2 enter:
<> Difference1

In the criteria of the Difference3 enter:
<> Difference1 AND <> Difference2

Turn on Totals in the query.
Set nID, Difference1 and Difference2 to Group By and the Difference3 to Min.

Untick the Show box on Difference2 and Difference3.

Let us know how you go as I suspect this idea might need to be refined.

 

[jdraw]

Hi All,

I Need a help with a qry.

I have 2 tables and they are connected by nID. The table 2 is made by previous qry.

The first table have 200 rows and the second have 50.000 rows.

Each one have a column named DiffA with double numbers like: 2,02 – 7,89 – 15,01 etc…

I Want from the table 1 to find the 3 closest numbers from the table 2 for all 200 rows (nID's).

For example table 1 with nID: 100 and DiffA: 3,04

The result must be from table 2 with nID: 100 to show me 3 results like: 3,04, 3,03 and 3,05. (if exist) or (if they don’t exist) the closest numbers like 3,08 etc.

I Think that I need a Public Function but I don’t know how to do it.

Thank you in advance.

I looked at this earleir today and could not come up with a query to do what you asked. So I created a database (attached) that has 2 tables TblOne and TblTwo. These were filled with randomized data in currency datatype to to get some decimal places. There are 200(TblOne) and 56000 records(TblTwo).

I wrote 2 procedures MyRand to populate the tables and MinDiff3 which
gets the 3 records from TblTwo that have the same Nid as record from TblOne that have the closest values to the Diffa value in TblOne.

I used a function GetRowsOK I found in Access help to get the values.

I saw Galaxiom has responded and like hiim, I used the
Abs(TblOne.DiffA-TblTwo.DiffA) to find the closest values.

There is also a query to get the 3 values from TblTwo when you input a NID value for testing. It only does 1 Nid at a time.

Hope the attached is useful. It's a zipped mdb.

 

[editolis]

Thank you both for the help but this is not what i am looking for.

"It only does 1 Nid at a time". This is ok but i need with one qry to show me all the 3 rows from the closest number based on the nID.

So i don't want to have a parameter.

If i pull out the parameter then it show's me only the equal numbers not the closest.

 

[vbaInet]

Thank you both for the help but this is not what i am looking for.

"It only does 1 Nid at a time". This is ok but i need with one qry to show me all the 3 rows from the closest number based on the nID.

So i don't want to have a parameter.

If i pull out the parameter then it show's me only the equal numbers not the closest.

3,04 etc is not a number, it is a string. If the value was 3.04 (for example) then it would easy find your records. However one workaround could be to use the Like operator. Lots of examples on here or Access help

 

[Brianwarnock]

He is from Greece, which I think uses the comma as a decimal separator.

Brian

 

[editolis]

He is from Greece, which I think uses the comma as a decimal separator.

Brian

That's Correct Brian.

Brian i am waiting for your opinion here....

 

[Brianwarnock]

I am too long in retirement so my VBA is too rusty, but maybe JDraw will come back and tell you how to use his mindeff3 which works, except it does not print nid 3 to the immediate window.

My approach would have been to run a query similar to his test query but selecting all records, and sorted into ascending difference within nid, then used this as the record source to select the first 3 records per nid.

But the code could take me a long time and a lot of hassle.

Brian

 

[jdraw]

I am too long in retirement so my VBA is too rusty, but maybe JDraw will come back and tell you how to use his mindeff3 which works, except it does not print nid 3 to the immediate window.

My approach would have been to run a query similar to his test query but selecting all records, and sorted into ascending difference within nid, then used this as the record source to select the first 3 records per nid.

But the code could take me a long time and a lot of hassle.

Brian

Hi Brian and Editolis,

Not sure what part isn't working (" except it does not print nid 3 to the immediate window").
My understanding is
2 tables
TblOne and TblTwo. Both have structure Nid, DiffA.
TblOne has 200 records, different Nids and various Diffa values.
TblTwo has over 50000 records, many records with the same NId.

The issue is : For a Given NID (and DiffA value) in TblOne find three records in TblTwo that have the same Nid as TblOne, and whose Diffa values are closest to the value of Diffa in TblOne.

I wrote a query, with a parameter for user input of Nid. The query then finds the records in TblTwo with that Nid, it finds the Absolute value (TblOne.DiffA-TblTwo.Diffs); sorts the records on ascending value of this Abs value, then takes the Top 3 records from the group.

Since I couldn't get it working easily as a query for all 200 Nids, I decided to use vba. I used the same query, but put the query SQL inside a For Loop. That way I would do all 200 NIds (based on TblOne). Within the For loop, I use the iterator "i" to adjust the where clause (actually HAVING clause) in the query to get the next Nid.

I output the results to the immediate window as

Nid (Diffa TblOne) TblTwo.FirstValue TblTwo.SecondValue TblTwo.thirdvalue

I created test tables using random numbers just to get something representative.

 v1 = Int((5 * Rnd) + 1)    ' Generate random value between 1 and 6.
        v2 = Int((9 * Rnd) + 1)
        v3 = Int((9 * Rnd) + 1)
        SVal = v1 + (v2 / 19) / v3 'manipulation to get the Diffa value
        Debug.Print i; j; SVal
        rs2.AddNew
        rs2!nId = j
        rs2!DiffA = SVal

TblOne has 200 records - NId values 1 -200
DiffA values based on the code above.

TblTwo has 56000 records 200 * 280. (Editolis said he had over 50000, so I tried to simulate his stuff). I have 280 random records for each Nid in TblTwo.

Brian, I'm not sure what isn't working re Nid 3?
Editolis, this routine lists each Nid and value of DiffA in TblOne, and the 3 closest Diffa values from TblTwo into each line in the immediate window.

Now the immediate Window seems to roll over at 199 lines.

 

[Brianwarnock]

Hi Brian and Editolis,

Brian, I'm not sure what isn't working re Nid 3?
Editolis, this routine lists each Nid and value of DiffA in TblOne, and the 3 closest Diffa values from TblTwo into each line in the immediate window.

Now the immediate Window seems to roll over at 199 lines.

Sorry a typo it was nid1 not showing , and no wonder I couldn't find the reason, it is explained by your last line above, I didn't know that.

Brian

 

[jdraw]

Sorry a typo it was nid1 not showing , and no wonder I couldn't find the reason, it is explained by your last line above, I didn't know that.

Brian

Ya, I didn't know it either. I knew there was some limit, but if I print 200 lines and go back to the top -- line 1 is no longer there===>199??
jack

 

[editolis]

jdraw Please check out this thread for extra ideas to solve my problem:

http://www.access-programmers.co.uk/forums/showthread.php?t=149930

 

[jdraw]

jdraw Please check out this thread for extra ideas to solve my problem:

http://www.access-programmers.co.uk/forums/showthread.php?t=149930

Interesting but not quite the same as you are looking for -- in my opinion.

I think what Wayne was suggesting is to add a column in your table. In my case that would be TblTwo, and populate that field with the DiffA value from TblOne.

In my case I created a new Table TBLThree, that is the previous TblTwo with a new column X that contains the DiffA value from TblOne where the Nid values in TblThree = the Nid value in TblOne. I also added an autonumber field called Id to TblThree.

I did not get a function to do the work. (Wayne's example was looking for 1 value, not 3).

I created this query

SELECT D1.Nid, D1.X AS SourceVal, D1.Diffa, D1.ID
FROM TblThree AS D1
WHERE (((D1.ID) In (SELECT TOP 3 Keepers.ID
FROM TblThree AS Keepers
Where keepers.Nid = D1.nid
ORDER BY Abs([x]-[Diffa]);
            )))
ORDER BY D1.Nid, Abs([x]-[diffa]);

which will give the 3 Closest values of DiffA (from TblTwo) to the DiffA( I named it SourceVal ) from TblOne.

The query provides the Nid, SourceVal, Diffa (and I included the Id). However,
it reports 1 value per line in the output. Also note that it provides the 3 closest values in a Nid grouping, NOT 3 RECORDS. There can be more records where the values are in the Closest 3.

The query takes quite a bit of time to run (about 2 min on my PC) because of all the calculations.
[COLOR="[B]SeaGreen[/B]"]Note:[/COLOR]
I attached new zip. Had to delete values in TblTwo to meet AWF zip size limit.

 

[editolis]

Jdraw,
Fantastic work...
Thank you

 

[jdraw]

Jdraw,
Fantastic work...
Thank you

You are welcome. I hope it does what you need.