[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1535
  • Last Modified:

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?  
0
JuJuChia
Asked:
JuJuChia
  • 4
  • 3
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
hnasrCommented:
Load a sample of your database, with instuctions to get the expected output.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now