Unanswered: Manufacturing area of Microsoft Access database
I am creating a database for a friend - he has a fireplace company.
He wanted his database improving which I have done
However he also wants the production of the fireplaces more productive.
This is where I have my problem, he is asking for something which will after customers have ordered fireplaces a button can be clicked at the end of each week to give production sheets for the following days. However he wants it to work out how many hearths, surrounds etc (all the parts to a fireplace) of different sizes need cutting from sheets. Once it has worked this out he wants it to put aside any spare pieces left over from cutting the sheets for example there may be 1 hearth left after cutting 8 but the last one is not needed for the orders for the week. But in the next week he wants it to be able to take into consideration those spare parts when the production sheets are created.
I don't know if it is at all possible to do something so powerful with lots of complex calculations within Microsoft Access.
If anyone has any ideas it would be of a great help, thank you in advanced
I don't know much about the fireplace manufacturing business, so I can't speculate on how the materials management side works. With that in mind, I can only make broad assumptions.
The reports he wants aren't that hard. Tracking orders and so forth is pretty basic. The x-factor here is figuring out how to calculate how many pieces of what type constitute one "sheet". I would assume that you may have multiple components that are derived from the same material, that's where the problem comes in.
It's easy to tell access that you can get say, 8 hearths out of 1 sheet. Then track how many hearths have been cut in a given week. If there were 7 hearths cut, access could tell you that you still have 1 left, and there are no more orders for hearths this week.
The problem arises when you could pull 6 hearths and a surround out of one sheet.
With my aformentioned lack of knowedge of the manufacturing process, I could only speculate that each piece would comprise of so many "units", with a given number of "units" in a hearth.
So, you could say a hearth takes up 1 unit, a surround takes 2 units and a sheet is comprised of 8 units.
With this scheme in mind, say you have 3 sheets for a given week. You need to produce 17 hearths and 2 surrounds.
You could ask access to take the total number of sheets on hand x the number of units per sheet: 3 x 8 = 24
Then you could ask access to sum the total number of units used:
(17 x 1) + (2 x 2) = 21
Then ask for the difference:
24 - 21 = 3
That's your left over material.
So that's how'd I'd evision it running. Probably have an incoming invoice or something to track incoming materials, and a table to track used materials, then use a query to get the difference. On the scheduling side, you'd probably have clients, orders and order details referencing models and parts.