Calculated table field looping with VBA on multi-level structure (Access 2007)

[marmo68]

Hi all,
I'm new of VBA and I'm trying to include some code to an existing DB. I,ve a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty). I want 1)to define if a row has no lower levels - so an additional field P/N Semplice is filled with "s" by a first Loop; 2)a second loop calculate an additional field (Stato Composto) with a simple algorithm - "Stato Composto" is equal to "Stato Semplice" for "s" rows and equal to 0,4*"Stato Semplice"+0,5(s um of lower details/number of details)+0,1(su m of the STD rows/number of STD rows) for the others
I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help

[NeoPa]

I've tried to rewrite this a bit to help understanding, but it still doesn't make a lot of sense. Certainly not anything I can work to. See if it makes sense to you and tell me where it's wrong.

Marmo:
Hi all,
I'm new to VBA and I'm trying to add some code to an existing DB. I have a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty).

I want :

1. To define if a row has no lower levels - so an additional field P/N Semplice is filled with "s" by a first Loop.
2. A second loop calculate an additional field (Stato Composto) with a simple algorithm :
   [Stato Composto] is equal to [Stato Semplice] for "s" rows and equal to 0.4 * [Stato Semplice] + 0.5 * (sum of lower details / number of details) + 0.1 * (sum of the STD rows / number of STD rows) for the others.

I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help

The layout of the table is as follows :
TableName = [???]

[U]Field Name      Type[/U]
Stato Semplice  Numeric
Lev.            Numeric
STD             String
P/N Semplice    String
Stato Composto  Numeric

Also, I notice that all records are set to 's' in [P/N Semplice], yet the next step tests this - implying some of these are reset somehow. There's nothing in your explanation that explains that.

Also, you include an attachment in a format that many won't even be able to open. Is the attachment in VBA? If so then just post it in [ CODE ] tags. If not then many will have to rely on your explanation.

[marmo68]

