update query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitchell_Collen - 20 Nov 2007 17:13 GMT

Hi, I was hoping someone could tell me where to start with using an update
query in Access. I have a table called AbsenceTable . The AbsenceTable is
updated daily when an employee is absent or tardy. I want comply with our HR
guildlines and send a report to managers of the employees who in a cumulative
12 month period have had more than 3 occurences of an Absence or Tardy.

SELECT Employee, [Absence Type],Date
FROM [Absence Table] INNER JOIN [Absence Type] ON [Absence Table].[Absence
Type] = [Absence Type].[Absence Type]
WHERE ((([Absence Table].Date)<=DateSerial(Year(Date()),Month(Date()),1) And
([Absence Table].Date)>DateSerial(Year(Date()),Month(Date())-12,1)));
********This finds the employees who fall into the past 12 months criteria

I have created a new table called AbsenceHotList where I want to update those
who fall into that category everyday. I can't use an update with an aggregate
query in Access. A report will not work because managers will also manually
update AbsenceHotList with the date and comment of verbal warnings, etc that
are given.

Please help me.
-Misty

Reply to this Message

John Spencer - 20 Nov 2007 18:02 GMT

Don't use an update query at all. Just use a query to get the information
as it is required.

First question is it three absences or three tardy or a combination of 3
absences and tardies.

Step 1 - Identify the Employee's that have 3 or more abcenses
SELECT Employee
FROM [Absence Table] INNER JOIN [Absence Type]
ON [Absence Table].[Absence Type] = [Absence Type].[Absence Type]
WHERE ((([Absence Table].Date)<=DateSerial(Year(Date()),Month(Date()),1) And
([Absence Table].Date)>DateSerial(Year(Date()),Month(Date())-12,1)))
AND [Absence Table].[Absence Type] In ("Absent, Tardy")
HAVING Count([Absence Type]) >= 3

Step 2 - Use that as a sub-query in a where clause to return all the
relevant records in Employees table
SELECT Employees.*
FROM Employees
WHERE Employees.Employee in (
SELECT Employee
FROM [Absence Table] INNER JOIN [Absence Type]
ON [Absence Table].[Absence Type] = [Absence Type].[Absence Type]
WHERE ((([Absence Table].Date)<=DateSerial(Year(Date()),Month(Date()),1) And
([Absence Table].Date)>DateSerial(Year(Date()),Month(Date())-12,1)))
AND [Absence Table].[Absence Type] In ("Absent, Tardy")
HAVING Count([Absence Type]) >= 3)

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Hi, I was hoping someone could tell me where to start with using an update
> query in Access. I have a table called AbsenceTable . The AbsenceTable is
[quoted text clipped - 24 lines]
> Please help me.
> -Misty

Reply to this Message

Mitchell_Collen - 20 Nov 2007 18:22 GMT

John,
Thanks for your code. I am going to apply it now. To answer your question,
its three tardy or three absences. No combination option, <--if we did that
they would need to terminate a lot of staff. Anyway, I am going to apply this
and see what happens. I will reply again in a bit.
Thanks again!! -Misty

>Don't use an update query at all. Just use a query to get the information
>as it is required.
[quoted text clipped - 29 lines]
>> Please help me.
>> -Misty

Reply to this Message

John Spencer - 20 Nov 2007 20:31 GMT

IF the Absences have to be 3 or more of one type then you can modify the
query slightly (see the addition of the group by clause)

SELECT Employee
FROM [Absence Table] INNER JOIN [Absence Type]
ON [Absence Table].[Absence Type] = [Absence Type].[Absence Type]
WHERE ((([Absence Table].Date)<=DateSerial(Year(Date()),Month(Date()),1) And
([Absence Table].Date)>DateSerial(Year(Date()),Month(Date())-12,1)))
AND [Absence Table].[Absence Type] In ("Absent, Tardy")
GROUP BY [Absence Table].[Absence Type]
HAVING Count([Absence Type]) >= 3

Step 2 - Use that as a sub-query in a where clause to return all the
relevant records in Employees table
SELECT Employees.*
FROM Employees
WHERE Employees.Employee in (
SELECT Employee
FROM [Absence Table] INNER JOIN [Absence Type]
ON [Absence Table].[Absence Type] = [Absence Type].[Absence Type]
WHERE ((([Absence Table].Date)<=DateSerial(Year(Date()),Month(Date()),1) And
([Absence Table].Date)>DateSerial(Year(Date()),Month(Date())-12,1)))
AND [Absence Table].[Absence Type] In ("Absent, Tardy")
GROUP BY [Absence Table].[Absence Type]
HAVING Count([Absence Type]) >= 3)

Step 2 - Use that as a sub-query in a where clause to return all the
relevant records in Employees table
SELECT Employees.*
FROM Employees
WHERE Employees.Employee in (
SELECT Employee
FROM [Absence Table] INNER JOIN [Absence Type]
ON [Absence Table].[Absence Type] = [Absence Type].[Absence Type]
WHERE ((([Absence Table].Date)<=DateSerial(Year(Date()),Month(Date()),1) And
([Absence Table].Date)>DateSerial(Year(Date()),Month(Date())-12,1)))
AND [Absence Table].[Absence Type] In ("Absent, Tardy")
HAVING Count([Absence Type]) >= 3)

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Don't use an update query at all. Just use a query to get the information
> as it is required.
[quoted text clipped - 57 lines]
>> Please help me.
>> -Misty

Reply to this Message

Mitchell_Collen - 20 Nov 2007 22:19 GMT

Thanks John. You are so very very helpful!!
Misty

>IF the Absences have to be 3 or more of one type then you can modify the
>query slightly (see the addition of the group by clause)
[quoted text clipped - 40 lines]
>>> Please help me.
>>> -Misty

Reply to this Message