Need Help using Access to create a payment schedule

[orange]

I used 9 because that was mentioned in your posts.



This is the line that forces it to stop at 9

If rs![Payment Number] = 9 Then Exit Do

You could change that to whatever you need. Most developers wouldn't want users
putting in this number. If it's unavoidable, you could put some code to make
sure a number was entered, and also to ensure the value is "in the expected range".

If you were expecting numbers like 5 or 8, then you could check the input number to be say
-positive
- greater than 0 and less than or equal 10.

If you only have a few users, you can tell them the rules to help make the entered data reasonable and consistent.

[vaaccessuser]

Hi Orange. How would you rewrite that one line of code so that it looks at the value of the NumberOfPaymentsInBetwwen field and uses that value to exit the loop?

[orange]

??What NumberOfPaymentsInBetween field? If its not always 9, where does the number come from??
Need more info. It sounds like you have table structure issues for a relational database.

I also note that your image shows an excel layout???
What is the process for the user to
--select an employee
--determine how many payments are to be made/added to the table
--what dollar amount to use?

I interpreted the problem to be you have an employee with initial payment, regular subsequent payment amount, and you had a standard 2 week pay period. The issue was to add reg payment amounts on pay dates until there was 9 payments total.

[vaaccessuser]

I sincerely apologize for the confusion, Orange .

The screen shot of Excel I posted yesterday shows two tables...(I am actually not using excel---just thought it was the easiest way to layout my goal based on one table the users enters data into.

The short one with one row on the bottom is basically the main and sole table on the db, and the fields shown are the exact fields that the user would enter (this is entirely different from the sample data we first started discussing a few days ago).

The bigger table on the screen shot is the desired schedule output I was hoping the code would create given the fields that the user fills with values.

Again, it sounds like an entirely new code and my paypal offer still stands

I envy and respect your knowledge of VBA and hope to achieve your level one day...it's a start for me...I understood your code once I started reading it and stepping into it in debug mode. I just need more experience to take the little parts and apply them. I have purchased a couple of VBA books and peruse the web as well, but found that stepping into the code and observing the watch window is a great way to learn as well.

[orange]

I think there is a basic disconnect in the definition of the issue.
Your 1 line table shows first payment, last payment and number of payments between.
But you'll notice the first and last payments are for different amounts, and there is no indication of what the
in between payments are.
Data base works on records and logic, you can add x records for an employee but you have to know what goes in the fields.

You may have all sorts of formulas in Excel and can move up/over etc, but that's not how database works.

You have a table employee (name, date of birth, hire date, latest pay grade, date of latest pay grade)
and you have a table of PayGrades (Pay Period start, pay period frequency, Pay amount).
You can hire /fire employees, change their PayGrades, Change their Pay Periods etc. But we seem to be aiming ata oving target and it is not very database like.

What we have been doing seems to be a fix up of a mess up. The problem definition is either changing or has never been finalized.
Maybe we have to go to square 1. What exactly is the issue? In database you would not have a start payment and an end payment and then fill in the between payments --it just doesn't work like that.

It seems to me a user in you situation, has to identify the employee, then has to see what the lastpayment was, and then look at the last payment number. Then has to decide how many payments of some amount (not yet determined) to be added to the file. IT just seem too awkward to be a real process - in my view.

[vaaccessuser]

Oh Gosh...no, we are on the same page. You hit the nail on the head. I was missing the field InBetweenPayment Amounts. The first payment and last payment are ALWAYS amounts that differ from each other (and also differ from the InBetweenPaymentAmounts, which will always be the same amount). So in the excel screenshot of the table, I need to add a field called InBetweenPaymentAmounts, which in this case would be the 4507.20 (which would all be identical but different from the first payment amount which would be 1352.16, and the Last Payment Amount of 2456.87). So a total of 8 fields in the table (only 7 on the screenshot because I forgot to insert the InBetweenPaymentAmounts field). All the user would do is fill on those 8 fields on the table, then the procedure would then create the output shown on the excel screenshot). Those 8 fields (when the user enters data) should have all the information needed for the procedure to create the schedule). I would love to send you something (Paypal or a starbucks gift card or something) If you would be able to code the procedure for this)

[vaaccessuser]

Also, you sound like a federal employee (or one who does work for them)...I am a fed myself...

[vaaccessuser]

