I am trying to solve the following business problem and I think QB may have the needed functionality but I am not a software / database programmer so am reaching out.
I run a small engineering / metal fabrication company and I have a need to produce quotations fast and accurately. I currently use an Excel spreadsheet and although this is OK for simple jobs it is very labour intensive when calculating prices for multiple items or complex jobs with lots of parts and process steps.
I want to build an App’ that has the following capabilities:
1 – A Table of Materials with the cost for each material based on £/meter (easily done in one table).
2 – A Table of Process “Standard Times”. This is a list of process Types (e.g. MIG weld), process details (100mm long fillet weld), with the associated time it takes to perform this operation (e.g. 1min 30 seconds).
3 – From the two tables I want to be able to create a “Job Costing” query. This is where I ran out of knowledge when trialling QB.
3.1 - The query needs to be a new form that I create for a new job / quote. I want to be able to look up and select from the materials table to select a type of material and then say how much of it will be used – e.g – look up Steel, 50x50x3 box section – select this material and then specify a length – e.g. 1500mm and then select a quantity of these parts – e.g, 10 off The resulting record in the query table would be : 50x50x3 box section; 1500mm; 10 off. The query should pull in from the Materials Table the cost per meter (a field) and then multiply it by the number of pieces; in the example above it would give 10x1,500mm = 15,000mm. And because the table also has cost/meter then it would also calculate the total cost for these 10 pieces of box section.
The query / app should be able to add multiple selections of material so that a list of parts with their corresponding material costs is generated.
3.2 – In the same way as the Materials query works a second query that pulls in Process costs is needed. So the user would select from a pull down or similar each of the manufacturing steps needed to make the product. E.g. Operation 1 might be “Sawing, Bar stock less than 50mm” – this would have an associated “time” in minutes of (e.g.) 0.75 minutes. The number of these operations would be a user selectable field in the query – e.g. 10 operations to cut 10 pieces of material. And this would then total up for this step the time taken. The time has a standard labour rate associated with it and so the labour cost for this process step is computed.
As above, multiple selections would be added to the Job costing query until all steps needed to make the part are presented.
4 - With the two sets of information in place – all drawn out of a database / set of tables, the overall job cost can be calculated. To this an overhead value and a profit margin can be added and the resulting number is the price to quote the customer for the job.
I have managed to set up tables that have the basic functionality for Materials and Process costs. But then I ran out of smarts. I think what I am lacking is the overall design / schema for the app’ – I can’t figure out how to get the “Job Costing” query to pull out the data from the tables and then do the multiplication and store it in a line item of the query.
I have an Excel sheet that does the whole lot but it is labour intensive to cut and paste each line item from the Materials and Process Tables (tab in excel) into the Job Costing sheet. I tried learning Pivot Tables but could not achieve what I wanted.
Is this achievable with the functionality QB has to offer or is it beyond its capabilities?
If it is - what might a database/ table design at the high level look like to deliver this functionality?
From what I’ve seen by trialling the tool I can figure out the programming but I think without a high level design I’m just going to go round in circles.Any help / suggestions very welcomed.