Multiple date query then append to table

[rmoore]

Posting again, last thread was solved but I ran into more problems, and needed to work on the normalization of the DB.



I have "tLoans" table with OpenDate, LoanAmount, PaymentAmount, NumberOfPayments, Frequency, StartPaymentDate.

I want to append "X amount"(NumberOfPayments) payment records to my "tPayments" table from my "fAddLoan" form. The "PaymentDate" has to be at the "Frequency" starting "from the "StartPaymentDate.

What is the easiest way to do this

Ajax has suggested some code but I cant get it to work, being such a novice and the changes that i made to the DB.

[Micron]

I haven't looked at your file.
Number of payments would be a calculation that you should not store. This ought to be the count of payment date for a particular loan id.
Nor should you store the payment total $ if you are doing so.

[June7]

I presume NumberOfPayments is number of payments to be made over life of loan.

RemainingBalance should be calculated when needed, not stored.

Wouldn't DateClosed be more informative than just a Yes/No?

If only one of the 4 Yes/No fields of Paid/MissedPayment/LatePayment/PartialPayment can be checked for a record, then this should be 1 field with 4 choices (use a combobox) not 4 Yes/No fields.

Advise NOT to build lookup fields in tables.

Why no data in Frequency?

Sounds like you want to create X number of records for customer for the life of loan ('batch' create records). Why create payment records for future periods?

Need to know unit of frequency (day, month, year, bimonthly). Is frequency of 1 for monthly? Are 7, 14, 30 all days?

Code something like:

Private Sub Command17_Click()
Dim x As Integer
For x = 1 To Me.NumberOfPayments
    CurrentDb.Execute "INSERT INTO tPayments(LoanNumber, PaymentDate) VALUES(" & Me.LoanNumber & ",#" & DateAdd(IIf(Me.Frequency = 1, "m", "d"), Me.Frequency * x, Me.StartPaymentDate) & "#)"
Next
End Sub

This will not prevent duplicate entries. User can accidentally click button twice (I did) and cause duplicate set of records (user can also forget to click button at all) - if you want to prevent this then options:
1. set LoanNumber and PaymentDate as compound index to prevent duplicate pairs (this means two payments cannot be recorded on same date for a loan)
2. don't depend on user, automate this with another event instead of button click, perhaps form AfterUpdate and only for new record: If Me.NewRecord Then

Should give buttons more informative names than "Command17" and "Command19".

[rmoore]

Thanks for the insight June7 I didn't think about calculating the remaining balance only when needed, just knew I needed it. I will take that out and calculate that in a query. Also ,I will change the yes/no fields to a combobox.

"Why create payment records for future periods? "
The main reason was to schedule the collections. Do you think there is a more efficient or less complicated way of scheduling collections. I'm sure there is many ways to accomplish this but that was the idea.

The frequency has been a problem because some months are longer than others. Because I couldn't figure out how to do it, I settled to use 1=daily, 7=weekly, 14=biweekly, 30=monthly.

The actual pay frequency would be:
DAILY = Every day
WEEKLY = Every end of the week, that (day of the week) can be determined from the "StartPaymentDate" I assume.
BI-WEEKLY = "Same as (Weekly) just at two week intervals.
MONTHLY = Last day of the month, doesn't matter the day of the week.

With that said, what changes are needed in the code you suggested?

I definitely want to avoid duplicating records, I will set the loannumber and paymentdate as compound index.

The button names was oversight

[June7]

The unit in each case is days - in code "d".

However, the 30-day (monthly) would be adding multiples of 30. This means payment date is shifting through the months. I would expect at least one month to be skipped, probably February.

This is why I presumed 1 meant monthly and used the IIf() conditional. Would anyone really ever make daily payments?

If you want to stay with these units, then:

    CurrentDb.Execute "INSERT INTO tPayments(LoanNumber, PaymentDate) " & _
                    "VALUES(" & Me.LoanNumber & ",#" & DateAdd(IIf(Me.Frequency = 30, "m", "d"), IIf(Me.Frequency = 30, 1, Me.Frequency) * x, Me.StartPaymentDate) & "#)"

[rmoore]

Actually believe it or not... yes they pay daily... its a small side business I have in the Philippines, the little make shift stores borrow to open up and they prefer to pay daily, to be on the safe side.

Is there a way to write the code to use a day of the week?

[ArviLaanemets]

For monthly payments, the formula for payment date for any day number except February 29. will be something like

...DateSerial(Year(FirstPaymentDate), Month(FirstPaymentDate)+(PaymentNumber),Day(FirstPaymentDate))...

For monthly payments at 1st of month and starting with next month after signing date

...DateSerial(Year(SigningDate), Month(SigningDate)+1,1)...

