Re: Two or more consecutive days
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Fri, 19 Jan 2007 15:25:55 -0500
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
..
"dreamsoul620 via AccessMonster.com" <u18823@uwe> wrote in message
news:6c78d974c3995@xxxxxx
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;
--
Message posted via http://www.accessmonster.com
.
- References:
- Two or more consecutive days
- From: dreamsoul620 via AccessMonster.com
- Two or more consecutive days
- Prev by Date: RE: Update Query???
- Next by Date: RE: How to retrieve all or patial records
- Previous by thread: Re: Two or more consecutive days
- Next by thread: text to number format or preceeding 0
- Index(es):
Relevant Pages
|
