Hi all,
I am looking for some advice on what options I have to mimic in Access what I have in Excel.
I work for a manufacturing company where we make bespoke fire barriers for walls and floors, as well as other products. A while ago I created an Excel workbook which allows staff to create a works order (similar to an Access form) to pass on to the factory. The works order also automatically creates a bill of materials (BOM) to aid inventory control.
I have recently created an Access inventory control database which also stores customer order details and I would like to integrate the works order and BOM into the database. My vision is for the customer order details form to have a subform which will allow the user to enter the same details as they would enter in the Excel works order, then hit a command button to open a report in same design as the Excel works order and BOM.
This all sounds straight forward so far but the problem is in order to get the BOM to auto generate, the Excel workbook uses hundreds of formulas which are bespoke for each product. I am struggling to think of how I can use calculated fields in a query for all our products (there are almost 300 of them) to get this to work. Are there any other options, such as integrating the Excel sheet containing the formulas?
I have attached some screenshots to help explain what I have in Excel.
I appreciate all advice I can get.
Chris
I am looking for some advice on what options I have to mimic in Access what I have in Excel.
I work for a manufacturing company where we make bespoke fire barriers for walls and floors, as well as other products. A while ago I created an Excel workbook which allows staff to create a works order (similar to an Access form) to pass on to the factory. The works order also automatically creates a bill of materials (BOM) to aid inventory control.
I have recently created an Access inventory control database which also stores customer order details and I would like to integrate the works order and BOM into the database. My vision is for the customer order details form to have a subform which will allow the user to enter the same details as they would enter in the Excel works order, then hit a command button to open a report in same design as the Excel works order and BOM.
This all sounds straight forward so far but the problem is in order to get the BOM to auto generate, the Excel workbook uses hundreds of formulas which are bespoke for each product. I am struggling to think of how I can use calculated fields in a query for all our products (there are almost 300 of them) to get this to work. Are there any other options, such as integrating the Excel sheet containing the formulas?
I have attached some screenshots to help explain what I have in Excel.
I appreciate all advice I can get.
Chris