Reassign values to other records. Make up for. (1 Viewer)

pawson

New member
Local time
Today, 23:15
Joined
Mar 19, 2020
Messages
23
Good afternoon Access guys!!

I have a problem with something.

The next table shows the "Posicionamiento" of some software products (Posicionamiento = Licencias Adquiridas - Consumo)


tabla.png



The next step is calculate "Compensación" and what I need is this:

Licencias Adquiridas always will be in the softwares which ones are the high version (Normalized_Name).
For Example :
- Microsoft Visual Studio Professional 2019 >> 20
- Microsoft Visual Studio Professional 2019 >> 6
- Microsoft Visual Studio Professional 2019 >> 60

But
When Posicionamiento > 0 -> Re asign the values to the other software versions like Professional 2017, 2015, 2013, 2012...
The thing is substract the negative values from the positive ones in Posicionamiento.
The results have to be like that:

prueba.png


Thank you all, i hope someone will answer.
Kind regards.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 16:15
Joined
Feb 28, 2001
Messages
18,384
OK, first things first. I want to restate what I gathered from your question because I am not sure I fully understand your goal.

You have some software with licenses that have been bought with a fixed number of license credits. Fixed number for any one purchase, but varying according each separate purchase.

In some cases, you have consumed those credits. In other cases, you have surplus credits. You are looking to somehow move the surplus credits to the overdrawn credits. Or at least, that's what it seemed like you were saying. The point about high-version vs. lower versions is a fine point that I'm not sure I understand.
 

pawson

New member
Local time
Today, 23:15
Joined
Mar 19, 2020
Messages
23
OK, first things first. I want to restate what I gathered from your question because I am not sure I fully understand your goal.

You have some software with licenses that have been bought with a fixed number of license credits. Fixed number for any one purchase, but varying according each separate purchase.

In some cases, you have consumed those credits. In other cases, you have surplus credits. You are looking to somehow move the surplus credits to the overdrawn credits. Or at least, that's what it seemed like you were saying. The point about high-version vs. lower versions is a fine point that I'm not sure I understand.
Thank you The_Doc_Man you are right and understand what is my problem.
When I say lower versions is because these ones have to be a license asigned with the high versions surplus licenses.
For example the number 39 comes from : 54 + (-6) +(-5) +(-2) + (-2) = 39
That have to be like that because we have 54 licenses we don't need, but there are lower versions that have to be licensed.
Anything else contact.
Thanks
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 16:15
Joined
Feb 28, 2001
Messages
18,384
I still don't know what you need, but this time in another sense. I was not aware of renewable legacy licenses because most Microsoft products have a lifetime license UNLESS you are obtaining that license via Software-as-a-Service, such as Office 365. But the version numbers for some of the products you have named pre-date that service. So I am confused about the need for such a thing. So this license we are discussing is not one with which I am familiar.

To do this right, I would suggest that your current data structure will make life more difficult. You have, for example, Microsoft Visual Studio Professional 2012 and Microsoft Visual Studio Professional 2013 and Microsoft Visual Studio Professional 2015 (and others). If you had the year number or version number as a separate field, then it would be TRIVIAL to write a query that could group by the product and sort by the (separate) version as a way of segregating versions but aggregating products. Because that aggregation/segregation is going to be an important part of whatever you do next.

But now I have to get radical with two more questions. First, how many total entries are we talking about? Second, how often will you need to do this kind of license reconciliation?

The reason I'm asking is because if the number is small enough and the frequency of action is far enough apart, this MIGHT be ripe for manual one-time operation. If this is a frequent exercise or you have many hundreds of these to do, then some kind of automation makes sense.
 

pawson

New member
Local time
Today, 23:15
Joined
Mar 19, 2020
Messages
23
Doesn't matter what are you saying, it's just an exercice for my school. A real example. So the thing is how to reasign a value to others in the same field.

The entries are about 30-40 more less, and this have to be automatic, not manual.

Thank you sir.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 06:15
Joined
May 7, 2009
Messages
10,877
it will be easy if you assign an Autonumber (ID) to your table.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 06:15
Joined
May 7, 2009
Messages
10,877
add Autonumber (ID) to your table.
paste this on a Module, make sure to replace "YourTable" with the name of your table.
run it by clicking inside the Function and press F5.
Code:
Public Function fncAdjCompensacion()
    Const YourTable As String = "YourTable"
    Dim dbs As DAo.Database
    Dim rst As DAo.Recordset
    Dim dblPositive As Double
    Dim dblValue As Double
    Dim lngID As Long
        Set dbs = CurrentDb
        'get the sum of all positives
        dblPositive = DSum("Nz([Licencias Adquiridas], 0) - Nz([Consumo], 0)", YourTable, "(Nz([Licencias Adquiridas], 0) - Nz([Consumo], 0)) > -1")
        'get the Max ID where it is positive
        lngID = DMax("ID", YourTable, "(Nz([Licencias Adquiridas], 0) - Nz([Consumo], 0)) > -1")
        'open the recordset order by ID
        Set rst = dbs.OpenRecordset( _
                            "SELECT * FROM [" & YourTable & "] ORDER BY ID ASC;")
        With rst
            If Not (.BOF And .EOF) Then
                .MoveFirst
            End If
            Do Until .EOF
                .Edit
                dblValue = Nz(![Licencias Adquiridas], 0) - Nz(![Consumo], 0)
                !Posicionamiento = dblValue
                If dblValue < 0 Then
                    dblPositive = dblPositive + dblValue
                End If
                'just fill all [Compensacion] with zero for now
                ![Compensacion] = 0
                .Update
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        'now, update the record with Max ID with the remaining positive value
        dbs.Execute "Update [" & YourTable & "] Set [Compensacion] = " & dblPositive & " " & _
                    "WHERE ID = " & lngID & ";"
    
End Function
 

Users who are viewing this thread

Top Bottom