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)?