Table/query layout trouble

[ineedhelp]

I know this query looks/sounds like a very tall order, but I would be sooooo
grateful of absolutely any suggestions.

I am producing a database for a company that deliver maintenance and support
services where their customers pay them a monthly/quarterly/annually fee for
the service. The business needs a system to record when their customers have
paid their due payments, which of their customers has paid their due payments
and when the next payment for each customer will be.

I have started by setting up a maintenance and support payment table:
M&S payment ID - primary key
Customer ID - number
Payment frequency - text or lookup value (monthly/quarterly/annually)
M&S start date - date
Date of payment - date

This would be a table for the company to enter each payment whenever they
receive one from a customer.

The problems that I am encountering is that depending on the payment
frequency of the customer, I can’t see a way of the system taking into
account leap years, 30 day months and 31 day months to determining next due
payment dates based on the most current payment date and payment frequency.

I had an idea to create a query to work out all of the next payment dates
for each customer with a payment.

Next due payment: [date of payment]+[payment frequency]

This would only work if the payment frequency is a number value, it would
obviously not work if the payment frequency field contained a text or lookup
value such as monthly.

One of my questions is, is there anyway of me turning the payment frequency
(monthly/quarterly/annually) into a number value, or using some sort of
calendar control to eliminate mistakes to do with leap years etc.

Secondly, I would be so grateful for any alternative layouts for tables or
queries for many payments to be added for each customer, and methods for the
user to record whether a due payment has been paid, and customers that have
still to pay their due payments.
Thank you

 

[Arvin Meyer [MVP]]

RE: > One of my questions is, is there anyway of me turning the payment
frequency

(monthly/quarterly/annually) into a number value, or using some sort of
calendar control to eliminate mistakes to do with leap years etc.

Yes. The DateAdd() and DateDiff() functions do take into account month
sizes, so you can write:

DateAdd("m",3,Date)

and the date will be 3 months from now. So now if you set up your frequency
you can use some code like (substitute your names, and remember this is
untested):

Sub txtFrequency_AfterUpdate()
Select Case Me.txtFrequency
Case "Monthly"
Me.txtNextDate = DateAdd("m",1,Me.DatePaid)
Case "Quarterly"
Me.txtNext Date = DateAdd("m",3,Me.DatePaid)
Case "Yearly"
Me.txtNext Date = DateAdd("y",1,Me.DatePaid)
Case Else

End Select
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I know this query looks/sounds like a very tall order, but I would be
sooooo
grateful of absolutely any suggestions.

I am producing a database for a company that deliver maintenance and
support
services where their customers pay them a monthly/quarterly/annually fee
for
the service. The business needs a system to record when their customers
have
paid their due payments, which of their customers has paid their due
payments
and when the next payment for each customer will be.

I have started by setting up a maintenance and support payment table:
M&S payment ID - primary key
Customer ID - number
Payment frequency - text or lookup value (monthly/quarterly/annually)
M&S start date - date
Date of payment - date

This would be a table for the company to enter each payment whenever they
receive one from a customer.

The problems that I am encountering is that depending on the payment
frequency of the customer, I can't see a way of the system taking into
account leap years, 30 day months and 31 day months to determining next
due
payment dates based on the most current payment date and payment
frequency.

I had an idea to create a query to work out all of the next payment dates
for each customer with a payment.

Next due payment: [date of payment]+[payment frequency]

This would only work if the payment frequency is a number value, it would
obviously not work if the payment frequency field contained a text or
lookup
value such as monthly.

One of my questions is, is there anyway of me turning the payment
frequency
(monthly/quarterly/annually) into a number value, or using some sort of
calendar control to eliminate mistakes to do with leap years etc.

Secondly, I would be so grateful for any alternative layouts for tables or
queries for many payments to be added for each customer, and methods for
the
user to record whether a due payment has been paid, and customers that
have
still to pay their due payments.
Thank you

 

[Ken Sheridan]

Firstly, rather than using simple date arithmetic such as [date of
payment]+[payment frequency], use the DateAdd function. This accepts an
argument to specify the interval (years, months, quarters etc right down to
seconds) so takes account of leap years and different length months. Its
also better programming practice in principle because it doesn't rely on the
date/time implementation used by Access. I'd suggest you use the same values
in your PaymentInterval column as those used by the DateAdd function, "yyyy"
for year, "q" for quarter, "m" for month and so on. You can then include the
number of intervals in a PaymentFrequency column, e.g. for a customer paying
once every 6 months PaymentInterval would be m and PaymentFrequency 6.
This will make your query a lot easier (more on this below).

As regards the 'logical model' i.e. how you represent the real world
entities in terms of tables and relationships between them, your current
table includes some redundancy because you'll need to repeat the payment
frequency and M&S start date values every time a payment is made. So you
need to decompose this table into two related tables:

CustomerM&S
----CustomerID
----PaymentInterval
----PaymentFrequency
----M&S_StartDate
----PaymentAmount

Payments
----CustomerID
----PaymentDateDue
----PaymentDateActual
----PaymentAmount

If there is only one M&S account per customer then CustomerID can be the
primary key of CustomerM&S, which will then be related one-to-one to a
Customers table. In fact, as there will presumably be no customers who do
not have M&S accounts there is no reason why these cannot be columns in the
Customers table.

If on the other hand one customer could have more than one account, e.g.
relating to different services and/or a different payment regime, then
CustomerID cannot be the primary key of this table, so introduce a
CustomerM&S_ID primary key, and in the payments table make this the foreign
key in place of CustomerID.

The primary key of Payments is a composite one made up of CustomerID and
PaymentDateDue (or of CustomerM&S_ID and PaymentDateDue in scenario 2 above).
This assumes no one customer will make more than one payment on any one day
of course.

Note that PaymentAmount is represented as columns in both tables. In
CustomerM&S it represents the amount currently chargeable so can change over
time. In Payments it represents the amount actually paid each payment date,
so once entered will not change. Its analogous to the UnitPrice columns in
the Products and OrderDetails tables in the sample Northwind database.

For your query to work out the next payment date you'd now join the two
tables of course, and probably also include a Customers table to get the
customer name etc. You'll need to use the MAX(PaymentDateDue) as the basis
for working this out, i.e. the last payment due, so the expression would be
like this:

DATEADD(PaymentInterval,PaymentFrequency,MAX(PaymentDateDue))

The query would need to be grouped by customer of course.

Note that the computation of the next payment date is on the basis of the
date due not the actual date of payment. If it were based on the latter then
a customer paying late would benefit by virtue of the computed next payment
date being pushed forward. They'd be getting the time between when the
payment was due and the late payment date free! Similarly any customer who
should be foolish enough to pay early would not be disadvantaged.

One thing to note is that the above would not compute a next payment date
for any customers who have not as yet paid anything. There are ways around
this if necessary – it would involve LEFT OUTER JOINing CustomerM&S to
Payments and basing the computation on the M&S_StartDate if there is no
matching row in Payments.

Ken Sheridan
Stafford, England

I know this query looks/sounds like a very tall order, but I would be sooooo
grateful of absolutely any suggestions.

I am producing a database for a company that deliver maintenance and support
services where their customers pay them a monthly/quarterly/annually fee for
the service. The business needs a system to record when their customers have
paid their due payments, which of their customers has paid their due payments
and when the next payment for each customer will be.

I have started by setting up a maintenance and support payment table:
M&S payment ID - primary key
Customer ID - number
Payment frequency - text or lookup value (monthly/quarterly/annually)
M&S start date - date
Date of payment - date

This would be a table for the company to enter each payment whenever they
receive one from a customer.

The problems that I am encountering is that depending on the payment
frequency of the customer, I can’t see a way of the system taking into
account leap years, 30 day months and 31 day months to determining next due
payment dates based on the most current payment date and payment frequency.

I had an idea to create a query to work out all of the next payment dates
for each customer with a payment.

Next due payment: [date of payment]+[payment frequency]

This would only work if the payment frequency is a number value, it would
obviously not work if the payment frequency field contained a text or lookup
value such as monthly.

One of my questions is, is there anyway of me turning the payment frequency
(monthly/quarterly/annually) into a number value, or using some sort of
calendar control to eliminate mistakes to do with leap years etc.

Secondly, I would be so grateful for any alternative layouts for tables or
queries for many payments to be added for each customer, and methods for the
user to record whether a due payment has been paid, and customers that have
still to pay their due payments.
Thank you