Calculating days from last meeting held

  • Thread starter Thread starter Tina Hudson
  • Start date Start date
T

Tina Hudson

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.
 
Tina

Take a look at Access HELP re: Totals query.

If you GroupBy TypeTDM and get the Maximum DateTDM, will that do it?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
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
 
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 said:
Tina

Take a look at Access HELP re: Totals query.

If you GroupBy TypeTDM and get the Maximum DateTDM, will that do it?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
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 said:
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]));
Click to expand...
 
Back
Top