I want to create a access report that can support bill of material, print out muti-levels. I have seen some articals about it on internet, but I am a biginner in constructing queries by sql or subroutine in VB. Can someone help me get started or is there other way to create the report?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Creating Bill of Material Report
- Thread starter kelvin24
- Start date
- Thread starter
- #3
It helps to know what the BMPRDSTR_SQL table looks like:
Parent Item----Component Item--------Qty---------UOM
Tis is by no means all the fields, but you get the idea. If a parent has 4 components, there are 4 records in the table. If one of the components has 2 of its own components, there will be 2 more records in the table.
That being the case, you will need to recurse through the table again.
If you link to the item master, there is a purchase or Mfg flag which you can read to help you in doing this. Only a manufactured item can have a bill of material in Macola, so if the flag is set to "P" then you do not have to recurse through the BOM table looking for components to that item.
This should get you started. If you need any help let me know.
Actually I have a crystal report that already does this. If you are interested, please email me. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com
Parent Item----Component Item--------Qty---------UOM
Tis is by no means all the fields, but you get the idea. If a parent has 4 components, there are 4 records in the table. If one of the components has 2 of its own components, there will be 2 more records in the table.
That being the case, you will need to recurse through the table again.
If you link to the item master, there is a purchase or Mfg flag which you can read to help you in doing this. Only a manufactured item can have a bill of material in Macola, so if the flag is set to "P" then you do not have to recurse through the BOM table looking for components to that item.
This should get you started. If you need any help let me know.
Actually I have a crystal report that already does this. If you are interested, please email me. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com
- Thread starter
- #5
Thx for replying my question. I have get the idea of recursing through the table, so i try this dumb way. I created several queries with the parent and child. The parent in the 2nd query was chose from the child in the 1st query and so on. All of these went ok, but when i tried to display in the report it wouldnt let me put the fields from diffrent query.
You are writing this report in MS Access is that correct? If so I cannot help you as I do not do much with MS Access. Howver if you ask this question in one of the Access forums I am sure you will get help.
I know my crystal report to do this works, so I do not believe this is a database problem, it is an Access problem.
If I can be of any more help, please let me know. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com
I know my crystal report to do this works, so I do not believe this is a database problem, it is an Access problem.
If I can be of any more help, please let me know. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com
What version are you on? This is for Psql 2000. It is for a BOM three components deep, but you will get the idea if you have more.
Cut and paste this query:
SELECT BMPRDSTR.PARENT_ITEM_NO, BMPRDSTR.COMPONENT_ITEM_NO, BMPRDSTR.QTY_PER_PARENT, BMPRDSTR_1.PARENT_ITEM_NO AS Level2Parent, BMPRDSTR_1.COMPONENT_ITEM_NO AS Level2Component, BMPRDSTR_1.QTY_PER_PARENT AS Level2QTY_PER_PARENT, BMPRDSTR_2.PARENT_ITEM_NO AS Level3Parent, BMPRDSTR_2.COMPONENT_ITEM_NO AS Level3Component, BMPRDSTR_2.QTY_PER_PARENT AS Level3QTY_PER_PARENT
FROM (BMPRDSTR LEFT JOIN BMPRDSTR AS BMPRDSTR_1 ON BMPRDSTR.COMPONENT_ITEM_NO = BMPRDSTR_1.PARENT_ITEM_NO) LEFT JOIN BMPRDSTR AS BMPRDSTR_2 ON BMPRDSTR_1.COMPONENT_ITEM_NO = BMPRDSTR_2.PARENT_ITEM_NO;
That might get you started. Use grouping in your report to total components.
Kirk
Cut and paste this query:
SELECT BMPRDSTR.PARENT_ITEM_NO, BMPRDSTR.COMPONENT_ITEM_NO, BMPRDSTR.QTY_PER_PARENT, BMPRDSTR_1.PARENT_ITEM_NO AS Level2Parent, BMPRDSTR_1.COMPONENT_ITEM_NO AS Level2Component, BMPRDSTR_1.QTY_PER_PARENT AS Level2QTY_PER_PARENT, BMPRDSTR_2.PARENT_ITEM_NO AS Level3Parent, BMPRDSTR_2.COMPONENT_ITEM_NO AS Level3Component, BMPRDSTR_2.QTY_PER_PARENT AS Level3QTY_PER_PARENT
FROM (BMPRDSTR LEFT JOIN BMPRDSTR AS BMPRDSTR_1 ON BMPRDSTR.COMPONENT_ITEM_NO = BMPRDSTR_1.PARENT_ITEM_NO) LEFT JOIN BMPRDSTR AS BMPRDSTR_2 ON BMPRDSTR_1.COMPONENT_ITEM_NO = BMPRDSTR_2.PARENT_ITEM_NO;
That might get you started. Use grouping in your report to total components.
Kirk
- Status