Results 1 to 14 of 14
  1. #1
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    59

    Excel to Access Vlookup

    I have the below in excel.



    In excel we are able to select a value from the top row using one combo box and then a second combo box listing the first column, the excel then runs a vlookup and pulls the relevant risk.

    It goes even further because there is a third combo box that lisis either yes or no and changes focus from this table to another one with modified risk values.

    We now want to do this in access, but Im not sure of the best way to put this together.

    Please help.

    Click image for larger version. 

Name:	Access from Excel.png 
Views:	47 
Size:	41.4 KB 
ID:	44719

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,757
    In Excel, data is arranged in columns; in Access it is in rows. Row data is compartmentalized (tables) and relationships are designed so that table data can be grouped as required. If Access is a hammer, Excel is a screwdriver and you should not design as if they were the same. If you do, you will only struggle in Access most of the time. There really is no equivalent to V or H lookups in Access (at least not to my mind) because you shouldn't need them if your db is properly normalized. You might benefit from researching db normalization to see if you got off on the wrong foot.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    636
    Hi Steven

    For the process to work you would need a table to store the categories and then a related table to store the values associated with the category selected.

  4. #4
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    59
    So

    I created a table for the row at the top called tblProductRisk
    I created a table for the column down the side called tblTransportRisk
    Then I have another table called tblTransportArrangement, basically a yes or no
    Then I have a table called tblRiskLevel which lists the different risks.

    I created table tblRiskAssessment to bring all this together. as below,

    in tblKYC I have created a field where I want to store RiskLevelID, so the resul will be Low or Medium or High, but it depends on the criteria.

    If the user wants a Spirits (selected from a Combo Box)
    Then Wants the goods moved Duty Paid Removals From Warehouse
    And he wants the company to arrange the movement, the overall risk is Low

    I thought a DLookup, would be a way to go, so :

    DLookUp("RiskLevelID","tblRiskAssessment","[tblRiskAssessment].ProductRiskID=[tblKyc].PlannedMovementofGoods" AND "[tblRiskAssessment].TransportRiskID=[tblKyc].DealSummary" AND "[tblRiskAssessment].TransportArrangementsID=[tblKyc].SummaryWillSeabrooksArrangeTransport")

    But with only a few months ok knowledge Im not sure how to relate the combos
    And the the result go to the table.


    Click image for larger version. 

