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
|
|||
|
|||
Most Recent Contract
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 -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Most Recent Contract
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); -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "timbits35 via AccessMonster.com" u34694@uwe wrote in message news:98e544851a9fc@uwe... 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 -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Most Recent Contract
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 -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Most Recent Contract
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); -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "timbits35 via AccessMonster.com" u34694@uwe wrote in message news:98eb4020dc401@uwe... 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 -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Most Recent Contract
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200907/1 |
#6
|
|||
|
|||
Most Recent Contract
This website has some introductory information about table aliases in SQL
statements: Access SQL: FROM clause http://office.microsoft.com/en-us/ac...769241033.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. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "timbits35 via AccessMonster.com" u34694@uwe wrote in message news:98f0c565f781a@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200907/1 |
Thread Tools | |
Display Modes | |
|
|