JuJuChia
asked on
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?
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?
ASKER
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.
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.
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.
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.
ASKER
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.
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.
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Load a sample of your database, with instuctions to get the expected output.
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