query for record for last 90 days

L

Linda RQ

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;
 
K

kingston via AccessMonster.com

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)
 
L

Linda RQ

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 said:
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 said:
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;
Click to expand...
Click to expand...
 
L

Linda RQ

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 said:
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 said:
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;
Click to expand...
Click to expand...
 
K

kingston via AccessMonster.com

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 said:
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
Click to expand...
[quoted text clipped - 43 lines]
Click to expand...
 
K

kingston via 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 said:
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
Click to expand...
[quoted text clipped - 43 lines]
Click to expand...
 
Top