Automatically creating records in one table based on values in another table

  • Thread starter Thread starter wslayton
  • Start date Start date
W

wslayton

I am developing a database that solicits settings from the user in two
option groups. I would like to take multiply the two option values and
then create that number of new records in a second table. The records
that would be automatically created need to have the id number from the
first table and a date that would be incremented a specific number of
days/months for each new record. Additionally, the new record would
have a dollar amount added as well. The following senario will help
conceptualize this.



The user would enter a start date, a dollar amount, payment schedule
i.e. monthly, quarterly, biannually, annually.



Additionally, they would enter a number of years for this to occur
over. I am using an option group to enter the payment schedule and the
number of years.



Payment schedule values are 12 for monthly, 4 for quarterly, 2 for
biannually, 1 for annually. Year values are 1 for one year, two for two
years etc. up to five years.



I would like to automatically create records in a second table based on
the input in the first table/form. The number of records to add would
be the result of [payment schedule]*[number of years].



The new records would have the id number from the first table. Each
record would have a date calculated based on the start date and the
payment schedule i.e. the date would increment by one month for those
that select monthly and by 3 months for those that select quarterly etc.
the dollar figure for each record would be the result of [Dollar
amount]/([payment schedule]*[number of years]).



Sorry this is so long and complex. Any help I can get will be most
appreciated.
 
I am developing a database that solicits settings from the user in two
option groups. I would like to take multiply the two option values and
then create that number of new records in a second table. The records
that would be automatically created need to have the id number from the
first table and a date that would be incremented a specific number of
days/months for each new record. Additionally, the new record would
have a dollar amount added as well. The following senario will help
conceptualize this.
Click to expand...

<snip>

An Append query can be written to do this. You'll need an auxiliary
table, Num, with one long integer field N - fill it with values from 1
to the most payments you'll ever need (be generous - I routinely have
a Num table with records 1 to 10000).

Create a Query based on Num: something like

INSERT INTO targettable ([ID], [PaymentDate], [Amount]
SELECT [Forms]![yourform]![txtID] AS ID, DateAdd("m", Date(), [N] *
[Forms]![yourform]![txtInterval]) AS PaymentDate,
[Forms]![yourform]![txtAmount]/([Forms]![yourform]![optPaymentSchedule]
* [Forms]![yourform]![optYears]) AS Amount
FROM Num
WHERE N <= [Forms]![yourform]![optPaymentSchedule] *
[Forms]![yourform]![optYears] ...

That's not going to be correct - you'll need to tweak the criteria
and/or the expression for PaymentDate - but I hope it will get you a
start.
 
Back
Top