need to make installment plan for 30 months (1 Viewer)

[rmirfan]

i am preparing database in ms acess for a society that sales houses on 3 years installments, if house price is of 3,00,000 on dated 20 Aug 2024, then installment plan is as follows

20 Aug 2024 booking amount 300,000
20 Sep 2024 balloting amount 500,000
Oct 2024 Monthly Instalment 25,000
Nov 2024 Monthly Instalment 25,000
Dec 2024 Monthly Instalment 25,000
Jan 2025 Monthly Instalment 25,000
Feb 2025 Monthly Instalment 25,000
Mar 2025 6 Month Instalment 100,000
Apr 2025 Monthly Instalment 25,000
May 2025 Monthly Instalment 25,000
Jun 2025 Monthly Instalment 25,000
July 2025 Monthly Instalment 25,000
Aug 2025 Monthly Instalment 25,000
Sep 2025 6 Month Instalment 100,000

and go on upto 36 months and after it, remaining amount goes in final amount as Possession fee

so how i can create a query in ms access, that months can automatically create with data from date of sales

 

[GPGeorge]

i am preparing database in ms acess for a society that sales houses on 3 years installments, if house price is of 3,00,000 on dated 20 Aug 2024, then installment plan is as follows

20 Aug 2024 booking amount 300,000
20 Sep 2024 balloting amount 500,000
Oct 2024 Monthly Instalment 25,000
Nov 2024 Monthly Instalment 25,000
Dec 2024 Monthly Instalment 25,000
Jan 2025 Monthly Instalment 25,000
Feb 2025 Monthly Instalment 25,000
Mar 2025 6 Month Instalment 100,000
Apr 2025 Monthly Instalment 25,000
May 2025 Monthly Instalment 25,000
Jun 2025 Monthly Instalment 25,000
July 2025 Monthly Instalment 25,000
Aug 2025 Monthly Instalment 25,000
Sep 2025 6 Month Instalment 100,000

and go on upto 36 months and after it, remaining amount goes in final amount as Possession fee

so how i can create a query in ms access, that months can automatically create with data from date of sales

This example seems to indicate that the initial installment is exactly 10% of the initial sale price. Is that correct?

How are the other installments calculated. Are they a percentage of the initial sale price as well?

I believe the best approach to creating a solution that is applicable to a variety of initial sales prices is to create an amortization table, which specifies installments as percentages of the initial sales price, where appropriate. If the monthly interim installments are always exactly 25,000 or 100,000, though, that could be in the amortization table as well.

 

[Pat Hartman]

Make a table for each schedule. You need just the payment Number and the % for that payment. Then you can join to that table on the scheduleID and multiply the principle times the % to calc the amount for each payment.

Since not all loans make the same number of payments, you need to decide what percentage each payment collects for each schedule. If it is 10 payments of 10% each, that is one schedule. The schedule you posted is incomplete since it doesn't collect 100% of the 3 million so I have no clue what the schedule looks like.

 

[rmirfan]

thanks for your replies,
i prepare two tables, one of customer that have a all information regarding customers,
second of payment plan, that have following informations
File # short text (by lookup i will get customers information from table "customer")
Down payment number (i want to write its value of down payment, as it is not a %age of sales, as it depend of customer wish, what he can pay easily)
Balloting Payment number (its also not a percentage of sales, so we will enter its value)
Total Months number (i will enter number of months for a monthly installments)
Monthly Installment Value number (the value as we need it as installment per month i.e 25000)
Total Half year number (number of half years comes in 3 years)
Half Year Installment Value number (the value as we need as half year installment i.e 100000)
Possession number (here we need a formula that can show, sales price - down
payment - balloting payment - 30 monthly installments - 6 half
year installments = possession value.

here i need a help, how i can show 30 months with month name as
1st Jan 24 as monthly installment
2nd Feb 24 as monthly installment
3rd Mar 24 as monthly installment
4th Apr 24 as monthly installment
5th May 24 as monthly installment
6th June 24 as half year installment
accordingly upto 36 installments with values in ms access in report

 

[ebs17]

For payment plans in queries, I would use the DateAdd function as a guide and create fields in a corresponding base table that serve the arguments of this function.

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Approach to a query

SELECT 
   O.ID_Occurence, 
   DateAdd(O.Intervall, T.I, O.StartDate) AS Appointment, 
   O.Amount 
FROM 
   tblOccurence AS O, 
   T999 AS T 
WHERE 
   T.I BETWEEN 0 AND O.Number - 1

T999 is a table with a field I (Long), which contains the numbers 0 to 999 consecutively.
This also allows you to combine several payment options (weekly, monthly, quarterly, etc.) into one occurence.

 

[rmirfan]

i create form from table, but i not like cursor goes to some text box, as those text boxes are a result of some calculations

 

[Pat Hartman]

here i need a help, how i can show 30 months with month name as

No one can possibly give you a solution unless you tell them the calculation, if there is one. You gave us a random list of payments. If the payment amount for each period is random, how are we supposed to provide a solution? If the payment amount is equal based on the number of installments, then tell us that. If there is some magic formula, we need to know that

 

[Gasman]

i create form from table, but i not like cursor goes to some text box, as those text boxes are a result of some calculations

So protect the controls where needed?

 

[Pat Hartman]

i create form from table, but i not like cursor goes to some text box, as those text boxes are a result of some calculations

Set the tabstop property to no.

 

[rmirfan]

No one can possibly give you a solution unless you tell them the calculation, if there is one. You gave us a random list of payments. If the payment amount for each period is random, how are we supposed to provide a solution? If the payment amount is equal based on the number of installments, then tell us that. If there is some magic formula, we need to know

No one can possibly give you a solution unless you tell them the calculation, if there is one. You gave us a random list of payments. If the payment amount for each period is random, how are we supposed to provide a solution? If the payment amount is equal based on the number of installments, then tell us that. If there is some magic formula, we need to know that

thanks for your reply, actually the amount for each month is same for upto 3 years, and we need to put by ourself, we need a auto date by month drag down, if first date is 2024/01/01 then we need next date 2024/02/01 and third of 2024/03/01 ............ continue upto 36 months, day will be same, month and year will be change turn by turn

 

[rmirfan]

Set the tabstop property to no.

please guide me how i apply tabstop perperty to no

 

[GPGeorge]

please guide me how i apply tabstop perperty to no

 

[rmirfan]

View attachment 115837

thanks so much, yes it work now

 

[rmirfan]

i create query by using three tables, after it i create a Form, but form shows in design view but not shown in Form View.. please guide how it can also show in Form View

 

[GPGeorge]

i create query by using three tables, after it i create a Form, but form shows in design view but not shown in Form View.. please guide how it can also show in Form View

The most likely reason (but without seeing the actual form we can only guess) is that the query returns no records.