Here's another way to look at it. Every Pay Period we get a "severance letter" from HR which gives us a payment schedule for the severance payments. I will attach another screenshot that shows exactly all the data in that letter. On each payperiod we can have 10 to 15 employee's severance letters. What they all have in common is that every future paydate they would all receive payments on that certain paydate. The processor who then enters the payment into the system has to pull up the letters for all employees who are receiving a payment on a particular paydate, cross out that paydate on the paper letter, and refile all the letters until the next paydate and do the same thing over and over until the total severance payment for the employees are fulfilled. Issue is its a lot of filing and pulling up the paper files each pay period, so rather than store and track via paper file, we came up with the idea that if these schedules are put into a DB, we can just query each paydate as a parameter and it would then report all employees on that paydate and the amount of their payment for that paydate. The DB would eliminate all the hassle of keeping paper files. However, the we don't want the processor to have to manually build the payment schedule because some employees can have up to 26 total future payments.

So I thought that if they can just enter the vital data (which is how I came up with the 8 fields: EmplId, EmployeeName, PayGroup, FirstPaymentDate, FirstPaymentAmount, LastPaymentDate, LastPaymentAmount, NumberPaymentsInBetween, AmountofPaymentsInBetween), then the VBA procedure can then create the schedule for them. The NumberInBetweenPayments is basically the total number of payments minus the First Payment and the Last Payment. We also know the total severance amount of all payments, but was trying to avoid having calculated fields in the table. I will attach an excel rendition of the data on that severance letter.

[orange]

Just out of curiosity where does this severance info fit with respect to your departmental HR system and your departmental Finance system. It seems to me that some sort of transaction record is identified in HR and forwarded to Finance/Pay where specifics are invoked to cause Payments to be made.

So what you have described is along this line regarding Employee severance and payments:

--Employee and HR meet and severance letter develops
--letter identifies:
EmplId
, EmployeeName
, PayGroup
, FirstPaymentDate
, FirstPaymentAmount
, LastPaymentDate
, LastPaymentAmount
, NumberPaymentsInBetween
, AmountofPaymentsInBetween

This transaction is inputted to "the system" and identifies the starting, ending and final payment info with associated payment dates.

Where exactly is "the system"? What is described is a recording of info system--it does not trigger a payment per se. It simply identifies what should/should have happened. it's a little bit like having your own system to record you household finances, and the bank's system that handles every transaction. Your system is about what you have recorded. The bank's system(with backup/recovery and reconciliation processes) has a detailed record of all debits and credits.

So in my view, there is a departmental system(s) to handle all the details. Where is the interface with your system?

[vaaccessuser]

We use Peoplesoft. What goes on the employee record is a personnel action that just states that they are terminated with an effective date of the termination. The issue is that the personnel action does not record the severance payment amount and dates, etc. The only interface to the system is the actual .dpf letter/schedule I described most recently, and that is the issue. Processor then enters each payment based on the .pdf schedule and what payperiod we are in---one payment per paydate on when that current pay period hits. After all employee's who have a payment for a specific paydate are entered during that payperiod, the processor then runs a query to show everything they entered for that pay period to reconcile against their paper stack. Issue is on more than one occasion a payment was missed because of a paper misfile. By recording the paper info onto a DB when the letter is first received, and running a report of out of the db for the current payperiod, we now have eliminated the need to use paper files and will just go by the Access Report to compare to the query out of Peopesoft.

[vaaccessuser]

By the way, as I mentioned, the only interface to the payroll system is the paper files. The paper files (severance letter) are not created or produced in anyway by Peoplesoft nor recorded in it prior to the processor actually doing the data entry...the recording of transactions is done by the data entry after the fact, and the letter it self is all drafted in paper by HR and sent to the processor.

[vaaccessuser]

Hi Orange...were you able to view my latest posts?

[orange]

Just got back online @4:10 . ATT has been working since yesterday AM-- found problem in very sporadic service and an issue at their office. Seems better now.

Yes I've just seen your posts. I have concocted a few things while being offline.

I've been playing/relearning a few things. Here some detailed records generated from transactions.
I'm also attaching some jpgs.

EmplId
Employee Name
Payment Date
Payment Number
ID
Payment Amount

279900
Cody AkBare
02/01/2015
1
33182
$1,350.51

279900
Cody AkBare
16/01/2015
2
33186
$3,150.00

279900
Cody AkBare
30/01/2015
3
33187
$3,150.00

279900
Cody AkBare
13/02/2015
4
33185
$700.00

15463
Heesah Payne
12/12/2014
1
33180
$27.00

15463
Heesah Payne
26/12/2014
2
33188
$347.00

15463
Heesah Payne
01/09/2015
3
33189
$347.00

15463
Heesah Payne
23/01/2015
4
33190
$347.00

15463
Heesah Payne
06/02/2015
5
33183
$1.99

12345
John Doe
16/01/2015
1
2
$1,352.16

12345
John Doe
30/01/2015
2
3
$4,507.20

12345
John Doe
13/02/2015
3
4
$4,507.20

12345
John Doe
27/02/2015
4
5
$4,507.20

12345
John Doe
13/03/2015
5
32799
$4,507.20

12345
John Doe
27/03/2015
6
32804
$4,507.20

12345
John Doe
10/04/2015
7
32805
$4,507.20

12345
John Doe
24/04/2015
8
32806
$4,507.20

12345
John Doe
08/05/2015
9
32807
$4,507.20

12347
john Dough
16/01/2015
1
33181
$1,352.16

12347
john Dough
30/01/2015
2
33184
$12.78

2700
Test Employee
16/01/2015
1
33151
$1,365.29

2700
Test Employee
30/01/2015
2
33154
$3,100.01

2700
Test Employee
13/02/2015
3
33155
$3,100.01

2700
Test Employee
27/02/2015
4
33156
$3,100.01

I foresee a History fle to "save" transactions once they have been applied. Something like this

EmplId
Employee Name
ID
PayGroup
FirstPaymentDate
FirstPaymentAmount
LastPaymentDate
LastPaymentAmount
NumberPaymentsInBetween
AmountofPaymentsInBetween
HasBeenVerified
AutoAssigned
MovedToHistory

2700
Test Employee
1
testOK
16/01/2015
$1,365.29
26/08/2015
$567.87
16
$68.88
-1
0
05/03/2015 2:01:55 PM

279900
Cody AkBare
1
testOK56
02/01/2015
$1,350.51
13/02/2015
$700.00
3
$3,150.00
-1
0
05/03/2015 2:33:30 PM

12347
john Dough
2
testfill
16/01/2015
$1,352.16
30/01/2015
$12.78
1
$25.98
-1
0
05/03/2015 2:33:53 PM

15463
Heesah Payne
3
rrr
12/12/2014
$27.00
06/02/2015
$1.99
4
$347.00
-1
-1
05/03/2015 2:34:11 PM

[orange]

Here are a few more thoughts to ponder.

Verify VATrans records ----report errors and stop
Apply First Payment Records
Apply Last Payment Records
Apply all interim records
------Routines to check if processes were completed or something deleted
Find VAUser Employees With No First Record
Find VAUser Employees With No Last Record

- One approach if there are employee records without a first and or last record is
- to delete all records for that employee in VAUser and rerun (easiest processing)

--Presently all VATrans are processed after all are verified

What to do with VATrans records after they have been processed?
--Move to a History Table (serves as backup and available for audit/reference
--Or Delete (loss of records/ any trail of info)

--Mocked up process

Processing:
VATrans are received and entered by Processor
Review with Review VATrans
If no records to review, then stop and determine where the records are.
If there are Records Process Current VATrans
-- this will verify all data is present
-- check and update the PayPeriods required between First and Last Payment dates
-- if any missing data, give message and quit. Processor must fix the batch of VATrans records.
-- if all Data is present, apply first and last payments for all records in VATrans, then generate and apply interim records
Visually inspect or use utilities to see if all VATrans have been applied.
--if all is well, move VATrans records to VATransHistory and remove from VATrans
---VATrans is emptied after VATrans records have been generated and applied to VAUser

Processes exist to Move VATrans to VATrans History and Retrieve VATrans from VATransHistory is a reapply is necessary

[vaaccessuser]

Wow!
-Your first screenshot named "Code" is exactly the output I need!
-Your frmVATransHistory is perfect, and same goes with the form VAStartMocku
-For Employees with missing records, I would delete all records for that employee in VAUser and rerun (easiest processing)
-After records are processed, I would --Move to a History Table (serves as backup and available for audit/reference

We are using MS Access 2010. Do you have the db somewhere where it can be downloaded? (original offer still stands)...

My only changes to the code would be to change nay reference of VAuser to CRTuser (for Central Reporting Team)...

Here in Virginia we'e again been it with snow s with teleworking yesterday and clearing the driveway, etc, I wasn't ale to log in again until tonight...

Wish I had a mind like yours, but strive to at least get halfway