Calculated Field in Query with Max Date?

[scs]

I am trying to create a query that will calculate estimated monthly revenue.
I want to calculate how much someone's payment amount averages monthly?

tblPayment
--------------
PaymentID
PaymentDate
CustomerID
Description
PaymentAmount
PaymentMethod
StartDate
RenewalDate
Comment

I'd like to take the last payment amount in the table and divide it by the
difference between RenewalDate & StartDate *30 days. This would roughly
give me a daily amount times 30. So far I have built a query that gives me
the CustomerID, last PaymentDate (using max PaymentDate), and PaymentAmount.
Whenever I try adding expressions it either gives me all records in the
payment table or a divide by 0 error. I made another query that looks at
the payment table and does the math but I can't seem to only do it for the
last payment. Customers have mutiple payments. How can I design a query
for the information I need?

 

[Guest]

Try this:

SELECT
CustomerID,
PaymentAmount / ((RenewalDate – StartDate) * 30) As EstMonthlyRev
FROM tblPayment AS P1
WHERE PaymentDate =
(SELECT MAX(PaymentDate)
FROM tblPayment AS P2
WHERE P2.CustomerID = P1.CustomerID);

In the above the subquery is correlated with the outer query by the use of
the aliases P1 and P2 to distinguish the two instances of the tblPayment
table.

Its not directly connected with this problem, but it looks to me like your
table contains some redundancy. If I understand it correctly there will be
multiple payment rows per customer which will all contain the same
CustomerID, start and renewal date values. This leaves the door open to
'update anomalies' as these values could be changed so that they differ from
row to row for the same customer. Even more importantly the CustomerID value
could be changed so a payment becomes related to the wrong customer. The
same might or might no apply to the Description and PaymentMethod columns
depending on whether these are attributes of each payment or of the payment
schedule for the customer as a whole.

To eliminate the redundancy the table should be decomposed so that the
redundant columns are in a table where each set of values will be stored just
once in one row, a PaymentSchedule table for instance with a primary key
PaymentScheduleID say (which could be an autonumber). The payments table
would then include a column which references this table via a foreign key
PaymentScheduleID field.

Ken Sheridan
Stafford, England

 

[scs]

Thanks very much Ken! That seems to have worked. I had to adjust the
parenthesis a bit to look like this. (PaymentAmount / (RenewalDate -
StartDate)) * 30 As EstMonthlyRev. I need to learn how and when to make
these aliases. Is it just a matter of learning MS SQL? Is there a book you
could recommend on it for Access specifically?

I'm not sure about tblPayment. Everytime a customer makes a payment it is
for a new membership and has a new StartDate and a new RenewalDate. I've
been thinking about how I might need to restructure it but it does seem to
me that the records are unique.

Thanks you!

 

[scs]

Thanks again Ken.

I have been modifying this to work a little better. I added tblCusotmer to
it. It's just about perfect. I have a small problem though. There is a
customer in the table that had a StartDate and RenewDate that were the same.
This is situation is possible and correct. When that happens the result of
the differnce is zero and we all know we can't divide by zero. Is there a
way to modify this statement to handle these situations? The query runs and
just shows an error on for that one record but I couldn't get my report to
run until I changed the records RenewalDate to something other than the
StartDate. Here is the current statement I'm using:

SELECT [FirstName] & " " & [LastName] AS Name,
(([PaymentAmount]/([RenewalDate]-[StartDate]))*365)/12 AS EstMonthlyRev
FROM tblCustomer INNER JOIN tblPayment AS P1 ON tblCustomer.CustomerID =
P1.CustomerID
WHERE (((P1.PaymentDate)=(SELECT MAX(PaymentDate) FROM tblPayment AS P2
WHERE P2.CustomerID = P1.CustomerID)))
ORDER BY [FirstName] & " " & [LastName];

 

[Guest]

I realised I'd parenthesised it wrongly as soon as I'd sent it, but I was
confident you'd spot this and correct it.

There are various ways you could get round the problem of the zero date
differences. Would converting the zero to 1 where necessary fit the bill?
If so try this:

SELECT [FirstName] & " " & [LastName] AS FullName,
(([PaymentAmount]/(IIF([RenewalDate]-[StartDate])=0,1,
[RenewalDate]-[StartDate]))*365)/12 AS EstMonthlyRev
FROM tblCustomer INNER JOIN tblPayment AS P1 ON tblCustomer.CustomerID =
P1.CustomerID
WHERE (((P1.PaymentDate)=
(SELECT MAX(PaymentDate) FROM tblPayment AS P2
WHERE P2.CustomerID = P1.CustomerID)))
ORDER BY [FirstName] & " " & [LastName];

You'll notice I've changed Name to FullName. Objects in Access have a Name
property so its best to avoid this as a column heading. It might cause
confusion; on a number of occasions I've had people ask why they get the name
of their report appearing in every record instead of the names of people!
The same goes for other keywords; Date is a common one which is best avoided
too as there is a VBA Date function. If in doubt wrap it in brackets, [Name]
or [Date], which tells Access its an object.

If you are using this query as the RecordSource for a report omit the ORDER
BY clause and use the report's internal sorting mechanism to sort it. Its
sometimes said that reports *always* ignore the ORDER BY clause of a query.
That's not actually true, but its best to assume it is and sort within the
report.

Ken Sheridan
Stafford, England

 

[Steve]

Thanks Ken! That's some very valuable advice.

I realised I'd parenthesised it wrongly as soon as I'd sent it, but I was
confident you'd spot this and correct it.

There are various ways you could get round the problem of the zero date
differences. Would converting the zero to 1 where necessary fit the bill?
If so try this:

SELECT [FirstName] & " " & [LastName] AS FullName,
(([PaymentAmount]/(IIF([RenewalDate]-[StartDate])=0,1,
[RenewalDate]-[StartDate]))*365)/12 AS EstMonthlyRev
FROM tblCustomer INNER JOIN tblPayment AS P1 ON tblCustomer.CustomerID =
P1.CustomerID
WHERE (((P1.PaymentDate)=
(SELECT MAX(PaymentDate) FROM tblPayment AS P2
WHERE P2.CustomerID = P1.CustomerID)))
ORDER BY [FirstName] & " " & [LastName];

You'll notice I've changed Name to FullName. Objects in Access have a
Name
property so its best to avoid this as a column heading. It might cause
confusion; on a number of occasions I've had people ask why they get the
name
of their report appearing in every record instead of the names of people!
The same goes for other keywords; Date is a common one which is best
avoided
too as there is a VBA Date function. If in doubt wrap it in brackets,
[Name]
or [Date], which tells Access its an object.

If you are using this query as the RecordSource for a report omit the
ORDER
BY clause and use the report's internal sorting mechanism to sort it. Its
sometimes said that reports *always* ignore the ORDER BY clause of a
query.
That's not actually true, but its best to assume it is and sort within the
report.

Ken Sheridan
Stafford, England

Thanks again Ken.

I have been modifying this to work a little better. I added tblCusotmer
to
it. It's just about perfect. I have a small problem though. There is a
customer in the table that had a StartDate and RenewDate that were the
same.
This is situation is possible and correct. When that happens the result
of
the differnce is zero and we all know we can't divide by zero. Is there
a
way to modify this statement to handle these situations? The query runs
and
just shows an error on for that one record but I couldn't get my report
to
run until I changed the records RenewalDate to something other than the
StartDate. Here is the current statement I'm using:

SELECT [FirstName] & " " & [LastName] AS Name,
(([PaymentAmount]/([RenewalDate]-[StartDate]))*365)/12 AS EstMonthlyRev
FROM tblCustomer INNER JOIN tblPayment AS P1 ON tblCustomer.CustomerID =
P1.CustomerID
WHERE (((P1.PaymentDate)=(SELECT MAX(PaymentDate) FROM tblPayment AS P2
WHERE P2.CustomerID = P1.CustomerID)))
ORDER BY [FirstName] & " " & [LastName];