For monthly payments at last day of month and starting with last day of signing date month (February payments in leap year will be at February 29.)

...DateSerial(Year(SigningDate), Month(SigningDate)+1,0)...

etc.

Create a table for payment frequencies like
tblPayFrequency; PFID (integer), Frequency (text)
with values like
1, "daily"
2, "weekly"
3, "be-weekly"
4, "monthly"
...
and use this as row source for combo where you select/display the frequency (2-column query with 1st column hidden and linked)

Whenever you want to calculate payment date, your code will be like

...
If PFID = 1 Then
   -- a code to calculate payment date or to run a query with calculated payment date with payment frequency with PFID=1 is run --
ElseIf PFID = 2 Then
   -- a code to calculate payment date or to run a query with calculated payment date with payment frequency with PFID=2 is run --
ElseIf PFID = 3 Then
   -- a code to calculate payment date or to run a query with calculated payment date with payment frequency with PFID=3 is run --
ElseIf PFID = 4 Then
   -- a code to calculate payment date or to run a query with calculated payment date with payment frequency with PFID=4 is run --
...
Else
     --whatever code you put in for case when non-existing frequency is found is run --
End If

[June7]

Arvil has expanded model to where I was just thinking about advising. The calculation I built was not right to show monthly payment on last day of month. It needed to be more complex, as Arvil has demonstrated.

A table would be helpful as a source for combobox but not mandatory, as you already know since you built lookup field in table with ValueList.

Not quite sure what you mean by 'use a day of the week'.

[rmoore]

Thanks ArivLaanements... I will try to put all that together.... not to familiar with code so I will see how it goes.... I think i know what you suggest.

Will i still need to index the fields, so as avoid duplicating?

[rmoore]

I was trying to ask if the code could be used to look for the "day of week" as in every Friday of the month or every other Friday, last day of the month etc....

But i think Arvil hit the nail on the head. I just need to figure out how to peice the code all together

[ArviLaanemets]

Indexing does not only prevent duplicate entries, it also may speed up queries. So when you keep the total number of indexes reasonable (every index uses some amount of disk space and memory), it is generally a good idea to keep them around.

At same time, try to keep occurrences where index has to prevent the entry minimal. A best way for this is to add multiple-row entries using append queries created and activated by form events. The best type of such queries will be like (on fly)

INSERT INTO YourTable (<FieldList>)
SELECT <FieldList> From SourceTable
WHERE SourceTable.SomeField NOT IN (SELECT SomeField FROM YourTable WHERE SomeCondition = TRUE)

or

INSERT INTO YourTable (<FieldList>)
SELECT <FieldList> From SourceTable alias1 LEFT JOIN YourTable alias2 ON JoinCondition
WHERE alias2.SomeField Is Null

[rmoore]

I appreciate all the help but now I am confused I have copied and pasted so much all the codes are jumbled up. I'm not familiar enough with coding to understand the inferences (Sorry). I tried to peice all the codes togeather but had no luck. It also looked like I should edit the code to add in specific data but that didn't work either

I understand Post #11 to mean I will using code for indexing . Is that correct?

Also not sure how to use the codes in Post #7 ,I tried to insert them into the first suggested code by June7 but its not working

Where do I put the (SigningDate) field? "fAddLoans" form OR "tPayments" table

[June7]

Indexing is not done in code, it is done in table design.

SigningDate is fieldname used by Arvil. Use whatever field is appropriate for indicating the start date for calcs. Seems StartPaymentDate field in your table is appropriate.

Private Sub Command17_Click()
Dim x As Integer
For x = 1 To Me.NumberOfPayments
    Select Case Me.Frequency
        Case 1, 7, 14
            CurrentDb.Execute "INSERT INTO tPayments(LoanNumber, PaymentDate) " & _
                    "VALUES(" & Me.LoanNumber & ",#" & DateAdd("d", Me.Frequency * x, Me.StartPaymentDate) & "#)"
        Case 30
            CurrentDb.Execute "INSERT INTO tPayments(LoanNumber, PaymentDate) " & _
                    "VALUES(" & Me.LoanNumber & ",#" & DateSerial(Year(Me.StartPaymentDate), Month(Me.StartPaymentDate) + x, 0) & "#)"
    End Select
Next
End Sub

Could have used a long IIf() conditional similar to what I originally did instead of this Case; but maybe this structure is easier to follow.

[rmoore]

Thanks June7, after taking a step back and clearing my mind I can now see ...

I used the latest code post# 13 and its not adding records to tPayments?

Attached is the DB, can anyone think of something im missing or didn't do?

[June7]

There is no code in that db.

The code I provided worked in the original db you posted.