Hi Everyone,


My Goal is to show a sum of USTOPoints for each employee for the last 90 
days.  If an employee has 5 points total for a 90 day period, we need to 
start a
discipline process.


Using Access 2003.  I have a query that I'll put into a report, the sql is 
below but I use the query grid to create my queries so please let me know 
what I have to add that way.  My problem is that I need a sum of the 
USTOPoints for the last 90 days from the most recent date of USTODate for 
each employee not just 90 days from today.  Can this be done as a group 
report?  My Tables and Query are below.  If I'm on the wrong track and there 
is a better way to do this, let me know...........Thanks, Linda

UnscheduledTimeOffID
EmployeeID_fk
USTOTypesID_fk
USTODate

UnscheduledTimeOffTypesID
USTODescription
USTOPoints

FandLName
Name
strEmployeeID


SELECT tblUnscheduledTimeOff.USTODate, 
tblUnscheduledTimeOff.UnscheduledTimeOffID, 
tblUnscheduledTimeOff.EmployeeID_fk, tblUnscheduledTimeOff.USTOTypesID_fk, 
tblUnscheduledTimeOffTypes.USTOPoints, 
tblUnscheduledTimeOffTypes.USTODescription, qryDepartmentEmployees.Name
FROM (tblUnscheduledTimeOffTypes INNER JOIN tblUnscheduledTimeOff ON 
tblUnscheduledTimeOffTypes.UnscheduledTimeOffTypesID = 
tblUnscheduledTimeOff.USTOTypesID_fk) INNER JOIN qryDepartmentEmployees ON 
tblUnscheduledTimeOff.EmployeeID_fk = qryDepartmentEmployees.strEmployeeID;

You probably already know that you can use the criteria Between...And...  I
would also use the function DMax() to find the most recent date for an
employee:

Between DMax("[USTODate]","[UnscheduledTimeOffID]","[EmployeeID_fk]=" &
[EmployeeID_fk]) And (DMax(...)-90)


Linda RQ wrote:
>Hi Everyone,
>
>My Goal is to show a sum of USTOPoints for each employee for the last 90 
>days.  If an employee has 5 points total for a 90 day period, we need to 
>start a
>discipline process.
>
>Using Access 2003.  I have a query that I'll put into a report, the sql is 
>below but I use the query grid to create my queries so please let me know 
>what I have to add that way.  My problem is that I need a sum of the 
>USTOPoints for the last 90 days from the most recent date of USTODate for 
>each employee not just 90 days from today.  Can this be done as a group 
>report?  My Tables and Query are below.  If I'm on the wrong track and there 
>is a better way to do this, let me know...........Thanks, Linda
>
>UnscheduledTimeOffID
>EmployeeID_fk
>USTOTypesID_fk
>USTODate
>
>UnscheduledTimeOffTypesID
>USTODescription
>USTOPoints
>
>FandLName
>Name
>strEmployeeID
>
>SELECT tblUnscheduledTimeOff.USTODate, 
>tblUnscheduledTimeOff.UnscheduledTimeOffID, 
>tblUnscheduledTimeOff.EmployeeID_fk, tblUnscheduledTimeOff.USTOTypesID_fk, 
>tblUnscheduledTimeOffTypes.USTOPoints, 
>tblUnscheduledTimeOffTypes.USTODescription, qryDepartmentEmployees.Name
>FROM (tblUnscheduledTimeOffTypes INNER JOIN tblUnscheduledTimeOff ON 
>tblUnscheduledTimeOffTypes.UnscheduledTimeOffTypesID = 
>tblUnscheduledTimeOff.USTOTypesID_fk) INNER JOIN qryDepartmentEmployees ON 
>tblUnscheduledTimeOff.EmployeeID_fk = qryDepartmentEmployees.strEmployeeID;

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1

I do know about both but I don't know how to apply many of the criteria and 
functions yet.  I'll give it a whirl.

So this will calculate based on each individual's last date of unscheduled 
Time Off?  This time stuff gets me all mixed up, I always think it's using 
today to calculate everything.

Thanks,
Linda


"kingston via AccessMonster.com" <u27511@uwe> wrote in message 
news:74a13bbcfba2f@uwe...
> You probably already know that you can use the criteria Between...And... 
> I
> would also use the function DMax() to find the most recent date for an
> employee:
>
> Between DMax("[USTODate]","[UnscheduledTimeOffID]","[EmployeeID_fk]=" &
> [EmployeeID_fk]) And (DMax(...)-90)
>
>
> Linda RQ wrote:
>>Hi Everyone,
>>
>>My Goal is to show a sum of USTOPoints for each employee for the last 90
>>days.  If an employee has 5 points total for a 90 day period, we need to
>>start a
>>discipline process.
>>
>>Using Access 2003.  I have a query that I'll put into a report, the sql is
>>below but I use the query grid to create my queries so please let me know
>>what I have to add that way.  My problem is that I need a sum of the
>>USTOPoints for the last 90 days from the most recent date of USTODate for
>>each employee not just 90 days from today.  Can this be done as a group
>>report?  My Tables and Query are below.  If I'm on the wrong track and 
>>there
>>is a better way to do this, let me know...........Thanks, Linda
>>
>>UnscheduledTimeOffID
>>EmployeeID_fk
>>USTOTypesID_fk
>>USTODate
>>
>>UnscheduledTimeOffTypesID
>>USTODescription
>>USTOPoints
>>
>>FandLName
>>Name
>>strEmployeeID
>>
>>SELECT tblUnscheduledTimeOff.USTODate,
>>tblUnscheduledTimeOff.UnscheduledTimeOffID,
>>tblUnscheduledTimeOff.EmployeeID_fk, tblUnscheduledTimeOff.USTOTypesID_fk,
>>tblUnscheduledTimeOffTypes.USTOPoints,
>>tblUnscheduledTimeOffTypes.USTODescription, qryDepartmentEmployees.Name
>>FROM (tblUnscheduledTimeOffTypes INNER JOIN tblUnscheduledTimeOff ON
>>tblUnscheduledTimeOffTypes.UnscheduledTimeOffTypesID =
>>tblUnscheduledTimeOff.USTOTypesID_fk) INNER JOIN qryDepartmentEmployees ON
>>tblUnscheduledTimeOff.EmployeeID_fk = 
>>qryDepartmentEmployees.strEmployeeID;
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1
> 

