Most Recent Contract

[timbits35 via AccessMonster.com]

Hi,

I have a database that tracks yearly plans for a personal trainer. Each
athlete usually renews every year and therefore one athlete can have many
contracts. I have a contract date in the sales table and then I have a query
where I calculate the renew date as 364 days later than the contract date. In
my query I would like to show only the most recent contract for each athlete.
I have tried the totals query and the putting Max in the Renew date
calculated field which didn't work nor did putting Max in the contract date.
I do not get the most recent contract. Here is the SQL which Access generated
: All other fields in the query say Group By.

SELECT tblmembers.Lname, tblmembers.Fname, tblsales.contractyear, tblsales.
contractdate, Max(IIf(tblsalesdetails!ServiceID=8,[contractdate]+182,
[contractdate]+364)) AS renewdate
FROM tblservices INNER JOIN ((tblmembers INNER JOIN tblsales ON tblmembers.
MemberID = tblsales.MemberID) INNER JOIN tblsalesdetails ON tblsales.SalesID
= tblsalesdetails.SalesID) ON tblservices.ServiceID = tblsalesdetails.
ServiceID
GROUP BY tblmembers.Lname, tblmembers.Fname, tblsales.contractyear, tblsales.
contractdate, tblservices.ServiceID
HAVING (((tblservices.ServiceID)<>1))
ORDER BY Max(IIf(tblsalesdetails!ServiceID=8,[contractdate]+182,[contractdate]
+364));

The calculations you see for 182 or 364 depend on whether it may be a 6month
or 1 year contract.

Thank you,
Liane

 

[Ken Snell [MVP]]

This is done using a subquery. Here is generic example (using your field and
table names) for how to do this:

SELECT tblmembers.LName, tblsalesdetails.contractdate
FROM (tblmembers INNER JOIN tblsales
ON tblmembers.MemberID = tblsales.MemberID)
INNER JOIN tblsalesdetails ON
tblsales.SalesID = tblsalesdetails.SalesID
WHERE tblsalesdetails.contractdate =
(SELECT Max(T.contractdate) AS MCD
FROM tblsalesdetails AS T
WHERE T.SalesID = tblsalesdetails.SalesID);

 

[timbits35 via AccessMonster.com]

Hi,

To Ken,

I have tried your solution but it is not completely working. Also, I am not
sure if you have mistaken one of my fields. The contract date comes from the
tblsales not the tblsalesdetails. Right now my query returns the maximum
contract date period, not the maximum date for each member id. Please give
further details.

PS I am a newbie at subqueries so I find it hard to follow when you use
abbreviations like MCD etc.

Here is my revised SQL.

SELECT tblmembers.Lname, tblmembers.Fname, tblsales.contractyear, tblsales.
contractdate, IIf(tblsalesdetails!ServiceID=8,[contractdate]+182,
[contractdate]+364) AS renewdate, tblcoach.Name, tblservices.Description
FROM tblservices INNER JOIN (((tblcoach INNER JOIN tblmembers ON tblcoach.
CoachID=tblmembers.Coach) INNER JOIN tblsales ON tblmembers.MemberID=tblsales.
MemberID) INNER JOIN tblsalesdetails ON tblsales.SalesID=tblsalesdetails.
SalesID) ON tblservices.ServiceID=tblsalesdetails.ServiceID
WHERE (((tblsales.contractdate)=(SELECT Max(tblsales.contractdate) AS
MaxOfcontractdate FROM tblsales)) AND ((tblservices.ServiceID)<>1))
ORDER BY IIf(tblsalesdetails!ServiceID=8,[contractdate]+182,[contractdate]
+364);

Thank you,
Liane

 

[Ken Snell [MVP]]

You're close. You need to include the MemberID value in the subquery so that
it looks only at the records for a specific MemberID. I also aliased the
table in the subquery so that ACCESS won't become confused about which
instance of the table to use:

SELECT tblmembers.Lname, tblmembers.Fname, tblsales.contractyear, tblsales.
contractdate, IIf(tblsalesdetails!ServiceID=8,[contractdate]+182,
[contractdate]+364) AS renewdate, tblcoach.Name, tblservices.Description
FROM tblservices INNER JOIN (((tblcoach INNER JOIN tblmembers ON tblcoach.
CoachID=tblmembers.Coach) INNER JOIN tblsales ON
tblmembers.MemberID=tblsales.
MemberID) INNER JOIN tblsalesdetails ON tblsales.SalesID=tblsalesdetails.
SalesID) ON tblservices.ServiceID=tblsalesdetails.ServiceID
WHERE (((tblsales.contractdate)=(SELECT Max(T.contractdate) AS
MaxOfcontractdate FROM tblsales AS T
WHERE T.MemberID = tblsales.MemberID)) AND ((tblservices.ServiceID)<>1))
ORDER BY IIf(tblsalesdetails!ServiceID=8,[contractdate]+182,[contractdate]
+364);

 

[timbits35 via AccessMonster.com]

Thank-you

I copied and pasted your SQL and it worked. Can you refer me to some good
website to learn about aliases as this is the first that I have heard of them?
Can you also suggest a more intuitive alias than just using the letter T? Is
a longer word possible?

Thank-you,
Liane

 

[Ken Snell [MVP]]

This website has some introductory information about table aliases in SQL
statements:
Access SQL: FROM clause
http://office.microsoft.com/en-us/access/HA102769241033.aspx


You can use any word you want as an alias, so long as it is not a reserved
word and is not the name of another table that is already in the query. So,
instead of using T, you could use tblsales_subquery as the alias if you
want.