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
|
|||
|
|||
Calculating days from last meeting held
Good afternoon,
I need to set up a query for a report that shows a list of all active cases by unit and the date of the last TDM Meeting for each case. TDM info is stored in tblTDM: TDM_ID (auto Primary Key) DateTDM TypeTDM Family_ID (foreign key to tblFamily Case info stored in tblFamily: Family_ID CaseNbr CaseName, etc. Case Assignment info stored in tblAssignment: Assignment_ID Family_ID (foreign key to tblFamily) Unit_ID Worker_ID A case can have any number of TDM meetings, but I'm only interested in the last one held. I want to be able to identify for each unit, the date and type of the last TDM for each case in their unit. I'd also like to calculate the date the next meeting is due, which is 90 days from the date of the last meeting. I know this can be done, but I've tried and I'm running out of time. Any help will be most appreciated. -- Thanks, Tina Hudson |
#2
|
|||
|
|||
Calculating days from last meeting held
Tina
Take a look at Access HELP Totals query. If you GroupBy TypeTDM and get the Maximum DateTDM, will that do it? Regards Jeff Boyce Microsoft Office/Access MVP "Tina Hudson" wrote in message news Good afternoon, I need to set up a query for a report that shows a list of all active cases by unit and the date of the last TDM Meeting for each case. TDM info is stored in tblTDM: TDM_ID (auto Primary Key) DateTDM TypeTDM Family_ID (foreign key to tblFamily Case info stored in tblFamily: Family_ID CaseNbr CaseName, etc. Case Assignment info stored in tblAssignment: Assignment_ID Family_ID (foreign key to tblFamily) Unit_ID Worker_ID A case can have any number of TDM meetings, but I'm only interested in the last one held. I want to be able to identify for each unit, the date and type of the last TDM for each case in their unit. I'd also like to calculate the date the next meeting is due, which is 90 days from the date of the last meeting. I know this can be done, but I've tried and I'm running out of time. Any help will be most appreciated. -- Thanks, Tina Hudson |
#3
|
|||
|
|||
Calculating days from last meeting held
Something like the following SQL might work.
SELECT *, DateAdd("d",90,tblTDM.DateTDM) FROM tblAssignment INNER JOIN (tblTDM INNER JOIN (SELECT Family_ID, Max(DateTDM) as LastDate FROM tblTDM) as X ON tblTDM.FamilyID = X.FamilyID AND tblTDM.DateTDM = X.LastDate) ON tblAssignment.FamilyID = tblTDM.FamilyID John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Tina Hudson wrote: Good afternoon, I need to set up a query for a report that shows a list of all active cases by unit and the date of the last TDM Meeting for each case. TDM info is stored in tblTDM: TDM_ID (auto Primary Key) DateTDM TypeTDM Family_ID (foreign key to tblFamily Case info stored in tblFamily: Family_ID CaseNbr CaseName, etc. Case Assignment info stored in tblAssignment: Assignment_ID Family_ID (foreign key to tblFamily) Unit_ID Worker_ID A case can have any number of TDM meetings, but I'm only interested in the last one held. I want to be able to identify for each unit, the date and type of the last TDM for each case in their unit. I'd also like to calculate the date the next meeting is due, which is 90 days from the date of the last meeting. I know this can be done, but I've tried and I'm running out of time. Any help will be most appreciated. |
#4
|
|||
|
|||
Calculating days from last meeting held
Jeff,
I've tried to reply over the past several days, but kept getting error messages. Anyway, I had seen earlier posts to do just what you suggested, but couldn't get it to work. However, when I changed the autonumber key (Family_ID_ to Count instead of Group By, I did get exactly what I needed. Thanks! I also wanted to know how to calculate 90 days out from the Max date and I succeeded in doing this with John Spencer's reply to my original post. My Question: What I'm having trouble with now is selecting specific records that meet date restrictions. I've created a parameter form from which the user can select a Unit (which works), as well as all cases that have a TDM after a certain date that the user is interested in (not working). I've tried using the "greater than symbol" and "Between" but neither work. When I open the report with the query (code below) behind it, I get no records selected. Here is my SQL Code: SELECT Count(tblTDM.TDM_ID) AS CountOfTDM_ID, Max(tblTDM.DateTDM) AS MaxOfDateTDM, tblTDM.Family_ID, Last(tblTDM.TDMHeld) AS LastOfTDMHeld, Last(tblTDM.TDMType) AS LastOfTDMType, tblWorkerAssign.Active, tblFamily.Active, sqryAssignment3.Unit_Name, sqryAssignment3.FullName, tblFamily.Case_Name, tblFamily.CaseNum, DateAdd("d",90,[MaxOfDateTDM]) AS NextTDMDate FROM (tblFamily INNER JOIN tblTDM ON tblFamily.Family_ID = tblTDM.Family_ID) INNER JOIN (tblWorkerAssign INNER JOIN sqryAssignment3 ON tblWorkerAssign.Assignment_ID = sqryAssignment3.Assignment_ID) ON tblFamily.Family_ID = tblWorkerAssign.Family_ID GROUP BY tblTDM.Family_ID, tblWorkerAssign.Active, tblFamily.Active, sqryAssignment3.Unit_Name, sqryAssignment3.FullName, tblFamily.Case_Name, tblFamily.CaseNum HAVING (((tblWorkerAssign.Active)=Yes) AND ((tblFamily.Active)=Yes) AND ((sqryAssignment3.Unit_Name)=[Forms]![frmParamForm_tdmTickler]![cboUnitName]) AND ((DateAdd("d",90,[MaxOfDateTDM]))[Forms]![frmParamForm_tdmTickler]![txtFirstDayWeek])); -- Thanks, Tina Hudson "Jeff Boyce" wrote: Tina Take a look at Access HELP Totals query. If you GroupBy TypeTDM and get the Maximum DateTDM, will that do it? Regards Jeff Boyce Microsoft Office/Access MVP "Tina Hudson" wrote in message news Good afternoon, I need to set up a query for a report that shows a list of all active cases by unit and the date of the last TDM Meeting for each case. TDM info is stored in tblTDM: TDM_ID (auto Primary Key) DateTDM TypeTDM Family_ID (foreign key to tblFamily Case info stored in tblFamily: Family_ID CaseNbr CaseName, etc. Case Assignment info stored in tblAssignment: Assignment_ID Family_ID (foreign key to tblFamily) Unit_ID Worker_ID A case can have any number of TDM meetings, but I'm only interested in the last one held. I want to be able to identify for each unit, the date and type of the last TDM for each case in their unit. I'd also like to calculate the date the next meeting is due, which is 90 days from the date of the last meeting. I know this can be done, but I've tried and I'm running out of time. Any help will be most appreciated. -- Thanks, Tina Hudson |
#5
|
|||
|
|||
Calculating days from last meeting held
Tina
I urge you to start a new post with this (new) topic. Many of your potential viewers won't bother looking 'down-thread' on an existing/answered thread to find your new issue. Regards Jeff Boyce Microsoft Office/Access MVP "Tina Hudson" wrote in message ... Jeff, I've tried to reply over the past several days, but kept getting error messages. Anyway, I had seen earlier posts to do just what you suggested, but couldn't get it to work. However, when I changed the autonumber key (Family_ID_ to Count instead of Group By, I did get exactly what I needed. Thanks! I also wanted to know how to calculate 90 days out from the Max date and I succeeded in doing this with John Spencer's reply to my original post. My Question: What I'm having trouble with now is selecting specific records that meet date restrictions. I've created a parameter form from which the user can select a Unit (which works), as well as all cases that have a TDM after a certain date that the user is interested in (not working). I've tried using the "greater than symbol" and "Between" but neither work. When I open the report with the query (code below) behind it, I get no records selected. Here is my SQL Code: SELECT Count(tblTDM.TDM_ID) AS CountOfTDM_ID, Max(tblTDM.DateTDM) AS MaxOfDateTDM, tblTDM.Family_ID, Last(tblTDM.TDMHeld) AS LastOfTDMHeld, Last(tblTDM.TDMType) AS LastOfTDMType, tblWorkerAssign.Active, tblFamily.Active, sqryAssignment3.Unit_Name, sqryAssignment3.FullName, tblFamily.Case_Name, tblFamily.CaseNum, DateAdd("d",90,[MaxOfDateTDM]) AS NextTDMDate FROM (tblFamily INNER JOIN tblTDM ON tblFamily.Family_ID = tblTDM.Family_ID) INNER JOIN (tblWorkerAssign INNER JOIN sqryAssignment3 ON tblWorkerAssign.Assignment_ID = sqryAssignment3.Assignment_ID) ON tblFamily.Family_ID = tblWorkerAssign.Family_ID GROUP BY tblTDM.Family_ID, tblWorkerAssign.Active, tblFamily.Active, sqryAssignment3.Unit_Name, sqryAssignment3.FullName, tblFamily.Case_Name, tblFamily.CaseNum HAVING (((tblWorkerAssign.Active)=Yes) AND ((tblFamily.Active)=Yes) AND ((sqryAssignment3.Unit_Name)=[Forms]![frmParamForm_tdmTickler]![cboUnitName]) AND ((DateAdd("d",90,[MaxOfDateTDM]))[Forms]![frmParamForm_tdmTickler]![txtFirstDayWeek])); -- Thanks, Tina Hudson "Jeff Boyce" wrote: Tina Take a look at Access HELP Totals query. If you GroupBy TypeTDM and get the Maximum DateTDM, will that do it? Regards Jeff Boyce Microsoft Office/Access MVP "Tina Hudson" wrote in message news Good afternoon, I need to set up a query for a report that shows a list of all active cases by unit and the date of the last TDM Meeting for each case. TDM info is stored in tblTDM: TDM_ID (auto Primary Key) DateTDM TypeTDM Family_ID (foreign key to tblFamily Case info stored in tblFamily: Family_ID CaseNbr CaseName, etc. Case Assignment info stored in tblAssignment: Assignment_ID Family_ID (foreign key to tblFamily) Unit_ID Worker_ID A case can have any number of TDM meetings, but I'm only interested in the last one held. I want to be able to identify for each unit, the date and type of the last TDM for each case in their unit. I'd also like to calculate the date the next meeting is due, which is 90 days from the date of the last meeting. I know this can be done, but I've tried and I'm running out of time. Any help will be most appreciated. -- Thanks, Tina Hudson |
Thread Tools | |
Display Modes | |
|
|