Two or more consecutive days

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

.

Relevant Pages

  • Re: Comparing the Results from two queries
    ... you must substitute the actual names of your queries for Q1 ... I have assumed that employeeID in query 2 would never be NULL. ... > 2) - Training completed by Employee ... > multiple training modules, even the same modules across multiple years, ...
    (microsoft.public.access.queries)
  • Re: Two or more consecutive days
    ... but the query only returns the first of the two dates. ... employee, they have 2 days with Missed Punch and 2 more days with No Call ... FROM CONSEC1A AS A INNER JOIN CONSEC1A AS B ... but only part of my results are showing. ...
    (microsoft.public.access.queries)
  • Re: Stupid Query Question...Column Display
    ... I could give a better answer if you can post examples of a couple of records from each of the Tables used in your Queries, plus the SQL of any Queries you have defined, plus an illustration of what you'd like your Query to generate from your example records. ... I was however getting the results that I need, but I'm still getting all of the empty "table column headings" with blank cells? ... It's a simple query that contains the [Employee Name],, and about 15 other columns of named job's. ...
    (microsoft.public.access.queries)
  • Re: Query to find most recent date
    ... To combine them all into one query, you will need to have four queries. ... really only need to return two fields, the employee number and the Date. ... the badge number table, I'm just tracking the date and the badge ...
    (microsoft.public.access.queries)
  • RE: Sub report message
    ... performs the first join and then include that query in your SQL statement." ... Left join employee table to issue table. ... 1.I checked the main report query to see if there was a problem. ... All site staff are listed including all of the Food Service ...
    (microsoft.public.access.reports)