Two or more consecutive days

[dreamsoul620 via AccessMonster.com]

Hi! I'm trying to write a query or set of queries that will show employees
that have either forgot to swipe their time card or did not show up to work
at all for 2 or more consecutive days. Once I get this to work, I can
automatically generate the report from this data. Currently, I have two
queries, but only part of my results are showing. Any help on why this is
happening or how to show all the results would be great.

This query just selects the correct codes from my table. This works great!

SELECT EARLY_LEAVE_POINTS.ID, EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.
EMPNUM, EARLY_LEAVE_POINTS.DATE, EARLY_LEAVE_POINTS.USTR1, EARLY_LEAVE_POINTS.
FCLASS, EARLY_LEAVE_POINTS.LENT1, EARLY_LEAVE_POINTS.MINUTES,
EARLY_LEAVE_POINTS.POINTS, EARLY_LEAVE_POINTS.REASON
FROM EARLY_LEAVE_POINTS
WHERE (((EARLY_LEAVE_POINTS.REASON) In ("FPI = Forgot to punch in","FPO =
Forgot to punch out","FTC = Forgot time card","Missed punch","NO CALL / NO
SHOW","ABSENT - NO CODE")))
ORDER BY EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.DATE;


This is based off the first one. An example of the query not showing
everything is that Employee A had Missed Punch as their reason two days in a
row, but the query only returns the first of the two dates. For another
employee, they have 2 days with Missed Punch and 2 more days with No Call and
all four dates are shown, which is correct. Why would it show correct for
the second, but not the first?

SELECT ID, EMPNAME, DATE,REASON
FROM CONSEC1A AS A
WHERE(SELECT COUNT(*)
FROM CONSEC1A AS B
WHERE B.DATE >= A.DATE
AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

 

[Smartin]

dreamsoul620 via AccessMonster.com wrote:
[snip]

This is based off the first one. An example of the query not showing
everything is that Employee A had Missed Punch as their reason two days in a
row, but the query only returns the first of the two dates. For another
employee, they have 2 days with Missed Punch and 2 more days with No Call and
all four dates are shown, which is correct. Why would it show correct for
the second, but not the first?

SELECT ID, EMPNAME, DATE,REASON
FROM CONSEC1A AS A
WHERE(SELECT COUNT(*)
FROM CONSEC1A AS B
WHERE B.DATE >= A.DATE
AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

I don't think your query will return the last row in a sequence because
on that final row, the test

(SELECT COUNT(*)
FROM CONSEC1A AS B
WHERE B.DATE >= A.DATE
AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

will fail, as it only returns a count of 1.

I'm a bit foggy today, but you could try a strategy like

SELECT ID, EMPNAME, DATE,REASON
FROM CONSEC1A AS A
WHERE (
SELECT Abs(DateDiff('D', B.Date, A.Date))
FROM CONSEC1A AS B
WHERE B.EMPNAME = A.EMPNAME
) = 1;

Also, using "date" as a field name is not recommended as it is a
reserved word (a function, returning the current date). I suggest you
change your field names and subordinates appropriately, or at the very
least, you should enclose the field name in [square brackets] in queries
and expressions.

 

[dreamsoul620 via AccessMonster.com]

When I try that, no records are returned. Is there another way to show all
the rows in the query? I'll keep playing with the code you gave me as well.
Thanks for the quick reply.

[snip]

This is based off the first one. An example of the query not showing
everything is that Employee A had Missed Punch as their reason two days in a

[quoted text clipped - 10 lines]

AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

I don't think your query will return the last row in a sequence because
on that final row, the test

(SELECT COUNT(*)
FROM CONSEC1A AS B
WHERE B.DATE >= A.DATE
AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

will fail, as it only returns a count of 1.

I'm a bit foggy today, but you could try a strategy like

SELECT ID, EMPNAME, DATE,REASON
FROM CONSEC1A AS A
WHERE (
SELECT Abs(DateDiff('D', B.Date, A.Date))
FROM CONSEC1A AS B
WHERE B.EMPNAME = A.EMPNAME
) = 1;

Also, using "date" as a field name is not recommended as it is a
reserved word (a function, returning the current date). I suggest you
change your field names and subordinates appropriately, or at the very
least, you should enclose the field name in [square brackets] in queries
and expressions.

 

[John Spencer]

How about the following

SELECT EARLY_LEAVE_POINTS.ID, EARLY_LEAVE_POINTS.EMPNAME,
EARLY_LEAVE_POINTS.
EMPNUM, EARLY_LEAVE_POINTS.DATE, EARLY_LEAVE_POINTS.USTR1,
EARLY_LEAVE_POINTS.
FCLASS, EARLY_LEAVE_POINTS.LENT1, EARLY_LEAVE_POINTS.MINUTES,
EARLY_LEAVE_POINTS.POINTS, EARLY_LEAVE_POINTS.REASON
FROM EARLY_LEAVE_POINTS
WHERE (((EARLY_LEAVE_POINTS.REASON) In ("FPI = Forgot to punch in","FPO =
Forgot to punch out","FTC = Forgot time card","Missed punch","NO CALL / NO
SHOW","ABSENT - NO CODE")))
ORDER BY EARLY_LEAVE_POINTS.EMPNAME, EARLY_LEAVE_POINTS.DATE;


This is based off the first one. An example of the query not showing
everything is that Employee A had Missed Punch as their reason two days in a
row, but the query only returns the first of the two dates. For another
employee, they have 2 days with Missed Punch and 2 more days with No Call
and
all four dates are shown, which is correct. Why would it show correct for
the second, but not the first?

SELECT A.ID, A.EMPNAME, A.DATE, A.REASON, B.DATE, B.Reason
FROM CONSEC1A AS A INNER JOIN CONSEC1A AS B
ON A.EmpName = B.EmpName
And A.Date = DateAdd("d",-1,B.Date)

If you have exactly two days in a row, this will give you one record with
both dates and both reasons.

If you have three days in a row, this will give you two records. If you
really need 3 records then it gets more complex. You might use a union
query to get the dates (first query gets all empname and dates with a date
after; second one gets all empname and dates with a date before). I would
be a little leary of using EmpName as a unique identifier since names are
often duplicated (even in fairly small organizations).

SELECT A.EMPNAME, A.DATE
FROM CONSEC1A AS A INNER JOIN CONSEC1A AS B
ON A.EmpName = B.EmpName
And A.Date = DateAdd("d",-1,B.Date)
UNION
SELECT A.EMPNAME, A.DATE
FROM CONSEC1A AS A INNER JOIN CONSEC1A AS B
ON A.EmpName = B.EmpName
And A.Date = DateAdd("d",1,B.Date)

If I've got that right you should end up with one record for employee plus
date that has a record before or after another date

Now you can use that in yet another query

SELECT A.EmpName, A.Date, A.Reason
FROM CONSEC1A as A INNER JOIN TheUnionQuery as U
On A.EmpName = U.EmpName and A.Date = U.Date

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

 

[Smartin]

Sorry, that was bad code on my part.

This is getting closer:

SELECT * FROM
CONSEC1A As A INNER JOIN CONSEC1A As B
ON B.EMPNAME = A.EMPNAME
WHERE
Abs(DateDiff('D', B.eDate, A.eDate)) = 1

This returns each row where another row has a consecutive eDate for the
same employee.

However, this returns a row twice if there are consecutive eDates both
before and after. This duplication could be eliminated though by
changing the first line as follows:

SELECT DISTINCT A.ID, A.empname, A.edate FROM

HTH!

When I try that, no records are returned. Is there another way to show all
the rows in the query? I'll keep playing with the code you gave me as well.
Thanks for the quick reply.

[snip]

This is based off the first one. An example of the query not showing
everything is that Employee A had Missed Punch as their reason two days in a

[quoted text clipped - 10 lines]

AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

I don't think your query will return the last row in a sequence because
on that final row, the test

(SELECT COUNT(*)
FROM CONSEC1A AS B
WHERE B.DATE >= A.DATE
AND B.DATE <= DATEADD("D",1,A.DATE)
AND B.EMPNAME = A.EMPNAME) >= 2;

will fail, as it only returns a count of 1.

I'm a bit foggy today, but you could try a strategy like

SELECT ID, EMPNAME, DATE,REASON
FROM CONSEC1A AS A
WHERE (
SELECT Abs(DateDiff('D', B.Date, A.Date))
FROM CONSEC1A AS B
WHERE B.EMPNAME = A.EMPNAME
) = 1;

Also, using "date" as a field name is not recommended as it is a
reserved word (a function, returning the current date). I suggest you
change your field names and subordinates appropriately, or at the very
least, you should enclose the field name in [square brackets] in queries
and expressions.

 

[dreamsoul620 via AccessMonster.com]

Thanks! That worked perfectly!!!

Sorry, that was bad code on my part.

This is getting closer:

SELECT * FROM
CONSEC1A As A INNER JOIN CONSEC1A As B
ON B.EMPNAME = A.EMPNAME
WHERE
Abs(DateDiff('D', B.eDate, A.eDate)) = 1

This returns each row where another row has a consecutive eDate for the
same employee.

However, this returns a row twice if there are consecutive eDates both
before and after. This duplication could be eliminated though by
changing the first line as follows:

SELECT DISTINCT A.ID, A.empname, A.edate FROM

HTH!

When I try that, no records are returned. Is there another way to show all
the rows in the query? I'll keep playing with the code you gave me as well.

[quoted text clipped - 32 lines]

least, you should enclose the field name in [square brackets] in queries
and expressions.