SORT QUESTION

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use a query to keep track of sick days. The query only keeps the days that are less than a year old, which I want. The problem I have is that when somebody does not call in sick for 6 months, his sick days need to be reset to 0. This is the part where I need help. The query will return the sick days that are less than a year old. However, when the last sick day is more than 6 months old from today, I do not want the query to return the sick days. In that instance I want the query to give me only the days that are less than 6 months old. I created a date field, Expr1, with a date that is 6 months ago from today. I tried to use the criteria >Between [Expr1] and Date() is null, which didn't work. Can somebody help me

old I created a date field with a date of six month ago as of the current date. I was wrong by thinking that the Between function would work.
 
Bert said:
I use a query to keep track of sick days. The query only keeps the days
Click to expand...
that are less than a year old, which I want. The problem I have is that when
somebody does not call in sick for 6 months, his sick days need to be reset
to 0. This is the part where I need help. The query will return the sick
days that are less than a year old. However, when the last sick day is more
than 6 months old from today, I do not want the query to return the sick
days. In that instance I want the query to give me only the days that are
less than 6 months old. I created a date field, Expr1, with a date that is 6
months ago from today. I tried to use the criteria >Between [Expr1] and
Date() is null, which didn't work. Can somebody help me?
old I created a date field with a date of six month ago as of the current
Click to expand...
date. I was wrong by thinking that the Between function would work.




Hmmm, if I understand correctly, you want the Query to return only a total
of all sick leave dates that are less than six months old?

CREATE TABLE Employees
(EmployeeID INTEGER
,TotalSickDays BYTE
,CONSTRAINT pk_Employees PRIMARY KEY (EmployeeID)
)

Sample Data
1, 3
2, 0
3, 1

CREATE TABLE SickDates
(EmployeeID INTEGER
,SickDate DATE
,CONSTRAINT pk_SickDates PRIMARY KEY (EmployeeID, SickDate)
,CONSTRAINT fk_SickDates_Employess FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
)

Sample Data
1, 09/01/03
1, 02/01/04
1, 03/01/04
2, 03/16/04
3, 09/15/03


SELECT S1.EmployeeID
,COUNT(S1.SickDate) As TotalSickDays
FROM SickDates AS S1
WHERE (S1.SickDate > (DateSerial(Year(Date()), Month(Date()) - 6,
Day(Date()))
GROUP BY S1.EmployeeID

Should Return the quantity of all Sickdates since a date six months ago.

From the context of the description above, it sounds like there is a total
Sick Days column somewhere, possibly in the Employee Table (I'm not sure,
either way). But it is mentioned that "sick days need to be reset to 0".

Trying to use the above query (even including tricks to preseve the
indication of zero quantity of sick days) as a source in an Update Query
will fail, because there is Aggregation, and Access will throw its
ripping-out-hair-at-the-roots "Operation Must use an Updateable query"
error.

This query will have to be translated into a Make-Table Query, the new
table (strangely updateable with excactly the same information) will have to
be used to update any Total of Sick days in another table, and then the
temporary table will have to be deleted. This can be handled as a Macro,
but would be better off controlled from VBA.


Sincerely,

Chris O.
 
Thanks for the help, however it will only give me the dates that are more than 6 months old. This is not exactly what I am looking for. I want all the dates when one of them is not older than 6 months and at the same time I do not want any dates when none of them is older than 6 months.
 
I use a query to keep track of sick days. The query only keeps the days that are less than a year old, which I want. The problem I have is that when somebody does not call in sick for 6 months, his sick days need to be reset to 0. This is the part where I need help. The query will return the sick days that are less than a year old. However, when the last sick day is more than 6 months old from today, I do not want the query to return the sick days. In that instance I want the query to give me only the days that are less than 6 months old. I created a date field, Expr1, with a date that is 6 months ago from today. I tried to use the criteria >Between [Expr1] and Date() is null, which didn't work. Can somebody help me?


old I created a date field with a date of six month ago as of the current date. I was wrong by thinking that the Between function would work.
Click to expand...

Bert, did you ever get this figured out? I'm not sure I yet understand
what records you want to retrieve!
 
Thanks for the inquiry. No I didn't figure it out yet. This is what I would like to do. I have a date field and I am looking for a criteria that will do the following; give me all the dates if one of them is not older than 6 months. Do not give me any dates if they are all older than 6 months.
Click to expand...

SELECT <whatever> FROM <whereever>
WHERE <primary key> IN
(SELECT primarykey FROM tablename WHERE datefield BETWEEN
DateAdd("m", -6, Date()) AND Date());
 
Almost got it to work with the criteria BETWEEN DateAdd("m", -6, Date()) AND Date()); It will not give me any dates when all of them are older than 6 months. However, when one of them is not older than 6 months, it will give me only that one dat while I need to get all dates. Thanks for the help.
 
Try this

SELECT SickDays.User, SickDays.[Sick Day
FROM SickDay
WHERE SickDays.User IN (SELECT SickDays.User FROM SickDays WHERE DateDiff("d",[Sick Day],Now()) < 182 )

Do the initial SQL statement
SELECT SickDays.User FROM SickDays WHERE DateDiff("d",[Sick Day],Now()) < 182
To determine who has a sick day in the last 6 months

Use the results of the query to get the sick days of all the people who have at least one sick day in the last 6 months

Hope this helps

Jim
 
Almost got it to work with the criteria BETWEEN DateAdd("m", -6, Date()) AND Date()); It will not give me any dates when all of them are older than 6 months. However, when one of them is not older than 6 months, it will give me only that one dat while I need to get all dates. Thanks for the help.
Click to expand...

ok... sorry...

A criterion of

WHERE EXISTS(SELECT ID FROM table AS X WHERE datefield BETWEEN
DateAdd("m", -6, Date()) AND Date()) AND X.ID = table.ID)

