How to assign serial numbers From - To.

[Ihussein]

Hello,
Below Table (A) shows the primary information, I need to generate/assign integer number From - To starting form 1, and the result should look same as table (B)
Table (A)---------------------------------------------------------
Name Qnt
Sahara 3
Tarak 2
Waeel 1

Table (B)--------------------------------------------------------
Name Qnt From To
Sahara 3 1 3
Tarak 2 4 5
Waeel 1 6 6

Regards

 

[arnelgp]

is there a formula that i should know how you arrived at those numbers?
(there must be a pattern/formula somewhere. i remember when i used to apply for a job i get exam like that. after that, the psycho test.)

 

[Ihussein]

These are just number no formula involved under column Qnt., in excel it's very easy to do the starting number is 1 which it will be in the first record as per below example,
---------------------------------------------------------
A1 B1 C1 D1
Name Qnt From To
Sahara 3 1 =C2+B2-1 'these are Excel formulas
Tarak 2 =D2+1 =C3+B3-1 'these are Excel formulas
Waeel 1 =D3+1 =C4+B4-1 'these are Excel formulas
considering the data starts from A1.
hope this clarifies
Regs.

 

[arnelgp]

ok first create your second table.
copy and paste the code below in a module.
then on immediate window of VBE run:

fnFromToGenerator "yourFirstTableName", "yourSecondTableName"

Public Sub fnFromToGenerator(strSourceTable As String, strTargetTable As String)

    Dim db As DAO.Database
    
    Dim rSource As DAO.Recordset
    Dim rTarget As DAO.Recordset
    Dim strNumUsed As String
    Dim i As Integer
    Dim iLast As Integer
    
    Set db = CurrentDb
    
    ' delete all records from target recordsource
    db.Execute "DELETE * FROM " & strTargetTable & ";"
    
    ' open the recordsets
    Set rSource = db.OpenRecordset(strSourceTable, dbOpenSnapshot)
    Set rTarget = db.OpenRecordset(strTargetTable, dbOpenDynaset)
    
    With rSource
        If Not .EOF And Not .BOF Then .MoveFirst
        iLast = 1
        While Not .EOF
            rTarget.AddNew
            rTarget![Name] = ![Name]
            rTarget![Qnt] = ![Qnt]
            rTarget![From] = iLast
            For i = iLast To iLast + ![Qnt] - 1
            Next
            iLast = i - 1
            rTarget![To] = iLast
            iLast = iLast + 1
            rTarget.Update
            .MoveNext
        Wend
    End With
    
    Set rSource = Nothing
    Set rTarget = Nothing
    Set db = Nothing
End Sub

 

[Ihussein]

Thanks for your support, exactly what I need., I have change the field Name to another name as it's reserved word by access. apprciated