Need Help using Access to create a payment schedule

[orange]

This is a picture of data.
Have you considered asking for a format more appropriate to your needs?
I'm not familiar enough with excel to comment on it.

[vaaccessuser]

Unfortunately the only format we can get it in is .pdf, which I convert into excel and import to access...I have smaple data on the access db I will to attach. You'll see what I mean by the number of rows between Payment Number differs.

[vaaccessuser]

Sample Data of payment info

[orange]

I'll look at this tonight.

[orange]

vaaccessuser,

I looked at your sample data. The approach I followed was to get the fields and values into a table.
The table could be used as the record source for your own report.
Fields were text strings terminated with :
Values were text strings that followed the :

The first issue was those lines/records that had 2 fields on the same line.
That was solved and I made a new list with every field/value pair on 1 line/record.

I set up a table with all the field names I found in the sample.
I found that some fields were not terminated with :. So to get around the "funny fields", I made 2 fields to resolve the issue.
These are numbers 10 and 11 in the list below. The originals caused a discrepancy in the field list and related values list in the SQL.
I was setting up sql to insert the field and values into the table when I found that some fields were used more than once in the same payment record. I had to adjust the table (manually) to remove these.

Bottom line is that there is a table called REVPayment with the following structure. All fields are text data type.
1 Payment Number
2 Account Name
3 Case Type
4 Principal
5 Citation Code
6 Total Amount
7 Appropriation Code
8 BETC
9 Comments
10 Payment Method Electronic Funds Transfer (EFT)
11 Payment Method Check
12 Account Number
13 ABA Routing Number
14 Account Type
15 Financial Institution Name
16 Financial Institution City
17 Financial Institution State
18 City
19 Country
20 Party Entitled to Payment
21 Payee Name
22 State
23 Street
24 Zip Code
25 SSN/EIN

I think your Payment table (your original) has missing : for Payment Method and Electronic Funds Transfer.
I think that Payment Method Check: and Payment Method Electronic Funds Transfer (EFT): are more consistent.

Anyway, I am attaching the updated database. You can adjust, revamp etc as necessary.

Good luck.

[vaaccessuser]

Thanks Orange...was a busy weekend (3 little ones) so I was only able to see this now. How do I run it?

[orange]

What I gave was the approach to get the fields from your payment table into a real access table REVPayment, so that you could use that table to build some custom forms/queries/reports. An nterim table tblPayment is used to get all the fields from Payment and to put them into a structure for processing to REVPayment.

As mentioned there were some "inconsistencies in the payment table"
-- some terms did not end with ":", and I had to adjust these "by hand" (no automation of the process).
-- some fields in your Payment had 2 values on the same line (requiring further processing)
-- some fields were used more than once in the payment table (since you can not have 2 fields with same name in table, I had to remove the second instance)[note: you can use the same field more than once in a report/query or form]

The steps I used were:
1.
' Procedure : createTable
' Author : mellon
' Date : 18/03/2015
' Purpose : Create a revised payment table called REVPayment that has all fields and values of original Payment.
'Note that not all payment records have the same fields. REVPayment is meant to house all fields from all records.
' This was the initial attempt to create the table. The table has since been updated manually
' through the table design interface.

2.' Procedure : adjustPayment
' Author : mellon
' Date : 18/03/2015
' Purpose : To restructure the Payment by removing the records with multiple
' values in 1 line. Citation Code, Appropriation Code and SSN/EIN were the fields.
'This routine does:
'a) reads Payment and outputs to tblPayment. It creates a new record for
' Citation Code, Appropriation Code and SSN/EIN . Such taht every field identiifed in the sample data
' is now an individual record in tblPaymnet, and
'
'b) it process tblPayment to remove the original Citation Code, Appropriation Code and SSN/EIN info that was
' included in another record.

'---------------------------------------------------------------------------------------
3.
'---------------------------------------------------------------------------------------
' Procedure : trysplit
' Author : mellon
' Date : 18/03/2015
' Purpose : This routine takes the tblPayment (1 field and value per line) and assembles
'a record for the REVPayment table, then calls writeSQL to do the actual write.
'---------------------------------------------------------------------------------------

4.'---------------------------------------------------------------------------------------
' Procedure : writeSQL
' Author : mellon
' Date : 18/03/2015
' Purpose : This routine is called to write the sql (the new record) into RevPayment
' The purpose of the revPayment table is to have a regular table with which you can create
' your own report(s).
'---------------------------------------------------------------------------------------


5 (an extra)
'---------------------------------------------------------------------------------------
' Procedure : listflds
' Author : mellon
' Date : 18/03/2015
' Purpose : This routine lists all of the fields in the table REVPayment
'---------------------------------------------------------------------------------------


I ran it using the procedures, you might be able to automate, but you'd have to resolve the "inconsistencies" I found in the data.

Good luck.