r937,
thank you for trying. i am sorry for the confusion and don't EXPECT anyone to truly understand but what i could do to try and get what's going on understood so i can get the help looks to be my 1st step in getting help.
CaptainD,
thank you for your patience and attempt to help me with the add'l questions to better help you understand the situation.
i thought by only showing the actual data regardless of the other fields would be enough as I didn't want to confuse the matter by having too much information. but now it seems it's the other way around. leaving info out has caused more confusion.
ok, i have 2 tables I am working with as there are 2 tables for the backoffice system that stores all the data for a Bill Of Material (BOM)
the 2 tables in Access that I extracted the data from the backoffice systems are named CurrentBill and CurrentComp from BM1_Header and BM2_Details (backoffice table names).
Now, not sure if how I extract the data from the backoffice tables matters as the data is the same. It's just faster to work with running queries, especially during business hours when others are in the system. It's really slow.
However, i did link the BM1 to BM2 on the Bill (Parent) as the BM1 only hold the Bill items.
Basically the BillNumber and Revision fields are really the only fields I used from BM1. Then linked on BillNumber to the BM2 I get the Component items and QtyPerBill.
QtyPerBill stores how many component items each (Child) it needs to make the Bill item.
SO with the 1st query linking BM1 to BM2, i extract BillNumber, CurrentRevision, Component, QtyPerBill into the table CurrentBill.
Code:
SELECT
BM1_BillMaterialsHeader."BillNumber", BM1_BillMaterialsHeader."CurrentRevision", BM2_BillMaterialsDetail."Component", BM2_BillMaterialsDetail."QtyPerBill",
FROM
"BM2_BillMaterialsDetail" BM2_BillMaterialsDetail,
"BM1_BillMaterialsHeader" BM1_BillMaterialsHeader,
WHERE
BM2_BillMaterialsDetail."BillNumber" = BM1_BillMaterialsHeader."BillNumber" AND
BM2_BillMaterialsDetail."Revision" = BM1_BillMaterialsHeader."Revision" AND
BM2_BillMaterialsDetail."Revision" = BM1_BillMaterialsHeader."CurrentRevision"
My next query i extract the data into CurrentComponent table.
Code:
SELECT
BM2_BillMaterialsDetail."BillNumber", BM2_BillMaterialsDetail."Revision", BM2_BillMaterialsDetail."Component", BM2_BillMaterialsDetail."QtyPerBill
FROM
"BM2_BillMaterialsDetail" BM2_BillMaterialsDetail,
Here's the result from the query you are asking about:
Code:
Bill CurrentRevision Component QtyPerBill
V3 B 87-33000 1
V3-E B 87-33000 1
X-100B 001 87-33000 1
This shows that the component 87-33000 are used in the Bill V3, V3-E, and X-100B.
THANK YOU!
