I am making a small estimator app for a friend who fabricates windows and doors. I have 8 tables which are linked in a straight line of 1-M relationships as follows: Customers --> Orders --> Order.Details --> Products --> Components --> Parts --> Formulas --> Materials.
The Order.Details holds the quantity, height and length of each product ordered. The Materials table holds the unit cost of each raw material. The Formulas table has a text field that holds the method of calculating the exact measure of each raw material for each part of each component of each product. These stored methods use code letters (H, L, W) for Height, Length and Width, e.g. "(H-14)/2".
I will write VBA to replace the code letters with the actual values of (H, L, W) for each order detail and then use the EVAL function to compute the exact size of each material.
I have a query that retrieves the respective data from the 8 tables as follows:
and this is the sample result I am getting for my first order:Code:SELECT Cu.CustomerName, Or.OrderID, Pr.ProductName, OD.Quantity, OD.Height, OD.Width, OD.Length, Co.ComponentName, Pt.PartName, F.Formula1, F.Units1, M.ShortName, M.UnitCost FROM Customers Cu INNER JOIN ( Materials M INNER JOIN ( ( ( ( Products Pr INNER JOIN ( Orders Or INNER JOIN OrderDetails OD ON Or.OrderID = OD.OrderID ) ON Pr.ProductID = OD.ProductID ) INNER JOIN Components Co ON Pr.ProductID = Co.ProductID ) INNER JOIN Parts Pt ON Co.ComponentID = Pt.ComponentID ) INNER JOIN Formulas F ON Pt.PartID = F.PartID ) ON M.MaterialID = F.MaterialID ) ON Cu.CustomerID = Or.CustomerID;
CustomerNameOrderIDProductNameQtyHeightWidthComponentNamePartNameFormula1Units1MaterialJOHN DOEUnitCost
Window - Sliding1
1100WindowWallFrame120
Misc
(H+L)*2
cm
Brushes - Roll
JOHN DOE1
Window - Sliding1
1100WindowWallFrame120
Misc
4
pc
Rivet
JOHN DOE10
Window - Sliding1
1100WindowWallFrame120
Misc
28
cm
Angle
JOHN DOE52
Window - Sliding1
1100WindowWallFrame120
Misc
(H+L)*2
m
Rubber
JOHN DOE500
Window - Sliding1
1100WindowWallFrame120
Misc
4
pc
Wall screws
JOHN DOE10
Window - Sliding1
1100WindowWallFrame120
Bottom
H
cm
Top/Bottom
JOHN DOE38
Window - Sliding1
1100WindowWallFrame120
Top
W
cm
Top/Bottom
JOHN DOE38
Window - Sliding1
1100WindowWallFrame120
Left
L
cm
Jamb
JOHN DOE69
Window - Sliding1
1100WindowWallFrame120
Right
L
cm
Jamb
69
Based on the above query and sample results, I am trying to figure out a better way to handle the computation than what I have come up with so far in VBA:
1. Get the query results in a rst and loop through each record
2. Replace the letter codes in the formula with the actual values
3. Run EVAL to get the measure of each piece
4. Multiply the value in (3) by the order quantity to get the total measure
The new record, with values (3) and (4), is then inserted into a REQUIREMENTS Table.
Q1. This row-based approach is considered inefficient by SQL pros.
A set-based approach would be preferred but I can't think of how to run the VBA function EVAL in SQL.
Q2. I am using 2007, but could the computed column option in Access 2010to help resolve (2) and (3)?