A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating days from last meeting held



 
 
Thread Tools Display Modes
  #1  
Old July 1st, 2009, 07:55 PM posted to microsoft.public.access.queries
Tina Hudson
external usenet poster
 
Posts: 22
Default 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  
Old July 1st, 2009, 08:16 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old July 1st, 2009, 09:58 PM posted to microsoft.public.access.queries
John Spencer MVP
external usenet poster
 
Posts: 533
Default 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  
Old July 8th, 2009, 08:08 PM posted to microsoft.public.access.queries
Tina Hudson
external usenet poster
 
Posts: 22
Default 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  
Old July 9th, 2009, 05:26 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.