Hi NeoPa,
the zip file contains the excel file with macro (xlsm) that perform the action in a worksheet. It works with no problem using the excel rows and columns. The problem is to migrate this approach to a VBA Access module inside a DB. The layout of the table is the one that You show. I have a jerarchical structure of items (Lev. 1 is the higher level, than Lev. 2 etc.)each having 3 basic information ("Level", "Stato Semplice" and "STD"): a first loop should define wether or not the item ia a lower level (if it's a detail - of course resetting this field); a second loop assign a value depending on the values of "P/N Semplice" and "STD".
I hope not to have added entropy ... (it's a little complex). I report the Excel macro and maybe is more clear

Public numrighe, lvmax, lv, lrif, col, rigapadre, finecicloint, liv, livsucc As Variant
Public i, j, k, w, n, m As Integer
Public p, stato, sd, std As Double

Sub KPI()
Call partisemplici
lvmax = 1
i = 2
Do While i <= numrighe
    If Cells(i, 1).Value > lvmax Then
    lvmax = Cells(i, 1).Value
    End If
i = i + 1
Loop
For lv = lvmax To 2 Step -1
lrif = lv - 1
i = 2
Do While i <= numrighe
    If Cells(i, 1).Value = lrif Then
        If Cells(i + 1, 1).Value = lv Then
        rigapadre = i
        j = rigapadre + 1
        Do While j <= numrighe And Cells(j, 1).Value > lrif
        j = j + 1
        Loop
        fineciclointerno = j - 1
        stato = 0   'stato composto
        sd = 0      'sommatoria dettagli
        std = 0     'sommatoria std
        m = 0       'numero dettagli
        n = 0       'numero std
        For k = rigapadre + 1 To fineciclointerno
         If Cells(k, 1).Value = lv Then
            If Cells(k, 4) = "s" Then 'ciclo che sceglie lo stato da conteggiare,semplice se è un dettaglio,composto se è un assieme
            col = 3
            Else
            col = 2
            End If
            If Cells(k, 5) = "std" Then
            n = n + 1
            std = std + Cells(k, col).Value
            Else
            m = m + 1
            sd = sd + Cells(k, col).Value
            End If
        End If
        Next k
        If n = 0 Then   'evita la divisione per 0
        n = 1
        ElseIf m = 0 Then
        m = 1
        End If
        stato = 0.4 * Cells(rigapadre, 3).Value + 0.5 * (sd / m) + 0.1 * (std / n)
        Cells(rigapadre, 2).Value = stato
        End If
        End If
i = i + 1
Loop
Next lv
End Sub

Sub partisemplici()
'assegna una s alle parti semplici
Set rigtab = Range("A1").CurrentRegion
numrighe = rigtab.Rows.Count
w = 2
Do While w <= numrighe
    Cells(w, 4).Value = ""
    w = w + 1
Loop
w = 2
Do While w <= numrighe
    liv = Cells(w, 1).Value
    livsucc = Cells(w + 1, 1).Value
    Cells(w, 1).Select
    If livsucc <= liv Then
        Cells(w, 4).Value = "s"
    End If
    w = w + 1
Loop
End Sub

[NeoPa]

It is certainly clearer with the code visible Marmo, but not so easy to understand what is going on. Normally I'd insist you explain it in words, but I think you're doing a good job getting as far as you have with that so I'll have to see what I can do with the code. Do something for me though if you would. Please post the whole of the Excel file that you have as I don't have the data to work with. If there are very many lines then you can delete some if necessary, but I need to see which order the data appears in for Excel code (The layout is fine for Access, but Excel doesn't use names for its columns). If you can save it in XLS format instead of XLSX then that would be better, but I think I can probably read XLSX with 2003 (I can for DOCX so I hope I can for XLSX too).

Another point :

Public i, j, k, w, n, m As Integer

This declares i, j, k, w & n as Variant type variables and only m as Integer. I suspect you intended the following :

Public i As Integer, j As Integer, k As Integer
Public w As Integer, n As Integer, m As Integer

The same is also true for line #3 :

Public p, stato, sd, std As Double

Only std is a Double type variable.

[marmo68]

Updated Attachment

Updated Attachment with xls file

Hi all,
I'm new of VBA and I'm trying to include some code to an existing DB. I,ve a table with a simple field (Stato semplice) with a numeric value. Records listed define a structure with the field Lev. - levels from 1 to "n". Another field (STD) gives the information about the type of record ("std" or empty). I want 1)to define if a row has no lower levels - so an additional field P/N Semplice is filled with "s" by a first Loop; 2)a second loop calculate an additional field (Stato Composto) with a simple algorithm - "Stato Composto" is equal to "Stato Semplice" for "s" rows and equal to 0,4*"Stato Semplice"+0,5(s um of lower details/number of details)+0,1(su m of the STD rows/number of STD rows) for the others
I have an Excel macro (attached) that do the complete job in the Excel worksheet, but I want to convert to some code that can be driven by an Access macro.
Thanks for any help

[marmo68]

Updated with english version

Hi NeoPa,
also the declaration for the Double type ha sto be extended to all the group variables. For the integer I confirm Your clarification.
I transalted in english the previous version of macro in the xls. If You run should work (it works on my PC)
Thanks for Your support

[NeoPa]

The file attached has no Option Explicit set (See When Posting (VBA or SQL) Code) nor have the declaration lines been fixed to ensure the variables are specified correctly. I'm disappointed you left this extra work for me to do. It also makes looking into it take longer so you may need to be patient. Even then I cannot promise anything as the code is not written tidily or logically and trying to work out what it does is not straightforward (Even if it were written fully in English it wouldn't be).

Anyway, I'll see what I can come up with.

[NeoPa]

The first and most obvious point is that the procedure partisemplici() cannot work in Access without the data changing. Access does not recognise positional order for records (which this procedure relies on to set the values in column D - Detail).

I looked through the rest of it, but I'm afraid I found the code so messy it was a struggle even to read it. Reverse-engineering such code is fairly pointless as the mind that created this code doesn't seem to have much of an understanding of what they were doing anyway. There is no obvious structure to it. I don't doubt it manages to execute the required process, but I expect the same job could be done by someone who fully understands the process in half the lines of code (or fewer) and that code could be used to recognise the process. I wasn't able to make much progress with this, but whatever it's doing, is specific to a spreadsheet rather than a database as (as indicated earlier) it relies on relative positioning which, while possible in Access, is not database work at all.

I'm sorry I couldn't take this any further for you. If you need more help then I'm afraid you'll need to make the question make clear sense before posting. i understand that's difficult when you're not working in English, but this isn't a question we can determine the meaning of just by looking at the code (at least not without more work and time involved than I'm prepared to contribute)

[marmo68]

Hi NeoPa,
as I told You I'm new in VBA (and this includes Excel Macros), so the code was not "clean" as made by a professional. Thanks anyway for the effort and for the moment I'll continue using the first Excel step before the Access publishing on SharePoint. If I make some improvement in the Access code I'll let You Know

[NeoPa]

Please understand that I do appreciate the situation you're in. I am merely trying to explain why I can go no further at this stage.

Furthermore, the most important part was in paragraph #1. This is not something that can work easily in Access, as Access doesn't deal naturally with relative positions (as Excel can).

[marmo68]

I'll try to convert in something draft but "edible" for Access and open a new question after Christmas. I agree that code must be completely revised. Take care and Marry Christams