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

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

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

Show all headers | View raw

Am 16.09.2015 um 22:18 schrieb sara.mallicoat@genco.com:
> I need the running total of points such that I can then accurately remove the credits and come up with the right point totals prior to adding more points?
Well, here is a sample how you can calculate a "RunningSum":

SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, t1.prevDateOfAbs, 
t1.DiffAbs, t1.AbsenseCode, t1.Points, t1.AttCredits,
(
Select Sum([Points])
FROM CopyOfDays as t2
WHERE
t1.Teammate = t2.Teammate
and
t1.DateOfAbsence >= t2.DateOfAbsence
) as RunningSum
FROM CopyOfDays AS t1

Same principle as i posted before, instead if using datediff you use the 
sum function and restrict the result on each row to the same teammate 
and a date less or equal the current date of the row. So you can access 
the predecessor (n-1) of the current row.

> Basically trying to get to
> Points - credits (if neg then points = 0)+ new points - new credits (kind of an (pts + (n+1))-(creds)+(pts n +1 + current points) - (current creds).
>
> Maybe this just confused you?
Yes, i am confused. Maybe the calculations should be better wrapped in a 
clean datamodel otherwise it would be nearly unmaintainable and in 
addition it will also slow down your application. I wonder, how you will 
design a UI to present this data?

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