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

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-18 19:44 +0200
Organization	A noiseless patient Spider
Message-ID	<mthiee$59e$1@dont-email.me> (permalink)
References	(5 earlier) <25eec9d6-47b0-4fd5-9257-bda0d993c602@googlegroups.com> <mtd712$tbb$1@dont-email.me> <595ece3a-9cf3-437b-a371-d3389c7457f3@googlegroups.com> <mtgkv3$glo$1@dont-email.me> <4cb7309f-9c69-4018-876c-58487f3df3d7@googlegroups.com>

Show all headers | View raw

Am 18.09.2015 um 17:48 schrieb sara.mallicoat@genco.com:
> I put this back in my test database because I know I am not ready to roll it out again yet.
>
> In doing so and playing around and adding chunks I got the RunSum to work, but it is not looking at teammates at all.
>
> CODE:
> SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, t1.prevDateOfAbs, t1.DiffAbs, t1.Points, t1.AttendanceCredits, (SELECT SUM([Points]) FROM [DaysSinceLastAbs_RUNNING TOTAL] AS t2 WHERE t1.Teammate = t2.Teammate AND t1.DateOfAbsence >= t2.DateOfAbsence) AS RunSum
> FROM [DaysSinceLastAbs_RUNNING TOTAL] AS t1;
>
> Output:
> IDTeammateDateOfAbsence	prevDateOfAbsDiffAbsPointsAttendanceCredits RunSum
> 1	Ben	1/20/2015			1	0	12
> 2	Ben	1/25/2015	20-Jan-15	5	1	0	12
> 3	Ben	4/20/2015	25-Jan-15	85	1	0.5	12
> 4	Ben	4/21/2015	20-Apr-15	1	0	0	12
> 21	Brandon	2/1/2015			1	0	12
> 22	Brandon	2/13/2015	01-Feb-15	12	0.5	0	12
> 23	Brandon	3/5/2015	13-Feb-15	20	0.5	0	12
> 24	Brandon	3/13/2015	05-Mar-15	8	1	0	12
> 25	Brandon	3/14/2015	13-Mar-15	1	0	0	12
> 26	Brandon	5/24/2015	14-Mar-15	71	0	0.5	12
> 6	Chris	2/15/2015			1	0	12
> 7	Chris	2/16/2015	15-Feb-15	1	0	0	12
> 8	Chris	2/28/2015	16-Feb-15	12	0	0	12
> 20	Chris	8/20/2015	28-Feb-15	173	0	1.5	12
> 13	Chris	8/22/2015	20-Aug-15	2	0.5	0	12
> 14	Don	1/25/2015			1	0	12
> 15	Don	1/26/2015	25-Jan-15	1	0	0	12
> 16	Don	3/20/2015	26-Jan-15	53	1	0.5	12
> 17	Don	5/31/2015	20-Mar-15	72	1	0.5	12
> 18	Don	6/10/2015	31-May-15	10	0.5	0	12
> 19	Don	8/20/2015	10-Jun-15	71	1	0.5	12
I have just tested your sql and it works as expected. Check your input 
table for invalid types or extra characters (e.x. Blanks), particular on 
the fields teammate and DateOfAbsence.

Here you can find my testdata: http://1drv.ms/1W8ClJK

Ulrich

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