will do the trick.
 
Do not understand X.ID = table.ID. I assume that X.ID is the date field in which I put the criteria, but what do you mean with table.ID?
Click to expand...

ID would be the Primary Key of the parent table (EmployeeID I'd
guess). Sorry, I didn't explain myself very well!

This is NOT a criterion. This would be the entire SQL string; it's a
"correlated subquery", and the query within parentheses must reference
the outer query.
 
For some reason I do not get it to work. I have as an example, 3 records in one date field. One record is 02/02/03, the other one is 05/05/03 and the third one is 10/05/03. I want to see all three records because the last record is less than 6 months old. I have no problem in getting those records. The problem is that when all 3 records are more than 6 months old, I do not want to see them. I thought that I needed to use a criteria like "Between DateAdd("m",-6,date()) And Date() Is Null", however that doesn't seem to work.
 
For some reason I do not get it to work. I have as an example, 3 records in one date field. One record is 02/02/03, the other one is 05/05/03 and the third one is 10/05/03. I want to see all three records because the last record is less than 6 months old. I have no problem in getting those records. The problem is that when all 3 records are more than 6 months old, I do not want to see them. I thought that I needed to use a criteria like "Between DateAdd("m",-6,date()) And Date() Is Null", however that doesn't seem to work.
Click to expand...

Well, the expression "BETWEEN DateAdd("m",-6,date()) And Date()" will
always be either TRUE (if the date field for this record is less than
six months old) or FALSE (if it's older or in the future). It will
never be NULL, so your query will never return any records.

Please post the actual SQL string of your query.
 
First of all I apologize for the confusion I am causing. Her is my SQL string
SELECT [Absenteeism Query].AbsenteeismID, [Absenteeism Query].[First Day], [Absenteeism Query].[Last Day], [Absenteeism Query].I
FROM [Security Associates Query] INNER JOIN [Absenteeism Query] ON [Security Associates Query].ID = [Absenteeism Query].I
ORDER BY [Absenteeism Query].[First Day]
As you can see, I have several dates that I want to see because the last record is 10/20/03, less than 6 months old. Now if the last record is more than 6 months old, ie 09/01/03, I do not want to see any of my records
 
Bert said:
Thanks for the help, however it will only give me the dates that are more
Click to expand...
than 6 months old. This is not exactly what I am looking for. I want all the
dates when one of them is not older than 6 months and at the same time I do
not want any dates when none of them is older than 6 months.


Bert,

Sorry I didn't see your reply earlier.

The Original Test Tables and Data:


CREATE TABLE Employees
(EmployeeID INTEGER
,TotalSickDays BYTE
,CONSTRAINT pk_Employees PRIMARY KEY (EmployeeID)
)

Sample Data
1, 3
2, 0
3, 1

CREATE TABLE SickDates
(EmployeeID INTEGER
,SickDate DATE
,CONSTRAINT pk_SickDates PRIMARY KEY (EmployeeID, SickDate)
,CONSTRAINT fk_SickDates_Employess FOREIGN KEY (EmployeeID)
REFERENCES Employees (EmployeeID)
)

Sample Data
1, 09/01/03
1, 02/01/04
1, 03/01/04
2, 03/16/04
3, 09/15/03

The Query:

SELECT S1.EmployeeID
,COUNT(S1.SickDate) As TotalSickDays
FROM SickDates AS S1
WHERE (S1.SickDate > (DateSerial(Year(Date()), Month(Date()) - 6,
Day(Date())))
GROUP BY S1.EmployeeID

Note: I missed a ) at the end of the WHERE clause the first time around, I
must have failed to update the text in the post after testing, because the
test Query in my help db is working ok.


The Output Results of the Query were:

EmployeeID, TotalSickDays
1, 2
2, 1

If you check the Sample data, you'll see that it is returning Sick Days
from the last six months, not more than six months.
If it were returning dates from more than six months ago, EmployeeID value
1 would return a COUNT of 1, instead of a COUNT of 2. EmployeeID value 2
would not have a row. And EmployeeID value 3 would have had a COUNT of 1
(at least when I originaly wrote the Query it would have).


Sincerely,

Chris O.
 
Back
Top