Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi - keeping you guys busy today :-). Hope you can help me with this as I've been trying to sort it for a long time without result. Please see attached a table I have which is being populated by little modules as per the one in my previous question today. This table is a bit more complicated than it looks because more or less every line is generated by a different data source. I have been trying for some time to work out a module which would calculate a percentage of Line 7 against Line 1, which would then populate Line 9. For example field GCC in Line 9 should = (26/46)*100 to give me the % I require, however, to date I have singularly failed to work out either how to do this in VBA using the data source (its the same for both lines 1 and 7 in this case) or by doing an update query which keeps on giving me aggregate errors. Doing this by VBA would be much easier for me - I'm dealing with 000s of potential lines across the tables I want to calculate, and having to write 3/4 queries to get to each line's result is going to be extremely onerous.

    any suggestions aimed at beginner level (VBA)??

    Best.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    There's NO Attached Table, otherwise I would have a look at it.

    Also you need to explain which rows need to be updated and how
    in a bit more detail.
    Andrew

  3. #3
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry about that - doesn't seem to be wanting to upload.. will try again
    Attached Files Attached Files

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    OK, got the file, BUT

    You mention 1000's of rows.

    So are you just saying that for every field other than LineID ad Documentation,
    you need to
    Read the value in Record 1
    Then
    Read The Value in Record 7

    Then Divide Row 7 Value By Row 9 Value and Place the Result in Row 9 Record ?

    Also can we use the LINE ID to determine the ROW ID?
    Andrew

  5. #5
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again Andrew - this table is "static" and at every data update it is cleared out back to 0. Then routines similar to the one in my earlier question today runs through various data sources and totals certain variables into individual lines. I reckon if I can "crack" how to do one module for one line of this table, then I can work out the code for the others :-) - as I've said there are a many similar tables to be populated.

    For this table, as you state, data is identified by its Line Id (primary key) and description. The Header row relates to certain business units (BUs) GCC/PBS etc. and a Total field. Code similar to the undernoted populates the tables from various data sources with the totals of the variables found.. For lines 7 and 1 the same data source is relevant. I have written code to input from the data source for line 7 as undernoted, Line 1 is the same without the variable, giving the total. What I want to do then is to then calculate the number of complete processes (Line 7), as a percent of the total processes (Line 1) and input the totals, by BU, into Line 9. Therefore in this table, for GCC the values would be 26(Line7) / 46(Line1) x 100 = 57% (rounded up - see earlier email which you have answered so helpfully). I've tried to do this from the source data and have failed miserably, but it may be possible to calculate it after Lines 1 and 7 have been populated, using the Table data itself (TblA).

    Here is the code populating Line 7. Hope this is clear - I'm not the best communicator in writing - thanks for your help.

    Option Compare Database
    Option Explicit

    Sub AddTest()
    AddData "SubProcesses", 7
    AddData "SubProcesses", 11


    End Sub

    'ADDS TOTALS FROM SubProcesses TABLE TO LINES 7 AND 11 OF TBLA
    Sub AddData(strSource As String, lngLineID As Long)
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Long
    Set dbs = CurrentDb
    Set rstIn = dbs.OpenRecordset(strSource, dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("SELECT * FROM TblA WHERE LineID=" & _
    lngLineID, dbOpenDynaset)
    ' Optional: set all fields to 0 except the first two
    rstOut.Edit
    For i = 3 To rstOut.Fields.Count - 1
    rstOut.Fields(i) = 0
    Next i
    rstOut.Update
    ' Loop through data records
    Do While Not rstIn.EOF
    If rstIn!Status = "Complete" Then
    ' Update appropriate field and total
    rstOut.Edit
    rstOut.Fields(rstIn!BU) = rstOut.Fields(rstIn!BU) + 1
    rstOut!Total = rstOut!Total + 1
    rstOut.Update
    End If
    rstIn.MoveNext
    Loop

    ExitHandler:
    On Error Resume Next
    rstOut.Close
    Set rstOut = Nothing
    rstIn.Close
    Set rstIn = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not Totally Sure you would want to do it in same procedure.
    Anyway may or may not help

    Here is an example using your table to populate lineID 9 with Percentages.
    For every field in table other than LineID and Documentation.

    It assumes that prior to this Lines 1 and 7 are populated

    [attachment=87536:Example.zip]


    See the Function in the Module basUpdateTable
    Attached Files Attached Files
    Andrew

  7. #7
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for that Andrew - will give it a shot and get back to you! Much obliged.

  8. #8
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Andrew, scratching my head at some of your code - way over my head :-). Thanks very much though and I think I see what most of it is doing, however, your percentages are coming off in 0.00 format - what I need to see in my table is a full number e.g. where you show 0.68 (meaning 68%), I need to see 68. I'm not grasping what's happening with your IngF,3, IngF 4 etc., but suspect that's where I need to make an adjustment. How do I change this code to show "68" instead of 0.68?? Thanks. Maz

    'Now Last Time Recordset for Row 9 Only
    strSQL = "Select * From tblA Where LineID=9"
    Set rst = dbs.OpenRecordset(strSQL)
    rst.Edit
    'Use array to Update each field
    For lngF = 1 To lngFields
    'Get te Value
    If Nz(varData(lngF, 2), 0) = 0 Then
    varData(lngF, 4) = 0
    Else
    'Calc % to 2 Dec Places
    varData(lngF, 4) = Round(varData(lngF, 3) / varData(lngF, 2), 2) End If
    'Update the Field
    rst(varData(lngF, 1)) = varData(lngF, 4)
    Next
    'Update Whole Record
    rst.Update
    rst.Close
    dbs.Close

  9. #9
    Lounger
    Join Date
    Oct 2009
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, managed to work this out, and is now working great. Thanks!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •