Need Help using Access to create a payment schedule

[orange]

Sorry about all the snow. Seems like everyone north of Jacksonville has a similar complaint.


I didn't make a database yet since I wasn't sure if some of my guesses were in line with your operations.
The tables, queries, forms etc are in a section of my working database. I'll look at the VA/CRT replacement and a few things and move it to a separate database.

orange

[vaaccessuser]

Thanks Orange. I can also create the forms and link them to the procedures...whatever is easier (although your option to move it to separate db sounds enticing). I am excited because I can sure learn a lot from playing around with your db!

I keep accidentally clicking on the unsubscribe link on the email.

[vaaccessuser]

Eager to test when you're ready

[orange]

I have managed to get the search and replace utility working and have made the VA-->CRT change. I've tested the change a little and it seems to have worked.
I am attaching the latest version of the sample database. You should test/exercise the sample to ensure it does what you need. Keep a list of things that aren't quite what you need. It is set up as 1 database at the moment. If you are going to have multi users, then it should be split. Tables on a shared location/server, and queries, forms,reports, modules in a separate database, and a copy of that database on every user's PC.
First let's see if it does anything productive.

Take a look at the tables and forms. It should all work from forms.

Good luck.

[vaaccessuser]

Hi Orange:

tested the db today and works like a charm! Did a few tweaks and added additional queries. I can't thank you and the forum enough for all your assistance and responsiveness! Learning a lot as well looking through the code and how the forms, tables, and queries all interact to do what the db is supposed to do. Kudos and open to get to half your level somewhere down the road!

[orange]

Glad it is working for you.
As I said previously, if there is something that doesn't do what it is supposed to, then post or PM.
I'll be leaving FL on 28/29 weekend.

I suggest that once you get familiar with the code (and adjustments), then create some documentation (involve users) .
For communications, a diagram of the tables and relationships; the tables and field definitions; then the processes related to the various buttons. It's a great learning exercise, and it will pull the team together.

You should(read that as Must) split the database if multi-user. You should split it regardless.

Good luck.

Strange: I just got on the PC after a day at the auto repair shop. I didn't get any note that you had posted. I went looking for vaUser and that's how I found your latest post.???

[vaaccessuser]

Leaving Sunny FL? Vacation?

[orange]

Yes, 3 months here and heading home (to the last of the snow...hopefully).

How is it going? What sort of changes were needed? I'm here for a little longer if you need something.

[vaaccessuser]

No changes needed...I just had to pretty up the form and added 3 queries that are called upon by 3 reports:
1. Employee Severance Payment Schedule (when user runs it, they are prompted for EmplId and the report is the full payment schedule for that employee)
2. A report where user enters a specific paydate and they have all the employees they need to enter for that pay period
3. A Last Payment Report --- shows all employees on the database and their final payment information


I do have another little project but is not an entire database. We have a report from one of Treasury's web systems, but the problem is the data (let's say it is payment data) is created on a report. The problem with the report is when converted to excel, each payment information is presented in columns downward, with the data in separated into single cells. I can import it to Access, but trying to see if there is a simple procedure/code that will transpose the payment information across rows. I have a screen shot of the data (column A) and desired output (starts on Column C). Should loop until the last Payment # is found and processed. Might actually be easier on Excel since a transpose can be done?

[orange]

What does "data" represent? How many (max) of these "data" cells for a given Payment number?
You show 7 as max.
If the data input is formatted such as:
Payment 1
data
data
data
Payment 2
data
...

You could read the column, capture payment, then record the "data"
If it is just for reporting, then a loop

eg:
read column info

output line would be
outputline=outputline & ", " & column record

it would be a loop to process each payment and related data.

Needs logic to get first payment and data, then a loop to the end (I think).

This would just be a series of strings for presentation ( phoney report).

What is the purpose of this formatted "report"?
Does it have to be processed subsequently? If so, for what/how?

[vaaccessuser]

There is no max number of data. Problem is some payments have more fields than others. Just interested in getting each payment record across on rows. The problem with this report is the data is presented downwards. If I had it across, each payment would be a one row record then I can use VLOOKUP (or even better---index match in excel) to match records from a second table.

I had written en excel macro to insert a blank line before the cell Payment data to at least separate each payment form one another so when I attempt a loop, it knows where the next payment "block" starts.

Purpose of this project is so I can handle the data from the original report in a much better format (one row across versus several rows going downwards on a single column).

[orange]

Do you have a sample of the data?
If it's web based is it available in xml format?
Really need a sample to appreciate the format and options.

What happens to the data after you reformat to what you need?

[vaaccessuser]

After reformatting the second row actually includes individual's SSN...I then would export to excel and mash against another worksheet using Index,Match (better than VLOOKUP) and find my unmatched data...Will send sample data withing an hour...

[vaaccessuser]

The report on the web can only be produced as a pdf...so I copy all the data and paste it onto excel...will see if I can PM data...there are more Payment Numbers than the four shown...data changed for confidentiality...

[vaaccessuser]

sample data...there are more payments than the four shown