Results 1 to 12 of 12
  1. #1
    goodguy is offline 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. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,140
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    goodguy is offline 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. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Quote Originally Posted by goodguy View Post
    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. #5
    goodguy is offline 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. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,140
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    Quote Originally Posted by June7 View Post
    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.
    @June7: Thanks for the reply.
    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. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,140
    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 cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim W As Double, H As Double, L As Double, x As Double
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM qryOrderRequirements", cn, adOpenDynamic, adLockPessimistic
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    goodguy is offline 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 cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        
        Dim W As Double, H As Double, L As Double, xValue As Double
        Dim F As String
        
        Set cn = CurrentProject.Connection
        Set rs = New ADODB.Recordset
        rs.Open "SELECT * FROM qryOrderRequirements", cn, adOpenDynamic, adLockPessimistic
        
        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. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,140
    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])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    goodguy is offline 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. #12
    goodguy is offline 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!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated fields
    By Madmax in forum Forms
    Replies: 1
    Last Post: 06-17-2011, 08:36 AM
  2. Calculated fields in form
    By speckytwat in forum Access
    Replies: 15
    Last Post: 04-27-2011, 05:01 PM
  3. Calculated fields
    By graciemora in forum Forms
    Replies: 1
    Last Post: 02-04-2011, 06:07 AM
  4. calculated fields
    By nashr1928 in forum Forms
    Replies: 1
    Last Post: 07-21-2010, 04:49 PM
  5. Sum of calculated fields
    By nkuebelbeck in forum Forms
    Replies: 9
    Last Post: 03-12-2010, 01:32 PM

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