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    
 
   
RJD
post Mar 2 2018, 10:27 PM
Post#21


UtterAccess VIP
Posts: 9,240
Joined: 25-October 10
From: Gulf South USA


Hi again: I had an idea for some code, so went ahead and built a procedure to write a new table of the junction values. Looks like it works fine, and produces the same results on the test data as the query approach. But I think this one will go MUCH faster on the large file you described. No Cartesian products, no re-looking at values already matched to the original value, no looking beyond one record after the one with the minimum difference. And it selects the larger value when two values have the same difference from the value to be matched.

See how this works for you. The procedure deletes the records in the junction table, then reconstitutes the list. If you want to see the results from scratch, just delete the records from the junction table manually and then run the procedure. Compare to the SQL approach for results. Then run against your larger tables.

HTH
Joe
Attached File(s)
Attached File  UnequalJoin_Rev2.zip ( 27.37K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Mar 2 2018, 10:28 PM
Post#22


UtterAccess VIP
Posts: 9,240
Joined: 25-October 10
From: Gulf South USA


Oh, and here's the code for those who might be interested but don't want to download the file ...

CODE
Public Function MakeJunction()

Dim db As DAO.Database, rstVal As DAO.Recordset, rstMatch As DAO.Recordset, rstOut As DAO.Recordset
Dim PreviousValue As Double, CurrentValue As Double
Dim PreviousAve As Double, CurrentAve As Double
Dim PreviousDiff As Double, CurrentDiff As Double

CurrentDb.Execute "DELETE * FROM TblResults", dbFailOnError

Set db = CurrentDb
Set rstVal = db.OpenRecordset("SELECT * FROM ValTbl ORDER BY Value_")
Set rstMatch = db.OpenRecordset("SELECT * FROM LnkTbl ORDER BY AveVal")
Set rstOut = db.OpenRecordset("TblResults")


rstVal.MoveFirst

rstMatch.MoveFirst

NextValue:

PreviousValue = rstVal!Value_
PreviousAve = rstMatch!AveVal
PreviousDiff = Abs(rstVal!Value_ - rstMatch!AveVal)

rstMatch.MoveNext
CurrentAve = rstMatch!AveVal
CurrentDiff = Abs(PreviousValue - CurrentAve)

NextCheck:

If PreviousDiff < CurrentDiff Then

rstOut.AddNew
rstOut!Value_ = Round(PreviousValue, 2)
rstOut!AveVal = Round(PreviousAve, 2)
rstOut!ValDiff = Round(PreviousDiff, 2)
rstOut.Update

rstVal.MoveNext

If rstVal.EOF Then GoTo Outahere

GoTo NextValue

Else

rstMatch.MoveNext
PreviousAve = CurrentAve
CurrentAve = rstMatch!AveVal
PreviousDiff = CurrentDiff
CurrentDiff = Abs(PreviousValue - CurrentAve)

GoTo NextCheck

End If

Outahere:

rstVal.Close
rstMatch.Close
rstOut.Close
Set rstVal = Nothing
Set rstMatch = Nothing
Set rstOut = Nothing

End Function

Go to the top of the page
 
firlandsfarm
post Mar 8 2018, 12:43 PM
Post#23



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


Hi Joe and thanks for all that code, wow! I'm sorry for the delay in getting back to you but I have been a little busy now the snow has gone if the South of England ... I hope to have a look this weekend.
Go to the top of the page
 
RJD
post Mar 8 2018, 02:59 PM
Post#24


UtterAccess VIP
Posts: 9,240
Joined: 25-October 10
From: Gulf South USA


No problem on the delay - your schedule is the driver, of course.

Do let us know if this speeds up the processing - as I expect it will, dramatically, since it does not have to use the Cartesian approach and does not repeat a complete search, but rather just steps to the next check point.

Look forward to hearing from you ...

Joe
Go to the top of the page
 
Dave21495
post Mar 15 2018, 05:03 PM
Post#25



Posts: 406
Joined: 9-April 03
From: Los Angeles, CA


You do it easily with a sub query or two queries

You have two tables, TblScores that contains the list of test scores and tblGrades that contains the number that corresponds with the minimum score for each grade and the grade letter. 0 = F, 60 = D, 70 = C, 80 = B, 90 = A

Query1 = SELECT tbl_Scores.Score, tbl_Grades.GradeNumber
FROM tbl_Grades, tbl_Scores
WHERE tbl_Grades.GradeNumber<=[Score]
ORDER BY tbl_Scores.Score;


Query2 = SELECT Query1.Score, Max(Query1.GradeNumber) AS MaxOfGradeNumber, Last(tbl_Grades.[Grade Letter]) AS [LastOfGrade Letter]
FROM Query1 INNER JOIN tbl_Grades ON Query1.GradeNumber = tbl_Grades.GradeNumber
GROUP BY Query1.Score;

Go to the top of the page
 
firlandsfarm
post Mar 16 2018, 11:53 PM
Post#26



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


Hi Dave, thanks for your input but I'm a little confused by it. frown.gif Was it misposted? Yours sounds like a reference to GCSE exam results!
Go to the top of the page
 
firlandsfarm
post Mar 17 2018, 12:28 AM
Post#27



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


Joe, a week late, sorry after your efforts, but I eventually looked at your solution and ... OK I must first expose myself as not having much of a clue as to what to do with it! I have never used code in Access before so I don't know if I am causing a problem!

I opened the UnequalJoin2 database I posted here and selected Visual Basic in the Database Tools tab. I then clicked Insert:Module and pasted your code over the "Option Compare Database Option Explicit" prompts (I figured if they were needed they would be in your code smile.gif ) and clicked "run". I received an error that "TblResults" could not be found. I wondered if your TblResults is my ValTbl so copied it and named that TblRes (being mindful that your code says "DELETE * FROM TblResults") but that just took me to another error message "Run-time error '3265' Item not found in this collection" in respect of the line "rstOut!AveVal = Round(PreviousAve, 2)" ... being completely out of my depth that's when I decided I needed to add this post!

BTW, my concerns over TblResults were founded, the table is now empty so I'm assuming I need a table of Results in being probably to receive the results of your code and you delete it's contents before returning the results of running the code.
Go to the top of the page
 
RJD
post Mar 17 2018, 09:22 AM
Post#28


UtterAccess VIP
Posts: 9,240
Joined: 25-October 10
From: Gulf South USA


Hi: Looks like you did not download the db revision containing all objects necessary to run the module code (see Post #21). Try that first. And see how the module is set up and how the extra table is designed.

I have not made a complete database out of this, so open the module, place the cursor before "Public" and hit the F5 key. This will run the procedure. Then look at the results in the new table.

To put this in your db, just copy the extra table and the module from the demo to your db. Also make sure that your "real" db has the same object names (tables and fields) as the example you posted. Then open the module and run it (as above). See if this gives you what you want and is efficient enough for you.

If this is what you want, you can incorporate a call to the new procedure into a form somewhere appropriate, or perhaps call it somewhere else. I do not have your entire db, just your two tables, so I do not know where that might be done.

HTH
Joe

Go to the top of the page
 
firlandsfarm
post Mar 17 2018, 10:53 AM
Post#29



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


yes Joe, I did miss the revised DB, sorry.
Go to the top of the page
 
RJD
post Mar 17 2018, 10:57 AM
Post#30


UtterAccess VIP
Posts: 9,240
Joined: 25-October 10
From: Gulf South USA


No problem. Work with that and let us know if you need further guidance ...

Regards,
Joe
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 01:07 AM