Name:	Relationships.png 
Views:	42 
Size:	46.7 KB 
ID:	44737

  5. #5
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    636
    Hi Steven

    You need to stop and read up on the normalisation of tables.

    Your tblKYC screenshot attached shows how your table is not normalised.
    Attached Thumbnails Attached Thumbnails KYC.JPG  

  6. #6
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    59
    Hi Mike

    I truly do understand the normalisation of tables and I'm not trying to ignore expert and experienced advice and have dessigned stuff correctly before.

    I am putting something together that I need to do very quickly, I'm three months into the year and have a moster workload and need someting to work and carry my work to the end of the year so I can slowly develop somthing over the next 9 months that is designed correctly.

    My Director is not funding the project and wants something that it illeterate people can see and use.

    I just need specific technical components I do not yet know to be developed so I can adapt them later on, once I understand the basics I can run with it and build something better.

    Unfortunatly I'm picking up a mess of work from a colleague who has gone and need to get on top of it before this area of work implodes and causes my work to skyrocket to stressful levels.

  7. #7
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    636
    Hi Steven

    The longer you leave sorting this the more difficult it will be later down the road.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,757
    You are making this way too complicated and what I don't understand is if you really understand normalization why did you create a db like the one you have? If you didn't create it then why compound the problem by making 4 more tables when at most you should have made 1? If risk is the entity, then tblRisk should contain fields for PK, RiskType (values Product, Transport, etc.), RiskLevel (Low to High) and RiskValue (looks like values 1 to 5). I sympathize with your situation and it should be apparent by now that there really isn't any such thing as design short cuts that will save you time and effort. That you are here and stuck should be evidence of that.

    Perhaps if you copy, compact, zip and post a db here you will get somewhere but no promises. If your work is too complicated to follow, posting a db may not result in a solution.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,505
    I agree with Micron, post whatever you have AND an overview of requirements in simple, plain English. This is likely to get you a workable solution more efficiently than building some temporary "thing" and trying to build it correctly later.
    We've seen the "I'm too busy chopping wood to stop and sharpen the axe" syndrome before. And routinely that means, we'll work with whatever gives an answer --even if we have to create work arounds each time.

  10. #10
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    59
    I'll normalise my tables first and then come back to you.

  11. #11
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    59
    I've got round to developing this database a bit more and have now normalised my tables.

    Therefore they are all now much longer, but narrow, and the relationships created have helped me create a much more complex but to me better database. Thank you for the advice.

    However I still have the above issue I need help with.

    So the table above in colour, in excel the user selects combo box 1 to select the column, and then uses a second combo box to select the row and a v look up provides the value. It actually goes a bit further and there is a modified version where selecting a value from the 3rd combo box changes the vlookup to a second table to displays the value in an assigned field.

    I was thinking that to do this in access is more complex. I need ideas how best to build this in access. Bearing in mind that the combo box for the column will be on a different form that the combo box for the row.

    Can you guys help?

    Regards

  12. #12
    mike60smart is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    636
    Hi Steven

    Can you upload a zipped copy of the database?

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,757
    You have added your relationships to post 4 and that is what you are referring to as "the table above"? I created a db in Access 97 that had a grid on a form like that in your first post. It was about 20 years ago and I probably still have it somewhere but wouldn't be able to open it now to see how it was done. I'll have to think about it a bit, or if you're not in a real hurry I'll look for it on CD's and see if I can get someone with an older version than me who can convert it.

    As I recall, it is a form with labels across and down with the text you show. Using "aspect" as header label and "risk" as row label (because I don't know what you call these things):
    A grid of textboxes below/beside, formatted to look as you like. Each aspect and risk factor is assigned a suitable number in your tables and the product of these numbers is assigned a word such as "Low". This word ends up being the value in the appropriate control. Grid colour is controlled by conditional formatting based on the word. That's all for now. Need more coffee - brain is slowing down.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  14. #14
    Steven19 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    59
    Quote Originally Posted by Steven19 View Post

    Click image for larger version. 

Name:	Relationships.png 
Views:	42 
Size:	46.7 KB 
ID:	44737
    I um unable to zip the file size as it is now too big.

    However I have to file structure above, and the data contained within, see here. Click image for larger version. 

Name:	Screenshot (15).jpg 
Views:	12 
Size:	247.3 KB 
ID:	45129

    So to simplify.

    My manager enters data into a form, and the data is entered into tblKYCDeal, and in that form he should be able to select ProductRisk from a drop down, then he will later open a different form and update tblKycTransport and then from a dropdown select tblTransportRisk and tblTransortArrangements, and this will give the result in tblRiskLevel (In a way its life a filter table, just spread over two frms) so what I need is a way to best achieve this. I'm having to go with someone else's design preferences.

    Also, when selecting data for the lookups would where would the source data come from to do that?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access equivalent of excel Vlookup
    By oldlearner in forum Access
    Replies: 8
    Last Post: 06-17-2019, 06:58 AM
  2. Help with Excel vlookup alternative in Access
    By kozatchek in forum Access
    Replies: 2
    Last Post: 05-12-2017, 02:12 PM
  3. Replies: 1
    Last Post: 02-21-2014, 04:23 AM
  4. Access and the concept VLOOKUP in Excel
    By Bob Blooms in forum Access
    Replies: 1
    Last Post: 08-27-2012, 12:28 PM
  5. Excel VLookup vs Access IIF statement
    By Huddle in forum Queries
    Replies: 9
    Last Post: 02-02-2012, 11:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums