If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table/query layout trouble
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 |
#2
|
|||
|
|||
Table/query layout trouble
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 "ineedhelp" wrote in message news 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 |
#3
|
|||
|
|||
Table/query layout trouble
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(Payme ntDateDue)) 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 "ineedhelp" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|