访问:根据某些条件自动填充表中的值. (付款时间表) [英] Access: Auto-Fill in values in a table based on certain criteria. (Payment schedule)

问题描述

我基本上想基于用户选择的4个值在单独的表格中创建付款时间表.付款时间表非常基本，该表格仅需要2列，即1)付款日期，2)付款金额.

I basically want to create a payment schedule in a separate table based on 4 values that a user would select. The payment schedule is very basic and the table only needs 2 columns, 1) Date of payment, 2) payment amount.

用于填写此简单表格的4个标准值将是:1)总金额，2)付款次数，3)付款频率(每月，每季度，每半年，每年) )，4)首次付款的日期.

The 4 criteria values that are used to fill out this simple table would be: 1) the total amount of money, 2) number of payments, 3) the frequency of the payments (monthly, quarterly, semi-annually, annually), 4) the date of the first payment.

我构想的方式是有一个将选择这4个值的表格.在该表单上，可以有一个按钮来执行命令，以使用适当的值填写数据表.

The way that I envision this is having a Form where these 4 values will be selected. On that form there can be a button to execute the command to fill in a datasheet with the appropriate values.

显然，第一个条目将在第一次付款的日期，并且该条目的金额将是总金额除以付款次数.对于第二笔记录，美元金额将是相同的，日期将是第一笔付款日期+频率.因此，如果第一个付款日期是2000年1月1日，并且每年一次，那么第二个输入日期将是2001年1月1日.等等，直到最后一次付款.

The first entry would obviously be on the date of the first payment, and the amount for that entry would be the total amount divided by the number of payments. For the second record dollar amount would be the same and the date would be the first payment date + the frequency. So if the first payment date is 1/1/2000 and the frequency annually, then the second entry date would be 1/1/2001. Etc.. until the last payment is made.

虽然这是一个非常简单的付款时间表，但是我不确定如何在Access中最好地实现这一目标，甚至不确定.将不胜感激一些投入和方向.谢谢！

While it is a pretty simple payment schedule, I'm not sure how to best approach this in Access and if it's even possible. Would appreciate some input and direction. Thank you!

推荐答案

您将需要

-一个数字表，其整数从0到可能的最高支付次数，并在该数字上进行索引.

-- A numbers table with integers from 0 to the highest number of payments possible, indexed on the number.

-名为频率付款"的组合框，具有以下属性:

-- A combobox called Frequency on forms payments with the following properties:

Row source: q;quarter;m;month;ww;week
Row source type : value list
Bound column : 1
Column count : 2
Column widths : 0, 1

-查询

INSERT INTO Payments ( UserID, PaymentDate, Payment )
SELECT [Forms]![Payments]![UserID], 
       DateAdd([Forms]![Payments]![Frequency],
       [Number],
       [Forms]![Payments]![Startdate]) AS Expr2, 
       [Forms]![Payments]![LoanAmount]/
                 [Forms]![Payments]![NumberOfPayments] AS Expr3
FROM Numbers
WHERE (((Numbers.Number)<[Forms]![Payments]![NumberOfPayments]));

-用于运行查询的按钮.

-- A button to run the query.

这篇关于访问:根据某些条件自动填充表中的值. (付款时间表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！