# Calculated fields / Computed columns

1. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229

## Calculated fields / Computed columns

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:
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;```
and this is the sample result I am getting for my first order:

CustomerName
OrderID
ProductName
Qty
Height
Width
ComponentName
PartName
Formula1
Units1
Material
UnitCost
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Misc
(H+L)*2
cm

Brushes - Roll
1
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Misc
4
pc
Rivet
10
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Misc
28
cm
Angle
52
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Misc
(H+L)*2
m
Rubber
500
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Misc
4
pc
Wall screws
10
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Bottom
H
cm
Top/Bottom
38
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Top
W
cm
Top/Bottom
38
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
Left
L
cm
Jamb
69
JOHN DOE
1
Window - Sliding
1
100
120
WindowWallFrame
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)?

2. I just tested the Eval() function in an Access query and it did work.
Not really understanding all of your post. Can you provide the project for analysis?

I agree the data structure does not seem optimized for a relational database. I understand an order detail could have many products but can each product be related to many order details? This is a many-to-many relationship.

3. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229
June7, thanks. I would but the size limits do not allow me to. Any workaround?

If you look at it, the design is highly normalized: each orderdetail record relates to only one product record while each product record can relate to potentially hundreds of records.

4. Originally Posted by goodguy
June7, thanks. I would but the size limits do not allow me to. Any workaround?
Have you done a Compact and Repair and then zipped up the db? We accept up to 2MB of ZIP file.

5. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229
OK, here it is.

PS. There is a bug on the upload screen: when you upload files successfully AFTER getting an error message following a failed upload, the error message does not disappear. This is confusing to users who may not realize that their files have already been uploaded.

6. Just did a quick look. So far:

1. There is no code in the project, nothing to analyse.

2. Not sure is possible.

3. The Eval function does work in queries but don't think your structure is suitable for it.

For some of the lookup tables (Colours, Currencies, Categories) don't think I would use autonumber as primary key, just use the name itself, such as the colour. This will not only prevent duplicate colours, but also make it easier to view the colour value when need, no need for table join to retrieve. At least set the field to Index (no duplicates).

Also, misspelled aluminum as aluminium.

7. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229
Originally Posted by June7
Just did a quick look. So far:

1. There is no code in the project, nothing to analyse.

2. Not sure is possible.

3. The Eval function does work in queries but don't think your structure is suitable for it.

For some of the lookup tables (Colours, Currencies, Categories) don't think I would use autonumber as primary key, just use the name itself, such as the colour. This will not only prevent duplicate colours, but also make it easier to view the colour value when need, no need for table join to retrieve. At least set the field to Index (no duplicates).

Also, misspelled aluminium as aluminium.
1. Of course, it contains no code as yet. I said I was considering the approach that I described but wanted to see if a better approach was possible.
2. What possibility are you unsure about?
3. My query returns the Height, Width and Length values on the same record as the respective formula so I am puzzled why you think it is unsuitable.
4. I use AutoNumber as my PK even for such small lookup tables because that is second nature to me. I am also particular about indexing and setting constraints, unfortunately I still haven't gotten round to fixing them in this app.

So, the questions remains: Is there a better way to do this?

8. Here is why I am thinking your formula field won't work. Try this code.
Code:
```Option Compare Database
Option Explicit
Public Sub TestCalc()
Dim W As Double, H As Double, L As Double, x As Double
Set cn = CurrentProject.Connection
While Not rs.EOF
W = rs!Width
H = rs!Height
L = rs!Length
Debug.Print rs!formula1; rs!Width; rs!Height; rs!Length
x = Eval(rs!formula1)
Debug.Print x
rs.MoveNext
Wend
rs.Close
End Sub```
You managed to quote my post with a misspelling in my comment about misspelling. I don't remember posting with misspelling but must have and corrected it before I realized you had replied. But I decided to check online dictionary. Apparently, it is an accepted variation in spelling. Never seen it used in the U.S.
Last edited by June7; 09-18-2011 at 11:20 AM.

9. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229
Thanks for your code, June7. This is what I was planning to fall back on if nothing better came up. Below is my code
Code:
```Public Sub Requirements()

Dim W As Double, H As Double, L As Double, xValue As Double
Dim F As String

Set cn = CurrentProject.Connection

While Not rs.EOF
W = rs!Width: H = rs!Height: L = rs!Length: F = rs!Formula1

If InStr(1, F, "W") > 0 Then F = Replace(F, "W", W)
If InStr(1, F, "H") > 0 Then F = Replace(F, "H", H)
If InStr(1, F, "L") > 0 Then F = Replace(F, "L", L)
xValue = Eval(F)
Debug.Print "W = " & W & "; H = " & H & "; L = " & L & "; Formula = " & F & "; Value = " & x & vbCrLf; ""
rs.MoveNext
Wend
rs.Close

End Sub```

10. Then you do have a working solution. Did you have that before my posted code? Would have been nice to have it to analyze and correct my erroneous conclusion first. Now if you want can make this a function that can be called from query, like:
Public Function Calc(F As String, W As Double, H As Double, L As Double)
If InStr(1, F, "W") > 0 Then F = Replace(F, "W", W)
If InStr(1, F, "H") > 0 Then F = Replace(F, "H", H)
If InStr(1, F, "L") > 0 Then F = Replace(F, "L", L)
Calc = Eval(F)
End Function

Then call in query like:
Result: Calc([Formula1],[Width],[Height],[Length])

11. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229
@June7: Thanks for persisting with me. Yes, I did have the intent to write the code as I finally did, although I was holding out for a better solution. As I had stated in my original post:
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.

12. Competent Performer
Windows Vista Access 2007
Join Date
Dec 2010
Location
Zanzibar, Tanzania
Posts
229
@June7: The function works beautifully. Now, I can cut out all the code and go direct to displaying the report immediately the order is entered. WONDERFUL!!! Thanks a million!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Other Forums: Microsoft Office Forums - Senior Forums