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

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

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

Show all headers | View raw

Am 02.09.2015 um 17:08 schrieb sara.mallicoat@genco.com:
> Hi Sara,
>
> this snippet calculates the difference between the DateOfAbs and the
> previous DateOfAbs if any exists:
>
> SELECT t1.ID, t1.Teammate, t1.DateOfAbs, (SELECT top 1 DateOfAbs
> from tblTeam as t2
> where t1.Teammate=t2.Teammate and t2.dateofabs < t1.dateofabs
> order by DateOfAbs desc
> ) AS prevDateOfAbs, DateDiff("d",[prevDateOfAbs],[DateOfAbs]) AS Diff
> FROM tblTeam AS t1;
>
> Hope this will give you a little hint to solve a detail problem. You can
> save this sql as a separate query and use it in a join with the original
> table or extend it to your needs for some further grouping and summing
> on each teammate.
>
> Ulrich
> Ulrich, thanks for the reply!
>
> I tried this, I think I am doing something wrong?  My data was coming from a query - qry Attendance.  My actual names were as shown below for each field.  I got a little confused by the tblTeam and t2?
>
> SELECT [qry Attendance].ID, [qry Attendance].Teammate, [qry Attendance].[Date of Absence], (SELECT TOP 1 Date of Absence FROM tblTeam as t2 where [qry Attendance].Teammate=t2.Teammate and t2.dateofabs <[qry Attendance].[Date of Absence]order by Date of Absence desc) AS prevDateOfAbs,DateDiff("d",[prevDateofAbs].[Date of Absence]) AS Diff FROM tblTeam AS [qry Attendance];
>
> Where did I go wrong if you wouldn't mind?
>
> Thanks!
> Sara
t1 and t2 are aliases for the same sample database table 'tblTeam'. So 
replace all occurrence of tblteam with your query name and leave the 
aliases as there are:

SELECT t1.ID, t1.Teammate, t1.DateOfAbs, (SELECT top 1 DateOfAbs
from [qry Attendance] as t2
where t1.Teammate=t2.Teammate and t2.dateofabs < t1.dateofabs
order by DateOfAbs desc, ID
) AS prevDateOfAbs, DateDiff("d",[prevDateOfAbs],[DateOfAbs]) AS DiffAbs
FROM [qry Attendance] AS t1;

Btw.: it would be a good idea to avoid any spaces and special characters 
in query names, just use 'qryAttendance' for instance and live will be 
easier.

Perhaps it is possible to combine the qryAttenance with the code above 
into one optimized query?

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