Table design

[Jason]

I am working on a database that will be tracking customer payments. The
customer will be charged simple daily interest. I need to have a way to
calculate the number of days between payments in order to determine the
amount of interest to pull out of each payment.
Ideally, I would like to be able to simply have one field [PMT_Date] and use
a query to calculate the number of days between the current record and the
previous record. Failing that I would have 2 fields [PMT_Date] and
[Prev_PMT_Date] and have [Prev_PMT_Date] automatically populate from the
[PMT_Date] field in the previous record. Any help would be greatly
appreciated.

Thanks,

Jason

 

[Wei Lu]

Hi Jason,

Thank you for your post.

I would like to know the table schema of your order table.

You may use the DATEDIFF function to calculate the number of days between
the current record and the previous record.

DateDiff Function
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/ht
ml/vafctdatediff.asp

For example:

SELECT DATEDIFF("d",PMT_Date,(select top 1 PMT_date from tbl_testDate order
by PMT_Date desc))
FROM tbl_TestDate;

The reason why I need your table schema is that I could know how to specify
the previous record. If you could specify the previous record with a query,
you would replace the subquery in the above statement with the query and
have a try on your side.

Hope this information will be helpful!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jason]

Wei Lu,

Thank you for your help. The table structure for the payments table is:

[PMT_ID] Auto#
[Cuctomer_ID] int (from customer table)
[Loan_ID] int (from loans table)
[PMT_Date] short date
[PMT_Made] cur
[Late_Fee] cur

*This table uses a triple key, made up of the first 3 fields

* The Customer ID and Loan ID fields are populated automatically through the
use of subforms

* All other fields are calculated fields that will be handled with queries
and/or calculated fields in the forms and reports.

The table structure for the Loans table is:

[Loan_ID] int
[Customer_ID] int (from Customer table)
[Open_Date] short date
[PMT_Structure] int (populated via a list box... ie: Monthly, Bi-Weekly,
Weekly, ect)
[Interest_Rate] long int (For now I'm only focusing on fixed rate loans.)
[Orig_Loan_Amt] cur
[PMT_Amt] cur
[Term] int

* This table has a dual key made upof the first 2 fields.

Again, Thank you for your help.

Jason

 

[Wei Lu]

Hi Jason,

Thank you for your help.

Based on my understanding, you could use the following statement to specify
the latest payment.

SELECT TOP 1 * FROM payments WHERE [Customer_ID] = <custom id> AND
[Loan_ID] = <loan id> ORDER BY PMT_Date DESC

If I misunderstood, please feel free to let me know.

Then you could use the statement as I mentioned in the previous post.

SELECT TOP 1 datediff("d",NOW,(select top 1 PMT_date from tbl_testDate
order by PMT_Date desc)) from tbl_testDate.

Hope this will be helpful!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.