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

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

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

From Ulrich Möller <knobbi38@arcor.de>
Newsgroups comp.databases.ms-access
Subject Re: Row numbers in a query to keep track of absences?
Date 2015-09-04 23:59 +0200
Organization A noiseless patient Spider
Message-ID <msd44n$6oj$1@dont-email.me> (permalink)
References (3 earlier) <ms7ck3$2vb$1@dont-email.me> <c96aaf3d-7c79-467f-943e-5558e1432b78@googlegroups.com> <mscme4$jd6$1@dont-email.me> <9e6956b1-9d8d-494c-8d38-0a1167db3b2b@googlegroups.com> <msctq7$ffc$1@dont-email.me>

Show all headers | View raw

and here an example with a additional subquery in the primarily query:

SELECT
   t1.ID, t1.Teammate, t1.DateOfAbsence,
   (
     SELECT top 1 t2.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,t1.[DateOfAbsence]) AS DiffAbs,

   IIf
   (
     (
       SELECT Max(DateOfAbsence)
       FROM Attendance
       WHERE TeamMate = t1.Teammate
     ) = t1.DateOfAbsence,
     DateDiff("d",t1.DateOfAbsence,Date()),
     Null
   ) AS DiffLast

FROM Attendance AS t1;

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