UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Access Equivalent Of Excel's 'vlookup' Function, Access 2016    
 
   
firlandsfarm
post Feb 24 2018, 02:19 AM
Post#1



Posts: 289
Joined: 28-April 02
From: Heathfield, England


I want to do a Join that behaves as Excel's VLOOKUP function set to TRUE i.e. it looks for the best approximate match and not just an exact match.

I have a link table with average values in a one field [LnkTbl].[AveVal]. I want to Join to [AveVal] from a specific value held in another table [ValTbl].[Value] which will not be an exact match in 99% of records ... I want to Join on 'best approximate match' as Excel might put it when using VLOOKUP([LookupValue],[LookupRange],[LookupCol],TRUE). So something like ...

[LnkTbl] MY JOIN [ValTbl] ON [LnkTbl].[AveVal] = [ValTbl].[Value]

Now I know that won't work, I am simply using "MY JOIN" to illustrate the Join I am looking for.

Is this possible in Access Design View, SQL or just (maybe) only VBA (that's in order of preference based on my knowledge of each instruction set!).

I have thought about constructing another Link Table between [ValTbl] and [LnkTbl] but thought I would check to see if anyone knows of a more direct way of doing it.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
DanielPineault
post Feb 24 2018, 06:33 AM
Post#2


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



SQL joins are exact matches. You'd have to look at some type of LIKE criteria...



SQL
SELECT * FROM YourTableName WHERE [SomeFieldName] Like '*SomeValue*';

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
zaxbat
post Feb 24 2018, 06:46 AM
Post#3



Posts: 934
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Your attempt to locate approx values near your target value is not done in the link statement but it can be facilitated in SQL in the WHERE clause of the SQL statement.

Would need more details from you in order to build the SQL that you are looking for.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Jeff B.
post Feb 24 2018, 08:28 AM
Post#4


UtterAccess VIP
Posts: 10,051
Joined: 30-April 10
From: Pacific NorthWet


You are describing (?proscribing) a "how", as in how you want to do something.

If you describe a bit more "what" that is, that is, what you're trying to accomplish, folks here may be able to offer more suggestions.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
GroverParkGeorge
post Feb 24 2018, 09:18 AM
Post#5


UA Admin
Posts: 32,819
Joined: 20-June 02
From: Newcastle, WA


Are the values to be "searched" alphabetic or numeric.

You can, of course, implement either, but knowing more about the data might help focus in on a potential suggestion or two.

--------------------
Go to the top of the page
 
firlandsfarm
post Feb 25 2018, 11:54 PM
Post#6



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Hi George, they are numeric values.

Sorry Jeff B. but I thought I described both a How and a What! The situation is a simple everyday thing with a twist. I have two tables both have data and are inked by an inexact relationship i.e. the two linking fields will not always be equal in which case I want the link to be made with the closest match so that the data in the tables can be married.

Zaxbat, I don't know what else I can provide you with ... to me it's a simplce scenario. What sort of info. would help?

Daniel, I'm not sure that will help, see my reply to George above. Doesn't Like apply to strings and not numbers?

A Simple Example
I have a value of [ValTbl].[Value] = 1.50 that I want to have a relationship with with the value [LnkTbl].[AveVal] but the close values of [LnkTbl].[AveVal] are ...

1.45
1.49
1.52
1.54

There is no [LnkTbl].[AveVal] of 1.50 so I want it to join with the value 1.49 as the closest ... as I explained in my original post this is what the Excel VLookUp function would do when the TRUE parameter is set.



--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RJD
post Feb 26 2018, 12:25 AM
Post#7


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi See if this demo does what you are trying to do ...

Here's the SQL used ...

SELECT
O.ID,
O.TheValue,
ABS([Forms]![frmTestValue]![TestValue]-O.TheValue) AS DiffTest
FROM tblMyRecords AS O INNER JOIN (SELECT Min(ABS([Forms]![frmTestValue]![TestValue]-[TheValue])) AS DiffTest FROM tblMyRecords) AS T
ON ABS([Forms]![frmTestValue]![TestValue]-O.TheValue) = T.DiffTest;

There is no "approximate/closest" built-in function that I know of in Access, so you have to create your own approach ...

HTH
Joe
Attached File(s)
Attached File  CloseValues.zip ( 21.32K )Number of downloads: 7
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Jeff B.
post Feb 26 2018, 07:59 AM
Post#8


UtterAccess VIP
Posts: 10,051
Joined: 30-April 10
From: Pacific NorthWet


So, are you saying you want to find a record in Table B that has a value in field/column X that is "closest" to a value from Table A, field/column Y? And by "closest", do you mean the minimum absolute value difference between the two values?

(my "what" comment was pointed more at figuring out what real world impact having the matchup would facilitate)

Good luck!

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
firlandsfarm
post Feb 26 2018, 08:36 AM
Post#9



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Jeff, the real world impact would be the same as probably any join, it will enable me to link the data from two tables to create a relationship. And yes, I suppose I do mean the minimum absolute difference between the two values or to put it another way ... the closest in accordance with the mathematical principle of rounding up and down. Anyway, it really does seem to be much more difficult than I thought it would be so I have now created a link table to marry the two values by using the VLOOKUP function in Excel which was a doddle but will require maintenance of values which will be inconvenient! Thanks for your interest.

Joe (AKA RJD): thanks for your suggestion I will try it out and see if it works for me, it will save me from having to maintain the values in the link table.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
zaxbat
post Feb 26 2018, 10:50 AM
Post#10



Posts: 934
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Actually, not difficult at all to do.....just need the 'Tolerance' amount +/-
If you will have variable tolerances...then the tolerance amount needs to be saved in a table someplace, otherwise it can be hardcoded.
If each record may have a different tolerance (or even a dynamic tolerance that must re recalculated on each run) that is also doable.
None of it is difficult....but do need to know the spec.
This post has been edited by zaxbat: Feb 26 2018, 10:51 AM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Jeff B.
post Feb 27 2018, 08:14 AM
Post#11


UtterAccess VIP
Posts: 10,051
Joined: 30-April 10
From: Pacific NorthWet


I'll try once more ...

Having a way to link tables together, or performing a VLOOKUP() in Excel, is a means to an end. Unless you're writing a book/teaching a class, those (in my world) represent "how". If you were to explain to an eighty-year-old grandmother "why" you want to do this, what would you tell her?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
firlandsfarm
post Feb 27 2018, 08:49 AM
Post#12



Posts: 289
Joined: 28-April 02
From: Heathfield, England


And I'll try once more Jeff ... I would tell my 99 year old mother that I want to be able to link the data from two tables to create a relationship. WHY? Because I can then produce reports based on the combined data of the two tables.

I'm clearly missing something here. I really don't understand what you are driving at. I'm clearly not answering your question as you want me to but I can't see any other answer I can give! HOW? That is clearly what I am asking. And to confirm/explain further I stated that I effectively want to achieve the same as the VLOOKUP function in Excel. I really cannot see anything wrong in what I am saying.

I have given a Why, I have asked How and in an attempt to clarify I have given a What example.
This post has been edited by firlandsfarm: Feb 27 2018, 08:54 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post Feb 27 2018, 01:14 PM
Post#13



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Hi Zax, I'm not sure what you mean by the spec so i have attached a database with 2 tables each consisting of the fields to be linked (and kept to the original nomenclature). So the field [ValTbl].[Value_] is the dynamic field that is updated with fresh data and contains a random sample of the values in that field and the field [LnkTbl].[AveVal] is the static field that [Value_] needs to be linked/matched with. The range of values and actual values in LnkTbl is complete, they will not change. You will see that the difference from one value of [AveVal] to the next is in non-uniform steps.

As I have said earlier I can (and have for now) created a static link table but would have to maintain it manually as new values are added to ValTbl. I'm guessing I would do this by grouping all the values for [Value_], compare them against those already in a link table to find the orphan values and adding them. I can then manually (or through Excel) add the corresponding [AveVal] values on the other side of the link table. The range of values and actual values in LnkTbl is complete, they will not change.

Attached File  UnequalJoin.zip ( 18.47K )Number of downloads: 5

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Jeff B.
post Feb 27 2018, 02:27 PM
Post#14


UtterAccess VIP
Posts: 10,051
Joined: 30-April 10
From: Pacific NorthWet


Thank you. You want to be able to create a report (you must have an exceptional mother if she gets relational database tables and joins!). The data you want in the report uses data from more than one source/table.

?Did I get it?


--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
firlandsfarm
post Feb 28 2018, 12:50 AM
Post#15



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Jeff, I really don't understand why this has become so difficult to communicate. I think you are now refering to an Access Report ("a report") ... I don't want that! I want the database to report the data not prepare a Report. My request is and has always been a very simple request ... how can I create a join between two tables on a 'best match' basis when the comparitive values are not equal. I just want to know how I can join two tables within the conditions I have stated. Once I have the tables joined I will futher process that data. If that's not possible then fine, it's not possible. If that's beyond the scope of this forum then fine it's beyond the scope of this forum ... I'm happy to accept both.

Anyway, I think it's time for me to thank you for your good intentions. This is wasting time for both of us and probably causing as much frustration within you as it is with me and should not be allowed to waste any more. So, thank you for your attempts to help but I think you and I should leave it there.
This post has been edited by firlandsfarm: Feb 28 2018, 01:16 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RJD
post Feb 28 2018, 07:26 AM
Post#16


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi again: Based on the last few posts you may well not be looking at this again, but attached is a possible solution, based on the conversations above and my assumptions... (note that your zip file indicates that it is not in any recognizable format and cannot be opened with WinZIP, so I do not know the contents) ...

My approach assumes that there will be groups of data in both tables (one in one table, many in the other, for each group), with a matching group number between tables, just no direct link between the base value and the related min diff value. The approach looks for the min difference in each group and supplies a single final result for each group, with fields from both tables. Essentially, it looks at all combinations in the group, then throws away those that are not the min in the group.

Although there are three queries in this solution, it could be reduced to one using subqueries.

See if this is close to what you are trying to do.

Sorry for any communications issues that went on before in this thread ... knowing the participants, I am sure that no rudeness was intended.

HTH
Joe
Attached File(s)
Attached File  CloseValues_Rev1.zip ( 22.96K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
firlandsfarm
post Feb 28 2018, 10:33 AM
Post#17



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Hi Joe, thanks for joining in. I was probably just being a bit thick before, I just couldn't see what Jeff was wanting. I didn't think there was any rudeness I was just getting more and more frustrated. It's behind us.

I've tried the zip thing again and it seems the original version was corupted in some way beacuse this version is different in size.

Attached File  UnequalJoin2.zip ( 21.95K )Number of downloads: 3

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RJD
post Feb 28 2018, 11:58 AM
Post#18


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi again: Well, I wasn't completely sure which way the link was supposed to go ...

1. Starting with records in ValTbl and finding the closest value(s) in LnkTbl to them.
OR
2. Starting with records in LnkTbl and finding the closest value(s) in ValTbl to them.

So I assumed option 1. and built around that.

See your modified db attached and see if this is what you are trying to do. While there are 46 records in ValTbl there are 48 matches - since two of the records in ValTbl are equal min distance from two records in LnkTbl. I didn't know how you wanted to handle that, so left it as-is. It could be handled with a Totals query selecting the lowest or highest comparative number (record) but I'll leave that for further consideration.

In looking at the results, first see qryFinalListWithMinDiffs (this is your junction table now). Then you can work your way back from there to see how it builds up. Once again, you could make this one query using subqueries, but separate queries at this stage will allow you to see how the results build up, and better see if this is what you actually want.

HTH
Joe
Attached File(s)
Attached File  UnequalJoin_Rev1.zip ( 24.2K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
firlandsfarm
post Mar 2 2018, 01:24 PM
Post#19



Posts: 289
Joined: 28-April 02
From: Heathfield, England


Yes, as I said in the initial post "I want to Join to [AveVal] from a specific value held in another table [ValTbl].[Value]" so you are the right way round.

To solve the duplicate value situation I've Grouped the 3rd query and chosen Max for AveVal ... somewhere in the correspondance I suggested if a tie then I would treat it the same as mathematics for Rounding i.e. taking the value 31 ... it 'matches' with both 30 and 32 so take 32 as the rounded match ( .5 gets rounded up).

I can see what you have done ... compare [Value_] with [AveVal], find the smallest difference, match the smallest difference back to find where it matches with [Value_]. I've done similar before, just didn't see it this time.

My only thing now is to test it against the real data where the [ValTbl].[Value_] table has 350 records and the [LnkTbl].[AveVal] table has 1,595.269 so the "QueryAllCombinations" will produce over 500,000,000 records! let's see how that runs, it maybe I will use your queries to build a link table and then have an append query so that as new records are added it appends new values to the link table.

I'll report back, probably tomorrow. Thanks for your interest ... much appreciated.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
RJD
post Mar 2 2018, 08:07 PM
Post#20


UtterAccess VIP
Posts: 8,360
Joined: 25-October 10
From: Gulf South USA


Hi again: ...

QUOTE
My only thing now is to test it against the real data where the [ValTbl].[Value_] table has 350 records and the [LnkTbl].[AveVal] table has 1,595.269 so the "QueryAllCombinations" will produce over 500,000,000 records!

Okay, wow, that's a lot! I was not aware of the large number of records, given your examples. I'll wait for your tests, but I think this can be done with VBA, and that should greatly shorten processing, dealing with one record against the list, and stopping when the difference notches up, then writing the junction record to a new table, and moving on to the next record for comparison, without restarting the second list.

If your tests don't work out to your satisfaction, let me know and we can look at a VBA solution.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 02:30 PM