Need Help using Access to create a payment schedule

[vaaccessuser]

I have a table that has data regarding severance payments for an employee. For instance, I have the following fields in a table (data entered by a user).



Employee ID
Employee Name
First Payment Date
First Payment Amount
Number of Payments (not including the first payment)
Amount of Payment (same for all payments after the first payment)

How do I create a query or code that would take the given information and create a payment schedule onto a table (each new employee's information would then be appended to the same table). We are a biweekly payroll so I need a calculation that would add for each payment:

Sample of what I need (this case, total of 5 payments including 1st payment)

EmplId | Employee Name | Payment Number | Payment Date | Payment Amount
==================================================
12345 John Doe 1 01/16/15 1352.16
12345 John Doe 2 01/30/15 4,507.20
12345 John Doe 3 02/13/15 4,507.20
12345 John Doe 4 02/27/15 4,507.20
12345 John Doe 5 03/13/15 4,507.20

[orange]

You really need to work through some tutorials on database and table design. There some basic concepts involved.
If you work through this tutorial, you will certainly experience and learn the basics.

Good luck.

[vaaccessuser]

Thanks. I have created several tables with relationships and can create queries to consolidate data from the tables. Just don't know how to create the payment schedule. Any templates or samples would be appreciated.

[orange]

Do you have an Employee Table? Does it have all the Employee data and a Primary Key?
Why exactly do you need Payment Number, if you record Payment Date?
Seems PaymentDate is always 2 weeks after first payment date.

So PaymentDate is DateAdd("ww",2, LastPaymentDate)

How do you know the Payment Amount?
Could an Employee:
--get a raise
--miss some days of work
--work less than specified amount of hour in a pay period
--work overtime during a pay period?

[vaaccessuser]

Thanks Orange. The payment amount is already given from the paperwork and is always the same amount (except for the first payment, that is also given).
My issue is (which is why I added payment Number) was I didn't know how to create the code that would loop (like a while-do or while-until) and actually add each payment date based on knowing the number of payments.

[vaaccessuser]

I also wanted to add that the user doesn't enter each payment date...just the first payment date/amount, number of payments and amount, and I want something to then build the schedule.

[orange]

Since I'm not clear on what exactly makes a payment schedule, I have copied your sample data into a table called VAUser.
Below is a vba procedure to append a record to your table. The new record would be for the next Payment, and includes the new Payment Date and new Payment Number.

'---------------------------------------------------------------------------------------
' Procedure : vaPay
' Author    : mellon
' Date      : 26/02/2015
' Purpose   :
'based on https://www.accessforums.net/database-design/need-help-using-access-create-payment-schedule-50611.html
'
' This routine reads the last record in the table, and gets values for adding a new record.
' It saves the field values in temp variables. Then prepares to add a new record (AddNew)
' assigns values to the fields
'Note: It adds 2 weeks to the latest Payment Date; 1 to the latest Payment Number
'
'It is set up to add only 1 record
'
'---------------------------------------------------------------------------------------
'
Sub vaPay()
' VaUser ( EmplId, [Employee Name], [Payment Date], [Payment Number], [Payment Amount] )


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim EmpId As Long
    Dim EmpName As String
    Dim PayAmt As Double
    Dim PayNo As Long
    Dim PayLast As Date
    
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("VaUser")
    i = 0
    rs.MoveLast    ' to get the latest values in the Employee record
    EmpId = rs!EmplId                      'same emplId
    EmpName = rs![Employee name]           'employee name
    PayLast = rs![payment Date]            'last Payment date
    PayNo = rs![Payment Number]            'last payment number
    PayAmt = rs![Payment Amount]           'last payment amount
    'do stuff here
    Do While Not rs.EOF And i = 0
        rs.AddNew                           ' set up to add a new record
        rs!EmplId = EmpId
        rs![Employee name] = EmpName
        rs![payment Date] = DateAdd("ww", 2, PayLast) 'add 2 weeks to the latest Pay Date
        rs![Payment Amount] = PayAmt
        rs![Payment Number] = PayNo + 1               'add 1 to the latest Pay Number
        
        'print the values to be added to ensure proper values
        Debug.Print rs!EmplId; rs![Employee name]; rs![payment Date]; rs![Payment Amount]; rs![Payment Number]
        rs.Update  'update the table with a new record
        i = i + 1  'stop the process since i is no longer = 0
    Loop

End Sub

Table after adding the new record

NOTE: ID is an autonumber PK I added to the table when importing the data.

EmplId
Employee Name
Payment Date
Payment Number
ID
Payment Amount

12345
John Doe
16/01/2015
1
2
1352.16

12345
John Doe
30/01/2015
2
3
4507.2

12345
John Doe
13/02/2015
3
4
4507.2

12345
John Doe
27/02/2015
4
5
4507.2

12345
John Doe
13/03/2015
5
6
4507.2

12345
John Doe
27/03/2015
6
17
4507.2

Hope this is helpful.

[vaaccessuser]

You are lifesaver, Orange!!! I will definitely learn a lot from this forum!!!

[vaaccessuser]

There a way to modify the loop such that if there was another data field that represents to number of payments past the first payment (for example, 8), it would then keep adding the rows until there are a total of 9 rows in the schedule?

[orange]

You can do all sorts of things. The real issue is to define exactly what should happen and to ensure that you have identified all the criteria and circumstances that may arise.

I you have a Payment Number, then you know how many payments have been made. If you want to ignore the first, then take the latest Payment number and subtract 1.
You can compare the latest Payment number to nine, and loop accordingly.

[vaaccessuser]

Orange, you've been the best in helping me out on this.

I hate to be a bugger and this should be the last time. So I know the first payment date and the first payment amount. Say I also knew the Last Payment Date and the Last Payment Amount (I can add both fields to to the table). Say I also knew the number of payments in-between the first payment amount and last payment out (in my example for instance say there are 7 payments between the first payment and last payment which means a total of 9 payments).

If you have time, do you think you modify your code and table so that it will keep adding rows until it matches the payments in between (8 payments in between), and when that loop stops it will add one final row with the last payment date and last payment amount? I am learning a lot playing around with what you created so far and am getting excited understanding how you stuff works. Can you do me this one last favor (I am willing to pay --- I can Paypal you if you have paypal)...

[orange]

Here is some revised code. You should test it thoroughly to make sure it does what you want/need.
It has been revised
' REVISION: Mar 2 2015 ************************************************** *******************
'It is now set up to :
' -get the last existing [Payment Number]
' -get the latest [Payment Date]
' -get the latest [Payment amount]
' add new records with incrementing Payment Numbers, add 2 weeks to Payment Date and same Payment Amount
' and stopping when Payment Number = 9

'---------------------------------------------------------------------------------------
' Procedure : vaPay
' Author    : mellon
' Date      : 26/02/2015
' Purpose   :
'based on https://www.accessforums.net/database-design/need-help-using-access-create-payment-schedule-50611.html
'
' This routine reads the last record in the table, and gets values for adding a new record.
' It saves the field values in temp variables. Then prepares to add a new record (AddNew)
' assigns values to the fields
'Note: It adds 2 weeks to the latest Payment Date; 1 to the latest Payment Number
'
'
' REVISION: Mar 2 2015 *********************************************************************
'It is now set up to :
'                 -get the last existing [Payment Number]
'                 -get the latest [Payment Date]
'                 -get the latest [Payment amount]
' add new records with incrementing Payment Numbers, add 2 weeks to Payment Date and same Payment Amount
' and stopping when Payment Number = 9

Sub VaPay()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim EmpId As Long
    Dim EmpName As String
    Dim PayAmt As Double
    Dim PayNo As Long
    Dim PayLast As Date
    
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from VaUser where [Payment Number] = (Select  max([payment number]) from vauser as x)")
    i = 0
    Do While Not rs.EOF
    rs.MoveLast    ' to get the latest values in the Employee record
    Debug.Print rs![payment Date] & "  " & rs![Payment Number]
    EmpId = rs!EmplId                      'same emplId
    EmpName = rs![Employee name]           'employee name
    PayLast = rs![payment Date]            'last Payment date
    PayNo = rs![Payment Number]            'last payment number
    PayAmt = rs![Payment Amount]           'last payment amount
    'do stuff here
    
    If rs![Payment Number] = 9 Then Exit Do
        rs.AddNew                           ' set up to add a new record
        rs!EmplId = EmpId
        rs![Employee name] = EmpName
        rs![payment Date] = DateAdd("ww", 2, PayLast) 'add 2 weeks to the latest Pay Date
        rs![Payment Amount] = PayAmt
        rs![Payment Number] = PayNo + 1               'add 1 to the latest Pay Number
        
        'print the values to be added to ensure proper values
        Debug.Print rs!EmplId; rs![Employee name]; rs![payment Date]; rs![Payment Amount]; rs![Payment Number]
        rs.Update  'update the table with a new record
        i = i + 1  'stop the process since i is no longer = 0
        Application.RefreshDatabaseWindow
        
    Loop

End Sub

[vaaccessuser]

Thanks Orange---I greatly appreciate this! I am very handy with Excel and now delving into access because of the limitation of excel on the number of rows.
I will test the code tomorrow or the following day, and am serious about giving you a little something for your assistance

[orange]

Happy to help. Make sure it does what you want before working with your production data.

[vaaccessuser]

Hi Orange. Works as expected! How do I avoid having to hardcode the loop to stop at payment #9 (i.e., the Payment Number field is entered by the user and would be dynamic---not necessary 9 payments all the time).