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

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-24 01:00 +0200
Organization	A noiseless patient Spider
Message-ID	<mtvaq2$gs1$1@dont-email.me> (permalink)
References	(7 earlier) <595ece3a-9cf3-437b-a371-d3389c7457f3@googlegroups.com> <mtgkv3$glo$1@dont-email.me> <4cb7309f-9c69-4018-876c-58487f3df3d7@googlegroups.com> <mthiee$59e$1@dont-email.me> <c67ef2ac-90a0-42f8-9954-7f6d23108925@googlegroups.com>

Show all headers | View raw

Am 22.09.2015 um 20:43 schrieb Sara M:
> Ulrich - I was going to send you my file I couldn't upload it where you sent the link.
>
> I got the point total to sum which I thought I could adjust the code and get the credits to sum up and then simply join the two and get what I need, but for whatever reason, my credits will not sum by teammates - it sums the total for all entries...  what am I doing wrong... I have tried a million different ways...
>
> Can I email you the file or is this easiest?
>
> Code:
> SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, t1.AttendanceCredits, t1.TotPts, (SELECT SUM([TotPts])
>      FROM qryRUNTot AS t2
>      WHERE t1.Teammate = t2.Teammate AND t1.DateOfAbsence >= t2.DateOfAbsence
>    ) AS RSum
> FROM qryRUNTot AS t1;
>
> Output from this:
> ID   Teammate DateOfAbsence Attendance
>                               Credits	TotPts	RSum
> 1	Ben	1/20/2015	0	1	9.5
> 2	Ben	1/25/2015	0	1	9.5
> 3	Ben	4/20/2015	0.5	0.5	9.5
> 4	Ben	4/21/2015	0	0	9.5
> 21	Brandon	2/1/2015	0	1	9.5
> 22	Brandon	2/13/2015	0	0.5	9.5
> 23	Brandon	3/5/2015	0	0.5	9.5
> 24	Brandon	3/13/2015	0	1	9.5
> 25	Brandon	3/14/2015	0	0	9.5
> 26	Brandon	5/24/2015	0.5	0	9.5
> 6	Chris	2/15/2015	0	1	9.5
> 7	Chris	2/16/2015	0	0	9.5
> 8	Chris	2/28/2015	0	0	9.5
> 13	Chris	8/22/2015	1.5	0	9.5
> 14	Don	1/25/2015	0	1	9.5
> 15	Don	1/26/2015	0	0	9.5
> 16	Don	3/20/2015	0.5	0.5	9.5
> 17	Don	5/31/2015	0.5	0.5	9.5
> 18	Don	6/10/2015	0	0.5	9.5
> 19	Don	8/20/2015	0.5	0.5	9.5
>
> Query I was pulling data from:
> ID	Teammate	DateOfAbsence	prevDateOfAbs	DiffAbs	AbsenceCode	Points	AttendanceCredits	TotPts
> 1	Ben	1/20/2015			U	1	0	1
> 2	Ben	1/25/2015	20-Jan-15	5	U	1	0	1
> 3	Ben	4/20/2015	25-Jan-15	85	U	1	0.5	0.5
> 4	Ben	4/21/2015	20-Apr-15	1	NON	0	0	0
> 21	Brandon	2/1/2015			U	1	0	1
> 22	Brandon	2/13/2015	01-Feb-15	12	U	0.5	0	0.5
> 23	Brandon	3/5/2015	13-Feb-15	20	U	0.5	0	0.5
> 24	Brandon	3/13/2015	05-Mar-15	8	U	1	0	1
> 25	Brandon	3/14/2015	13-Mar-15	1	NON	0	0	0
> 26	Brandon	5/24/2015	14-Mar-15	71	EM	0	0.5	0
> 6	Chris	2/15/2015			U	1	0	1
> 7	Chris	2/16/2015	15-Feb-15	1	NON	0	0	0
> 8	Chris	2/28/2015	16-Feb-15	12	EM	0	0	0
> 13	Chris	8/22/2015	28-Feb-15	175	U	0.5	1.5	0
> 14	Don	1/25/2015			U	1	0	1
> 15	Don	1/26/2015	25-Jan-15	1	NON	0	0	0
> 16	Don	3/20/2015	26-Jan-15	53	U	1	0.5	0.5
> 17	Don	5/31/2015	20-Mar-15	72	U	1	0.5	0.5
> 18	Don	6/10/2015	31-May-15	10	U	0.5	0	0.5
> 19	Don	8/20/2015	10-Jun-15	71	U	1	0.5	0.5
Hi Sara,

if have analyzed your queries and the error is a wrong data type in your 
iif expressions. These expressions e.x.

IIf([DiffAbs] Between 45 And 89,*"0.5"*, ...

returns string values instead of numerical values as expected by the 
dependent calculation and aggregations.
The correct expression would be

IIf([DiffAbs] Between 45 And 89,*0.5*, ...

without the double quotes.

Ulrich

Back to comp.databases.ms-access | Previous | Next — Previous 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