update query

[Mitchell_Collen via AccessMonster.com]

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

 

[John Spencer]

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)


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

 

[Mitchell_Collen via AccessMonster.com]

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.

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)

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

 

[John Spencer]

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)


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

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)


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

 

[Mitchell_Collen via AccessMonster.com]

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)

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)

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]