I pasted this in the criteria row in the USTODate column in the query grid 
and I get an error that says The expression entered has the wrong number of 
arguments.

Thanks,
Linda

"kingston via AccessMonster.com" <u27511@uwe> wrote in message 
news:74a13bbcfba2f@uwe...
> You probably already know that you can use the criteria Between...And... 
> I
> would also use the function DMax() to find the most recent date for an
> employee:
>
> Between DMax("[USTODate]","[UnscheduledTimeOffID]","[EmployeeID_fk]=" &
> [EmployeeID_fk]) And (DMax(...)-90)
>
>
> Linda RQ wrote:
>>Hi Everyone,
>>
>>My Goal is to show a sum of USTOPoints for each employee for the last 90
>>days.  If an employee has 5 points total for a 90 day period, we need to
>>start a
>>discipline process.
>>
>>Using Access 2003.  I have a query that I'll put into a report, the sql is
>>below but I use the query grid to create my queries so please let me know
>>what I have to add that way.  My problem is that I need a sum of the
>>USTOPoints for the last 90 days from the most recent date of USTODate for
>>each employee not just 90 days from today.  Can this be done as a group
>>report?  My Tables and Query are below.  If I'm on the wrong track and 
>>there
>>is a better way to do this, let me know...........Thanks, Linda
>>
>>UnscheduledTimeOffID
>>EmployeeID_fk
>>USTOTypesID_fk
>>USTODate
>>
>>UnscheduledTimeOffTypesID
>>USTODescription
>>USTOPoints
>>
>>FandLName
>>Name
>>strEmployeeID
>>
>>SELECT tblUnscheduledTimeOff.USTODate,
>>tblUnscheduledTimeOff.UnscheduledTimeOffID,
>>tblUnscheduledTimeOff.EmployeeID_fk, tblUnscheduledTimeOff.USTOTypesID_fk,
>>tblUnscheduledTimeOffTypes.USTOPoints,
>>tblUnscheduledTimeOffTypes.USTODescription, qryDepartmentEmployees.Name
>>FROM (tblUnscheduledTimeOffTypes INNER JOIN tblUnscheduledTimeOff ON
>>tblUnscheduledTimeOffTypes.UnscheduledTimeOffTypesID =
>>tblUnscheduledTimeOff.USTOTypesID_fk) INNER JOIN qryDepartmentEmployees ON
>>tblUnscheduledTimeOff.EmployeeID_fk = 
>>qryDepartmentEmployees.strEmployeeID;
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1
> 

Create an intermediate query with the calculated field LastUSTODate: DMax("
[USTODate]","[UnscheduledTimeOff]","[EmployeeID_fk]=" & [EmployeeID_fk]).

In other words, your query will have the EmployeeID... and the most current
USTODate.  Then in the final query (or report), this date would be available
and you can use it in the Between...And... criteria.  I think I got your
table name wrong earlier; sorry for the confusion.  Make sure the DMax()
function works for you in a query and I think the rest will be obvious.

Linda RQ wrote:
>I pasted this in the criteria row in the USTODate column in the query grid 
>and I get an error that says The expression entered has the wrong number of 
>arguments.
>
>Thanks,
>Linda
>
>> You probably already know that you can use the criteria Between...And... 
>> I
>[quoted text clipped - 43 lines]
>>>tblUnscheduledTimeOff.EmployeeID_fk = 
>>>qryDepartmentEmployees.strEmployeeID;

-- 
Message posted via http://www.accessmonster.com

Your intermediate query would be something like this:

SELECT tblUnscheduledTimeOff.*, DMax("[USTODate]","[tblUnscheduledTimeOff;]",
"[EmployeeID_fk]=" & [EmployeeID_fk])
FROM tblUnscheduledTimeOff;

This assumes that EmployeeID_fk is a number.  If it isn't put single quotes
around the value:

..."[EmployeeID_fk]='" & [EmployeeID_fk] & "'")

Linda RQ wrote:
>I pasted this in the criteria row in the USTODate column in the query grid 
>and I get an error that says The expression entered has the wrong number of 
>arguments.
>
>Thanks,
>Linda
>
>> You probably already know that you can use the criteria Between...And... 
>> I
>[quoted text clipped - 43 lines]
>>>tblUnscheduledTimeOff.EmployeeID_fk = 
>>>qryDepartmentEmployees.strEmployeeID;

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1