vlookup in Access

I have 2 tables, PARTS and ORDERS.  
In PARTS, I have the Part_Number, Criteria_1, Criteria_2, Criteria_3, Criteria_4.  
In ORDERS, I have a list of orders identified by 4 criteria:  Criteria_1, Criteria_2, Criteria_3, Criteria_4.
How do I match up the specific Part_Number for each order based on the 4 criteria?  
JuJuChiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
JuJuChia,

Based on your description, I fear that you need to seriously rethink your table design.  Please explain in detail how these criteria columns are used on both tables.

Patrick
0
JuJuChiaAuthor Commented:
Sure, let me just say that I work for a semiconductor company.  And for each of their semiconductor parts, there are 4 defining criteria.  
The part numbers are very long, so sales people don't bother entering them.  
Instead, they identify each of the 4 defining criteria for the part they are ordering.  
My goal is to take their inputs and match it up with a list containing all part numbers and their perspective criteria.  I know there are lots of duplicate information, but I don't know of a better way to solve it.  
I am definately open to better, easier solution.  


0
Patrick MatthewsCommented:
So, one criterion might be "pink", just to make something up, and then "pink" can show up in *any* of the criteria columns?

If that is the case, you have a recipe for data chaos, and you need to rip up the design. Instead, you would shunt off the criteria to a separate, related table:


tblParts
-------------------------------------------
PartID (PK)
PartName
<others>

tblOrders
-------------------------------------------
OrderID (PK)
OrderDate
CustID (FK)
SalesAgentID (FK)

tblCriteria
-------------------------------------------
CriterionID (PK)
Criterion

tblPartsCriteria
-------------------------------------------
PartsCriteriaID (PK)
PartID (FK)
CriterionID (FK) <--- create unique index on PartID + CriterionID

tblOrdersCriteria
-------------------------------------------
OrdersCriteriaID (PK)
OrderID (FK)
CriterionID (FK) <--- create unique index on OrderID + CriterionID


This may look more complicated, but it will actually simplify your life greatly, and make querying, reporting, and analysis much easier. In the UI, you manage it via subforms.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JuJuChiaAuthor Commented:
Hi Matthews,

This looks like a very robust system, except in my case, the tblOrder is an Excel import from Quickbook that will need to be updated every week or so.  I think I really need some serious help here.  I have been staying until midnight everyday, but still to no avil.  Can you refer me to someone who I can give a download of the project and help me think it through? Thanks.  
0
Patrick MatthewsCommented:
JuJuChia,

You can browse the profiles of the Experts on the leaderboards in the various Access zones.  Many of them are open to private offers of work; those who are will state that in the profile along with contact info.

A warning: the top Experts here do not work cheaply, but they are worth every penny.

Patrick
0
JuJuChiaAuthor Commented:
Hi Patrick,

Thank you for your direction, I have e-mailed a few and hopefully someone will get back to me.  
Regarding my question about matching part numbers based on 4 criteria, I tried using SQL code you wrote for my previous question:  

SELECT p.*, r.*
FROM Oppo AS p INNER JOIN Part AS r ON (p.PPM = r.PPM) AND (p.TEMP = r.TEMP) AND (p.PKG = r.PKG) AND (p.VOLTAGE = r.VOLTAGE);

This seems to do the trick.  Please let me know if there is any precautions I should be aware, thanks.  
0
Patrick MatthewsCommented:
Seeing that helps, as it looks like the four criteria columns all map to specific "categories", which reduces if not eliminates the source of my worry.

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
Load a sample of your database, with instuctions to get the expected output.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.