Trying to build a tickler system

[newfishie]

Hi all, I am very new to Access. I am trying to build a simple tickler system.
Basically I would like to enter new loan details into acces, ideally into tables showing when future quarterly and annual financials need to be received (showing financial period end date and then additional days until these financials become due - and these days could vary from loan to loan). Then, I'd like to add a received check box which can be manually checked.

Based on these tables I could run a query which would show items not received by the due date as 'overdue' items. I'd like to do for 20 loans or so.

A few questions:

1) can I autopopulate a table for a list of quaterly and annual dates? Instead of entering future time periods (and items to be received) manually or copy paste a list from excel, is there an elegant and easy way to do this in access (ideally entering just start date and maturity date of a loan, enter the days allowed until quarterly and annual financials have to be received, and then have a table autopopulate and show me all future quarter end dates, and respective due dates (and the same for annual financials).

Once I have such tables, I could run a query showing just the overdue items (comparing today's date to the due dates).



2) Alternatively, do I even need a list showing all future period end dates and due dates? Could I calculate if any item is overdue by comparing today's date to the last quarterly end date (+ the allowed days for submission of the item). This information could feed from a very simple table (start date, maturity, days allowed after quarterly and annual financials).

I guess, if doing this I'd also need to have the option to input a 'received date' when I do receive the items (once received the items are not overdue anymore obviously).
Would it suffice to just enter a 'last received date' or would it be better (and is it even possible) to enter each 'received date'?
For this calculation I am assuming this would need SQL or VBA language ? in a Form?

I would be very grateful for any help provided. As said, sadly, I am very new to Access, SQL or VBA so looking for the simplest solution. Thank you!

[newfishie]

hello,
this is me (newfishie) posting again. can someone kindly respond? is what I am trying to do possible in Access?

Thank you!

Hi all, I am very new to Access. I am trying to build a simple tickler system.
Basically I would like to enter new loan details into acces, ideally into tables showing when future quarterly and annual financials need to be received (showing financial period end date and then additional days until these financials become due - and these days could vary from loan to loan). Then, I'd like to add a received check box which can be manually checked.

Based on these tables I could run a query which would show items not received by the due date as 'overdue' items. I'd like to do for 20 loans or so.

A few questions:

1) can I autopopulate a table for a list of quaterly and annual dates? Instead of entering future time periods (and items to be received) manually or copy paste a list from excel, is there an elegant and easy way to do this in access (ideally entering just start date and maturity date of a loan, enter the days allowed until quarterly and annual financials have to be received, and then have a table autopopulate and show me all future quarter end dates, and respective due dates (and the same for annual financials).

Once I have such tables, I could run a query showing just the overdue items (comparing today's date to the due dates).

2) Alternatively, do I even need a list showing all future period end dates and due dates? Could I calculate if any item is overdue by comparing today's date to the last quarterly end date (+ the allowed days for submission of the item). This information could feed from a very simple table (start date, maturity, days allowed after quarterly and annual financials).

I guess, if doing this I'd also need to have the option to input a 'received date' when I do receive the items (once received the items are not overdue anymore obviously).
Would it suffice to just enter a 'last received date' or would it be better (and is it even possible) to enter each 'received date'?
For this calculation I am assuming this would need SQL or VBA language ? in a Form?

I would be very grateful for any help provided. As said, sadly, I am very new to Access, SQL or VBA so looking for the simplest solution. Thank you!

[isladogs]

Perhaps, like me, people have no idea what a 'tickler' system is.
I'm sure its nothing like it sounds!

[jjcalo]

1) can I autopopulate a table for a list of quaterly and annual dates? Instead of entering future time periods (and items to be received) manually or copy paste a list from excel, is there an elegant and easy way to do this in access (ideally entering just start date and maturity date of a loan, enter the days allowed until quarterly and annual financials have to be received, and then have a table autopopulate and show me all future quarter end dates, and respective due dates (and the same for annual financials).

Once I have such tables, I could run a query showing just the overdue items (comparing today's date to the due dates).

Depending on what version of MS Access you're using (I currently have Access 2013), there's a column type called Calculated, which may be an option for you.

In a "Calculated" type column, you can specify an expression on how the field should be calculated, referencing other columns in your table.

For example, say you have a "StartDate" of Date/Time type and a "QuarterEndLeadDays" as a Number type.

Then a "TargetQuarterEndDate" of Calculated type and the expression window will pop up. You can enter an expression formula: [StartDate]+[QuarterLeadDays] with a Result Type of Date/Time.

When you open your table and enter a Start Date and the number of "lead" days, the TargetQuarterEndDate will auto-calculate your target date.

[ArviLaanemets]

Taking into consideration you need to handle only about 20 loans, it is very likely you can make this all with 2 Excel tables (a Loans table with a row for every loan, and a Payments table with a row for every payment period per loan), and much more easily than with designing an Access database. Unless you have some home-made payment-calculation rules, main functions in Excel you will use will be PMT() and IPMT(). As for entering payment periods manually, per loan you have to enter a row with loan details (what you need to do in Access too) into Loans table, and a loan ID/number and number 1 for first payment period into Payments table, and to drag those 2 cells down for proper number of rows. When designed properly, the rest of Payments table is calculated automatically. And to mark payments paid, you need a column, where you can select e.g. "paid" or enter the "payment made" date into Payments table. In case you foresee a possibility the lessor can pay the loan partially or in advance, you need additional columns for "sum paid" and an additional calculated column "payment saldo".

It is possible to design Payments table being populated from Loans table fully automatically too, but it needs the Payment table to have a lot of pre-prepared empty rows for future loans.