Excel to Access Advice (1 Viewer)

ChrisMore

Member
Local time
Today, 06:53
Joined
Jan 28, 2020
Messages
174
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
 

Attachments

  • Screenshot 4.png
    Screenshot 4.png
    114.1 KB · Views: 310
  • Screenshot 5.png
    Screenshot 5.png
    428.2 KB · Views: 357

Minty

AWF VIP
Local time
Today, 06:53
Joined
Jul 26, 2013
Messages
10,355
You need to create a child table with a record for each BOM item for the Parent item.
It may well have thousands (10,000's) of records in it but that is the only way to process this in a database.

Think vertically not horizontally.

It's not clear what the formulae are actually doing?
 

ChrisMore

Member
Local time
Today, 06:53
Joined
Jan 28, 2020
Messages
174
You need to create a child table with a record for each BOM item for the Parent item.
It may well have thousands (10,000's) of records in it but that is the only way to process this in a database.

Think vertically not horizontally.

It's not clear what the formulae are actually doing?
Hi Minty,

The formula determine what inventory item code and the quantity of that item are on the BOM. I tried to highlight these areas in the screenshots. There could be multiple item code possibilities for each product depending on the size of the product ordered.

I'm not 100% sure what you mean by "Think vertically not horizontally"?
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:53
Joined
Jul 9, 2003
Messages
16,245
I'm not 100% sure what you mean by "Think vertically not horizontally"?

I've written a Blog to help people transfer their Excel type data to access data along with comprehensive explanations of why and how. It's on my website here:-

Excel in Access
 

Minty

AWF VIP
Local time
Today, 06:53
Joined
Jul 26, 2013
Messages
10,355
At a very high level you need three tables to deal with this I think.

Products
ProductComponents
ComponentsProductsJunction


The third table contains the ProductID, The ComponentID and the Qty of Components.

You would store each product size as a separate product code, adding more records as other sizes come along.
This should be all you need to build your BOM.
 

ChrisMore

Member
Local time
Today, 06:53
Joined
Jan 28, 2020
Messages
174
At a very high level you need three tables to deal with this I think.

Products
ProductComponents
ComponentsProductsJunction


The third table contains the ProductID, The ComponentID and the Qty of Components.

You would store each product size as a separate product code, adding more records as other sizes come along.
This should be all you need to build your BOM.
Thanks for the help, Minty. Can I ask what the purpose is of the ComponentsProductsJunction table? Can the quantity of components field not go in the ProductComponents table?
 

Minty

AWF VIP
Local time
Today, 06:53
Joined
Jul 26, 2013
Messages
10,355
I wouldn't have thought so. Let say you use a 10mm screw (ComponentID 1) and a 15mm screw ComponentID 2) in Product 1

Those screws are simply a component and may have associated data with them, a supplier, materials, stock location etc.
You use the same screws in different quantities in product2.

You wouldn't want to store all that data twice so you simply store the id's - a very simple example

ProductIDComponentIDQty
1​
1​
6​
1​
2​
8​
2​
1​
11​
2​
2​
20​
 

ChrisMore

Member
Local time
Today, 06:53
Joined
Jan 28, 2020
Messages
174
I wouldn't have thought so. Let say you use a 10mm screw (ComponentID 1) and a 15mm screw ComponentID 2) in Product 1

Those screws are simply a component and may have associated data with them, a supplier, materials, stock location etc.
You use the same screws in different quantities in product2.

You wouldn't want to store all that data twice so you simply store the id's - a very simple example

ProductIDComponentIDQty
1​
1​
6​
1​
2​
8​
2​
1​
11​
2​
2​
20​
I think I see what you're saying. Does that mean that the Products table and ProductComponents table aren't related directly but are indirectly through the ComponentsProductsJunction table?
 

Minty

AWF VIP
Local time
Today, 06:53
Joined
Jul 26, 2013
Messages
10,355
Yes - Your products contain many components.
But those components are separate entities in their own right (in your case).

Hence the junction table to store the quantity per component per product.
 

ChrisMore

Member
Local time
Today, 06:53
Joined
Jan 28, 2020
Messages
174
Yes - Your products contain many components.
But those components are separate entities in their own right (in your case).

Hence the junction table to store the quantity per component per product.
Thanks for helping me get my head around that, I understand what your idea is now. Next challenge is to populate the tables with 1000's of records! I think I will create the data in Excel and import it into Access, I'm sure that will be quicker.

Cheers for your help, Minty!
 

Users who are viewing this thread

Top Bottom