Scheduling database

[Lowly Civil Servant]

I have clients who will be sending me a schedule of when payments will be
made. They will be making payments every two weeks for a year. How do I set
up a database to track whether their payments are within 4 days of their
scheduled dates? All of them will be picking a start date sometime in
October and will commit to payments every 2 weeks. How can I track whether
they are meeting their schedules?

Does anybody have a template that I can modify for this situation?

 

[banem2]

I have clients who will be sending me a schedule of when payments will be
made.  They will be making payments every two weeks for a year.  How do I set
up a database to track whether their payments are within 4 days of their
scheduled dates?  All of them will be picking a start date sometime in
October and will commit to payments every 2 weeks.  How can I track whether
they are meeting their schedules?

Does anybody have a template that I can modify for this situation?  

I am not sure I fully understand how do you want to solve this
problem, but I think I can suggest one possible solution. Goal is to
fill up table with dates of payment and then check if payments has
arrived withing 4 days of their scheduled dates.

Create table

tblPayment
----------------
IDPayment, AutoNumber, Key
ScheduledDate, Date
Amount, Currency
Payed, Yes/No

Go to VBA screen (CTRL+G) and insert new module. Copy following code
there:

--------
Sub sFillPayments()
Dim rst As Recordset, i As Integer
Dim datStartDate As Date
Set rst = CurrentDb.OpenRecordset("tblPayments")
datStartDate = InputBox("Start Date?", "Enter start date", Date)

With rst
For i = 1 To 26 'half the number of weeks in year
.AddNew
!ScheduledDate = datStartDate
.Update
datStartDate = DateAdd("d", 14, datStartDate)
Next i
End With
End Sub
--------

From Immediate Window call the code with: call sfillpayments (if you
don't see Immediate Window, turn it on from VBA window menu View
\Immediate Window), enter starting date for first payment and program
will generate 26 records, each by 2 weeks difference.

All you need to do now is to fill payments amount and/or tick Payed =
Yes (whichever you like) and then with some simple query find if they
have made payment within 4 days of scheduled date. Something like:

SELECT tblPayments.ScheduledDate, [scheduleddate]+4 AS FourDaysAdd
FROM tblPayments
WHERE ([scheduleddate]+4)<Date();

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Lowly Civil Servant]

Your code worked very well. However, I have tried to make a modification to
include the customer ID to the Payment table so that everything is in one
place, but my knowledge of VBA is extremely limited and I have not been
successful. Is this something that you can offer some advice for me? What I
would ideally like is the ability to create a form where a user would be
prompted to supply a Customer ID and a Start Date and the Payment table would
be updated with the Customer's Payment Schedule. Is this doable?

I have clients who will be sending me a schedule of when payments will be
made. They will be making payments every two weeks for a year. How do I set
up a database to track whether their payments are within 4 days of their
scheduled dates? All of them will be picking a start date sometime in
October and will commit to payments every 2 weeks. How can I track whether
they are meeting their schedules?

Does anybody have a template that I can modify for this situation?

I am not sure I fully understand how do you want to solve this
problem, but I think I can suggest one possible solution. Goal is to
fill up table with dates of payment and then check if payments has
arrived withing 4 days of their scheduled dates.

Create table

tblPayment
----------------
IDPayment, AutoNumber, Key
ScheduledDate, Date
Amount, Currency
Payed, Yes/No

Go to VBA screen (CTRL+G) and insert new module. Copy following code
there:

--------
Sub sFillPayments()
Dim rst As Recordset, i As Integer
Dim datStartDate As Date
Set rst = CurrentDb.OpenRecordset("tblPayments")
datStartDate = InputBox("Start Date?", "Enter start date", Date)

With rst
For i = 1 To 26 'half the number of weeks in year
.AddNew
!ScheduledDate = datStartDate
.Update
datStartDate = DateAdd("d", 14, datStartDate)
Next i
End With
End Sub
--------

From Immediate Window call the code with: call sfillpayments (if you
don't see Immediate Window, turn it on from VBA window menu View
\Immediate Window), enter starting date for first payment and program
will generate 26 records, each by 2 weeks difference.

All you need to do now is to fill payments amount and/or tick Payed =
Yes (whichever you like) and then with some simple query find if they
have made payment within 4 days of scheduled date. Something like:

SELECT tblPayments.ScheduledDate, [scheduleddate]+4 AS FourDaysAdd
FROM tblPayments
WHERE ([scheduleddate]+4)<Date();

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Lowly Civil Servant]

Your code worked very well. However, I have tried to make a modification to
include the customer ID to the Payment table so that everything is in one
place, but my knowledge of VBA is extremely limited and I have not been
successful. Is this something that you can offer some advice for me? What I
would ideally like is the ability to create a form where a user would be
prompted to supply a Customer ID and a Start Date and the Payment table would
be updated with the Customer's Payment Schedule. Is this doable?

I have clients who will be sending me a schedule of when payments will be
made. They will be making payments every two weeks for a year. How do I set
up a database to track whether their payments are within 4 days of their
scheduled dates? All of them will be picking a start date sometime in
October and will commit to payments every 2 weeks. How can I track whether
they are meeting their schedules?

Does anybody have a template that I can modify for this situation?

I am not sure I fully understand how do you want to solve this
problem, but I think I can suggest one possible solution. Goal is to
fill up table with dates of payment and then check if payments has
arrived withing 4 days of their scheduled dates.

Create table

tblPayment
----------------
IDPayment, AutoNumber, Key
ScheduledDate, Date
Amount, Currency
Payed, Yes/No

Go to VBA screen (CTRL+G) and insert new module. Copy following code
there:

--------
Sub sFillPayments()
Dim rst As Recordset, i As Integer
Dim datStartDate As Date
Set rst = CurrentDb.OpenRecordset("tblPayments")
datStartDate = InputBox("Start Date?", "Enter start date", Date)

With rst
For i = 1 To 26 'half the number of weeks in year
.AddNew
!ScheduledDate = datStartDate
.Update
datStartDate = DateAdd("d", 14, datStartDate)
Next i
End With
End Sub
--------

From Immediate Window call the code with: call sfillpayments (if you
don't see Immediate Window, turn it on from VBA window menu View
\Immediate Window), enter starting date for first payment and program
will generate 26 records, each by 2 weeks difference.

All you need to do now is to fill payments amount and/or tick Payed =
Yes (whichever you like) and then with some simple query find if they
have made payment within 4 days of scheduled date. Something like:

SELECT tblPayments.ScheduledDate, [scheduleddate]+4 AS FourDaysAdd
FROM tblPayments
WHERE ([scheduleddate]+4)<Date();

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Lowly Civil Servant]

I figured it out myself. Thank you for your assistance.

Your code worked very well. However, I have tried to make a modification to
include the customer ID to the Payment table so that everything is in one
place, but my knowledge of VBA is extremely limited and I have not been
successful. Is this something that you can offer some advice for me? What I
would ideally like is the ability to create a form where a user would be
prompted to supply a Customer ID and a Start Date and the Payment table would
be updated with the Customer's Payment Schedule. Is this doable?

I have clients who will be sending me a schedule of when payments will be
made. They will be making payments every two weeks for a year. How do I set
up a database to track whether their payments are within 4 days of their
scheduled dates? All of them will be picking a start date sometime in
October and will commit to payments every 2 weeks. How can I track whether
they are meeting their schedules?

Does anybody have a template that I can modify for this situation?

I am not sure I fully understand how do you want to solve this
problem, but I think I can suggest one possible solution. Goal is to
fill up table with dates of payment and then check if payments has
arrived withing 4 days of their scheduled dates.

Create table

tblPayment
----------------
IDPayment, AutoNumber, Key
ScheduledDate, Date
Amount, Currency
Payed, Yes/No

Go to VBA screen (CTRL+G) and insert new module. Copy following code
there:

--------
Sub sFillPayments()
Dim rst As Recordset, i As Integer
Dim datStartDate As Date
Set rst = CurrentDb.OpenRecordset("tblPayments")
datStartDate = InputBox("Start Date?", "Enter start date", Date)

With rst
For i = 1 To 26 'half the number of weeks in year
.AddNew
!ScheduledDate = datStartDate
.Update
datStartDate = DateAdd("d", 14, datStartDate)
Next i
End With
End Sub
--------

From Immediate Window call the code with: call sfillpayments (if you
don't see Immediate Window, turn it on from VBA window menu View
\Immediate Window), enter starting date for first payment and program
will generate 26 records, each by 2 weeks difference.

All you need to do now is to fill payments amount and/or tick Payed =
Yes (whichever you like) and then with some simple query find if they
have made payment within 4 days of scheduled date. Something like:

SELECT tblPayments.ScheduledDate, [scheduleddate]+4 AS FourDaysAdd
FROM tblPayments
WHERE ([scheduleddate]+4)<Date();

Regards,
Branislav Mihaljev
Microsoft Access MVP