Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11075

Re: Row numbers in a query to keep track of absences?

Show all headers | View raw

Am 04.09.2015 um 19:27 schrieb sara.mallicoat@genco.com:

<snip>
> I am just a little confused why there is one gap for Chris?  Why?
> CODE:
>
> SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, (SELECT top 1 DateOfAbsence
> FROM [Attendance] as t2
> WHERE t1.Teammate=t2.Teammate and t2.DateofAbsence < t1.DateOfAbsence
> ORDER BY DateOfAbsence desc, ID) AS prevDateOfAbs, DateDiff("d",[prevDateOfAbs],[DateOfAbsence]) AS DiffAbs
> FROM Attendance AS t1;
>
> 1	Ben	1/20/2015		
> 2	Ben	1/25/2015	20-Jan-15	5
> 3	Ben	4/20/2015	25-Jan-15	85
> 4	Ben	4/21/2015	20-Apr-15	1
> 5	Ben	8/20/2015	21-Apr-15	121
> 6	Chris	2/15/2015		
> 7	Chris	2/16/2015	15-Feb-15	1
> 8	Chris	2/28/2015	16-Feb-15	12
> 9	Chris	6/5/2015	28-Feb-15	97
> 10	Chris	6/6/2015	05-Jun-15	1
> 11	Chris	6/7/2015	06-Jun-15	1
> 12	Chris	8/20/2015		
> 13	Chris	8/22/2015	07-Jun-15	76
> 14	Don	1/25/2015		
> 15	Don	1/26/2015	25-Jan-15	1
> 16	Don	3/20/2015	26-Jan-15	53
> 17	Don	5/31/2015	20-Mar-15	72
> 18	Don	6/10/2015	31-May-15	10
> 19	Don	8/20/2015	10-Jun-15	71
>
<snip>

I just tested your query and it worked as expected. Actually there 
should not be a gap. Maybe there are something unexpected data in your 
fields, especially in the row with the id 12, because this row is not 
identified as a previous row. Based on the subselect's  where condition 
i assume the contents of the field teammate or DateOfAbsence have some 
additional not visible blank characters? You can test this with an art 
hoc query like:

SELECT Attendance.Teammate, Len([Teammate]) AS LenOfTeammate, 
Attendance.DateOfAbsence, Format$(IsDate([DateOfAbsence]),"Yes/No") AS 
IsDate
FROM Attendance;

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-01 13:23 -0700
  Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-02 02:39 +0200
    Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-02 08:08 -0700
      Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-02 19:47 +0200
        Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-04 10:27 -0700
          Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-04 20:05 +0200
            Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-04 11:17 -0700
            Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-04 11:37 -0700
              Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-04 22:11 +0200
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-04 23:59 +0200
                Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-09 12:21 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-09 23:37 +0200
  Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-10 10:19 -0700
    Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-16 09:12 -0700
      Re: Row numbers in a query to keep track of absences? Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-09-16 09:50 -0700
      Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-16 21:02 +0200
        Re: Row numbers in a query to keep track of absences? "Phil" <phil@stantonfamily.co.uk> - 2015-09-16 20:42 +0100
        Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-16 13:12 -0700
          Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-16 13:18 -0700
            Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-17 04:05 +0200
              Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-17 13:49 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-18 11:21 +0200
                Re: Row numbers in a query to keep track of absences? sara.mallicoat@genco.com - 2015-09-18 08:48 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-18 19:44 +0200
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-18 13:43 -0700
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-22 11:43 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-23 00:39 +0200
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-23 06:11 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-23 18:23 +0200
                Re: Row numbers in a query to keep track of absences? Sara M <sara.mallicoat@genco.com> - 2015-09-23 09:29 -0700
                Re: Row numbers in a query to keep track of absences? Ulrich Möller <knobbi38@arcor.de> - 2015-09-24 01:00 +0200

csiph-web