Groups > comp.databases.ms-access > #11053 > unrolled thread

Row numbers in a query to keep track of absences?

Started by	sara.mallicoat@genco.com
First post	2015-09-01 13:23 -0700
Last post	2015-09-24 01:00 +0200
Articles	20 on this page of 31 — 5 participants

Back to article view | Back to comp.databases.ms-access

  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

Page 1 of 2 [1] 2 Next page →

#11053 — Row numbers in a query to keep track of absences?

From	sara.mallicoat@genco.com
Date	2015-09-01 13:23 -0700
Subject	Row numbers in a query to keep track of absences?
Message-ID	<94266d84-f680-4706-9d02-120929d0a07e@googlegroups.com>

Hello,
I have the following query (it will get more complex in the future):

ID	Teammate DateOfAbs	Code	Hrs Abs Comments
1	Ben	1/20/2015	U	10	***TEST***
2	Ben	1/25/2015	U	10	***TEST***
3	Ben	4/20/2015	U	10	***TEST***
4	Ben	4/21/2015	NON	10	***TEST***
5	Ben	8/20/2015	U	10	***TEST***
6	Chris	2/15/2015	U	10	***TEST***
7	Chris	2/16/2015	NON	10	***TEST***
8	Chris	2/28/2015	EM	10	***TEST***
9	Chris	6/5/2015	FUN	10	***TEST***
10	Chris	6/6/2015	FUN	10	***TEST***
11	Chris	6/7/2015	FUN	10	***TEST***
12	Chris	8/20/2015	EM	10	***TEST***
13	Chris	8/22/2015	U	1.5	***TEST***
14	Don	1/25/2015	U	10	***TEST***
15	Don	1/26/2015	NON	10	***TEST***
16	Don	3/20/2015	U	10	***TEST***
17	Don	5/31/2015	U	10	***TEST***
18	Don	6/10/2015	U	1.5	***TEST***
19	Don	8/20/2015	U	10	***TEST***

I want to group this by Teammate and be able to subtract days between each absence?  

I have an Access Database, so it probably needs to be an SQL code or a public function?  

I can get the rest of my query working, but for some reason I am stuck on this aspect and I need to know how many days between for absence credits.  

Also, I need to be able to use the first absence date as the start of the rolling year.  

Any ideas or suggestions would be much appreciated.

Thank you!
Sara

[toc] | [next] | [standalone]

#11054

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-02 02:39 +0200
Message-ID	<ms5gd3$nhg$1@dont-email.me>
In reply to	#11053

Am 01.09.2015 um 22:23 schrieb sara.mallicoat@genco.com:
> Hello,
> I have the following query (it will get more complex in the future):
>
> ID	Teammate DateOfAbs	Code	Hrs Abs Comments
> 1	Ben	1/20/2015	U	10	***TEST***
> 2	Ben	1/25/2015	U	10	***TEST***
> 3	Ben	4/20/2015	U	10	***TEST***
> 4	Ben	4/21/2015	NON	10	***TEST***
> 5	Ben	8/20/2015	U	10	***TEST***
> 6	Chris	2/15/2015	U	10	***TEST***
> 7	Chris	2/16/2015	NON	10	***TEST***
> 8	Chris	2/28/2015	EM	10	***TEST***
> 9	Chris	6/5/2015	FUN	10	***TEST***
> 10	Chris	6/6/2015	FUN	10	***TEST***
> 11	Chris	6/7/2015	FUN	10	***TEST***
> 12	Chris	8/20/2015	EM	10	***TEST***
> 13	Chris	8/22/2015	U	1.5	***TEST***
> 14	Don	1/25/2015	U	10	***TEST***
> 15	Don	1/26/2015	NON	10	***TEST***
> 16	Don	3/20/2015	U	10	***TEST***
> 17	Don	5/31/2015	U	10	***TEST***
> 18	Don	6/10/2015	U	1.5	***TEST***
> 19	Don	8/20/2015	U	10	***TEST***
>
> I want to group this by Teammate and be able to subtract days between each absence?
>
> I have an Access Database, so it probably needs to be an SQL code or a public function?
>
> I can get the rest of my query working, but for some reason I am stuck on this aspect and I need to know how many days between for absence credits.
>
> Also, I need to be able to use the first absence date as the start of the rolling year.
>
> Any ideas or suggestions would be much appreciated.
>
> Thank you!
> Sara

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

[toc] | [prev] | [next] | [standalone]

#11057

From	sara.mallicoat@genco.com
Date	2015-09-02 08:08 -0700
Message-ID	<a5c6a105-8b33-45aa-860f-9f617c289766@googlegroups.com>
In reply to	#11054

On Tuesday, September 1, 2015 at 7:39:50 PM UTC-5, Ulrich Möller wrote:
> Am 01.09.2015 um 22:23 schrieb sara.mallicoat@genco.com:
> > Hello,
> > I have the following query (it will get more complex in the future):
> >
> > ID	Teammate DateOfAbs	Code	Hrs Abs Comments
> > 1	Ben	1/20/2015	U	10	***TEST***
> > 2	Ben	1/25/2015	U	10	***TEST***
> > 3	Ben	4/20/2015	U	10	***TEST***
> > 4	Ben	4/21/2015	NON	10	***TEST***
> > 5	Ben	8/20/2015	U	10	***TEST***
> > 6	Chris	2/15/2015	U	10	***TEST***
> > 7	Chris	2/16/2015	NON	10	***TEST***
> > 8	Chris	2/28/2015	EM	10	***TEST***
> > 9	Chris	6/5/2015	FUN	10	***TEST***
> > 10	Chris	6/6/2015	FUN	10	***TEST***
> > 11	Chris	6/7/2015	FUN	10	***TEST***
> > 12	Chris	8/20/2015	EM	10	***TEST***
> > 13	Chris	8/22/2015	U	1.5	***TEST***
> > 14	Don	1/25/2015	U	10	***TEST***
> > 15	Don	1/26/2015	NON	10	***TEST***
> > 16	Don	3/20/2015	U	10	***TEST***
> > 17	Don	5/31/2015	U	10	***TEST***
> > 18	Don	6/10/2015	U	1.5	***TEST***
> > 19	Don	8/20/2015	U	10	***TEST***
> >
> > I want to group this by Teammate and be able to subtract days between each absence?
> >
> > I have an Access Database, so it probably needs to be an SQL code or a public function?
> >
> > I can get the rest of my query working, but for some reason I am stuck on this aspect and I need to know how many days between for absence credits.
> >
> > Also, I need to be able to use the first absence date as the start of the rolling year.
> >
> > Any ideas or suggestions would be much appreciated.
> >
> > Thank you!
> > Sara
> 
> 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

[toc] | [prev] | [next] | [standalone]

#11063

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-02 19:47 +0200
Message-ID	<ms7ck3$2vb$1@dont-email.me>
In reply to	#11057

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

[toc] | [prev] | [next] | [standalone]

#11074

From	sara.mallicoat@genco.com
Date	2015-09-04 10:27 -0700
Message-ID	<c96aaf3d-7c79-467f-943e-5558e1432b78@googlegroups.com>
In reply to	#11063

Ulrich - THANK YOU!

I took your advice and pulled directly from the table, and used your suggestions below to get the difference in dates I needed!  Your solution is so very easy - I think I was making it way more complicated!  

I am just a little confused why there is one gap for Chris?  Why?
CODE:

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

1	Ben	1/20/2015		
2	Ben	1/25/2015	20-Jan-15	5
3	Ben	4/20/2015	25-Jan-15	85
4	Ben	4/21/2015	20-Apr-15	1
5	Ben	8/20/2015	21-Apr-15	121
6	Chris	2/15/2015		
7	Chris	2/16/2015	15-Feb-15	1
8	Chris	2/28/2015	16-Feb-15	12
9	Chris	6/5/2015	28-Feb-15	97
10	Chris	6/6/2015	05-Jun-15	1
11	Chris	6/7/2015	06-Jun-15	1
12	Chris	8/20/2015		
13	Chris	8/22/2015	07-Jun-15	76
14	Don	1/25/2015		
15	Don	1/26/2015	25-Jan-15	1
16	Don	3/20/2015	26-Jan-15	53
17	Don	5/31/2015	20-Mar-15	72
18	Don	6/10/2015	31-May-15	10
19	Don	8/20/2015	10-Jun-15	71




On Wednesday, September 2, 2015 at 12:47:31 PM UTC-5, Ulrich Möller wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#11075

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-04 20:05 +0200
Message-ID	<mscme4$jd6$1@dont-email.me>
In reply to	#11074

Am 04.09.2015 um 19:27 schrieb sara.mallicoat@genco.com:

<snip>
> I am just a little confused why there is one gap for Chris?  Why?
> CODE:
>
> SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, (SELECT top 1 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],[DateOfAbsence]) AS DiffAbs
> FROM Attendance AS t1;
>
> 1	Ben	1/20/2015		
> 2	Ben	1/25/2015	20-Jan-15	5
> 3	Ben	4/20/2015	25-Jan-15	85
> 4	Ben	4/21/2015	20-Apr-15	1
> 5	Ben	8/20/2015	21-Apr-15	121
> 6	Chris	2/15/2015		
> 7	Chris	2/16/2015	15-Feb-15	1
> 8	Chris	2/28/2015	16-Feb-15	12
> 9	Chris	6/5/2015	28-Feb-15	97
> 10	Chris	6/6/2015	05-Jun-15	1
> 11	Chris	6/7/2015	06-Jun-15	1
> 12	Chris	8/20/2015		
> 13	Chris	8/22/2015	07-Jun-15	76
> 14	Don	1/25/2015		
> 15	Don	1/26/2015	25-Jan-15	1
> 16	Don	3/20/2015	26-Jan-15	53
> 17	Don	5/31/2015	20-Mar-15	72
> 18	Don	6/10/2015	31-May-15	10
> 19	Don	8/20/2015	10-Jun-15	71
>
<snip>

I just tested your query and it worked as expected. Actually there 
should not be a gap. Maybe there are something unexpected data in your 
fields, especially in the row with the id 12, because this row is not 
identified as a previous row. Based on the subselect's  where condition 
i assume the contents of the field teammate or DateOfAbsence have some 
additional not visible blank characters? You can test this with an art 
hoc query like:

SELECT Attendance.Teammate, Len([Teammate]) AS LenOfTeammate, 
Attendance.DateOfAbsence, Format$(IsDate([DateOfAbsence]),"Yes/No") AS 
IsDate
FROM Attendance;

Ulrich

[toc] | [prev] | [next] | [standalone]

#11076

From	sara.mallicoat@genco.com
Date	2015-09-04 11:17 -0700
Message-ID	<2636be1d-3869-46d6-9fa9-2dfb4f46ccc1@googlegroups.com>
In reply to	#11075

On Friday, September 4, 2015 at 1:05:41 PM UTC-5, Ulrich Möller wrote:
> Am 04.09.2015 um 19:27 schrieb sara.mallicoat@genco.com:
> 
> <snip>
> > I am just a little confused why there is one gap for Chris?  Why?
> > CODE:
> >
> > SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, (SELECT top 1 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],[DateOfAbsence]) AS DiffAbs
> > FROM Attendance AS t1;
> >
> > 1	Ben	1/20/2015		
> > 2	Ben	1/25/2015	20-Jan-15	5
> > 3	Ben	4/20/2015	25-Jan-15	85
> > 4	Ben	4/21/2015	20-Apr-15	1
> > 5	Ben	8/20/2015	21-Apr-15	121
> > 6	Chris	2/15/2015		
> > 7	Chris	2/16/2015	15-Feb-15	1
> > 8	Chris	2/28/2015	16-Feb-15	12
> > 9	Chris	6/5/2015	28-Feb-15	97
> > 10	Chris	6/6/2015	05-Jun-15	1
> > 11	Chris	6/7/2015	06-Jun-15	1
> > 12	Chris	8/20/2015		
> > 13	Chris	8/22/2015	07-Jun-15	76
> > 14	Don	1/25/2015		
> > 15	Don	1/26/2015	25-Jan-15	1
> > 16	Don	3/20/2015	26-Jan-15	53
> > 17	Don	5/31/2015	20-Mar-15	72
> > 18	Don	6/10/2015	31-May-15	10
> > 19	Don	8/20/2015	10-Jun-15	71
> >
> <snip>
> 
> I just tested your query and it worked as expected. Actually there 
> should not be a gap. Maybe there are something unexpected data in your 
> fields, especially in the row with the id 12, because this row is not 
> identified as a previous row. Based on the subselect's  where condition 
> i assume the contents of the field teammate or DateOfAbsence have some 
> additional not visible blank characters? You can test this with an art 
> hoc query like:
> 
> SELECT Attendance.Teammate, Len([Teammate]) AS LenOfTeammate, 
> Attendance.DateOfAbsence, Format$(IsDate([DateOfAbsence]),"Yes/No") AS 
> IsDate
> FROM Attendance;
> 
> Ulrich

I deleted that line and then added it again and everything worked as expected. You have no idea how excited I AM!  It is a great way to end this week!!!!!  Now, to get my running total for points working again!!!

[toc] | [prev] | [next] | [standalone]

#11077

From	sara.mallicoat@genco.com
Date	2015-09-04 11:37 -0700
Message-ID	<9e6956b1-9d8d-494c-8d38-0a1167db3b2b@googlegroups.com>
In reply to	#11075

Okay, but now I have a problem - I also need to know how many days since their last DateOfAbs?  I had a previous query that would calculate that with an Int(Now()-[dateofabs]), but is there a way to combine these into this query as well?

It would be awesome if I could, but I am not sure how at the moment?


On Friday, September 4, 2015 at 1:05:41 PM UTC-5, Ulrich Möller wrote:
> Am 04.09.2015 um 19:27 schrieb sara.mallicoat@genco.com:
> 
> <snip>
> > I am just a little confused why there is one gap for Chris?  Why?
> > CODE:
> >
> > SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, (SELECT top 1 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],[DateOfAbsence]) AS DiffAbs
> > FROM Attendance AS t1;
> >
> > 1	Ben	1/20/2015		
> > 2	Ben	1/25/2015	20-Jan-15	5
> > 3	Ben	4/20/2015	25-Jan-15	85
> > 4	Ben	4/21/2015	20-Apr-15	1
> > 5	Ben	8/20/2015	21-Apr-15	121
> > 6	Chris	2/15/2015		
> > 7	Chris	2/16/2015	15-Feb-15	1
> > 8	Chris	2/28/2015	16-Feb-15	12
> > 9	Chris	6/5/2015	28-Feb-15	97
> > 10	Chris	6/6/2015	05-Jun-15	1
> > 11	Chris	6/7/2015	06-Jun-15	1
> > 12	Chris	8/20/2015		
> > 13	Chris	8/22/2015	07-Jun-15	76
> > 14	Don	1/25/2015		
> > 15	Don	1/26/2015	25-Jan-15	1
> > 16	Don	3/20/2015	26-Jan-15	53
> > 17	Don	5/31/2015	20-Mar-15	72
> > 18	Don	6/10/2015	31-May-15	10
> > 19	Don	8/20/2015	10-Jun-15	71
> >
> <snip>
> 
> I just tested your query and it worked as expected. Actually there 
> should not be a gap. Maybe there are something unexpected data in your 
> fields, especially in the row with the id 12, because this row is not 
> identified as a previous row. Based on the subselect's  where condition 
> i assume the contents of the field teammate or DateOfAbsence have some 
> additional not visible blank characters? You can test this with an art 
> hoc query like:
> 
> SELECT Attendance.Teammate, Len([Teammate]) AS LenOfTeammate, 
> Attendance.DateOfAbsence, Format$(IsDate([DateOfAbsence]),"Yes/No") AS 
> IsDate
> FROM Attendance;
> 
> Ulrich

[toc] | [prev] | [next] | [standalone]

#11078

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-04 22:11 +0200
Message-ID	<msctq7$ffc$1@dont-email.me>
In reply to	#11077

Am 04.09.2015 um 20:37 schrieb sara.mallicoat@genco.com:
> Okay, but now I have a problem - I also need to know how many days since their last DateOfAbs?  I had a previous query that would calculate that with an Int(Now()-[dateofabs]), but is there a way to combine these into this query as well?
>
> It would be awesome if I could, but I am not sure how at the moment?
>
>
> On Friday, September 4, 2015 at 1:05:41 PM UTC-5, Ulrich Möller wrote:
>> Am 04.09.2015 um 19:27 schrieb sara.mallicoat@genco.com:
>>
>> <snip>
>>> I am just a little confused why there is one gap for Chris?  Why?
>>> CODE:
>>>
>>> SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, (SELECT top 1 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],[DateOfAbsence]) AS DiffAbs
>>> FROM Attendance AS t1;
>>>
>>> 1	Ben	1/20/2015		
>>> 2	Ben	1/25/2015	20-Jan-15	5
>>> 3	Ben	4/20/2015	25-Jan-15	85
>>> 4	Ben	4/21/2015	20-Apr-15	1
>>> 5	Ben	8/20/2015	21-Apr-15	121
>>> 6	Chris	2/15/2015		
>>> 7	Chris	2/16/2015	15-Feb-15	1
>>> 8	Chris	2/28/2015	16-Feb-15	12
>>> 9	Chris	6/5/2015	28-Feb-15	97
>>> 10	Chris	6/6/2015	05-Jun-15	1
>>> 11	Chris	6/7/2015	06-Jun-15	1
>>> 12	Chris	8/20/2015		
>>> 13	Chris	8/22/2015	07-Jun-15	76
>>> 14	Don	1/25/2015		
>>> 15	Don	1/26/2015	25-Jan-15	1
>>> 16	Don	3/20/2015	26-Jan-15	53
>>> 17	Don	5/31/2015	20-Mar-15	72
>>> 18	Don	6/10/2015	31-May-15	10
>>> 19	Don	8/20/2015	10-Jun-15	71
>>>
>> <snip>
>>
>> I just tested your query and it worked as expected. Actually there
>> should not be a gap. Maybe there are something unexpected data in your
>> fields, especially in the row with the id 12, because this row is not
>> identified as a previous row. Based on the subselect's  where condition
>> i assume the contents of the field teammate or DateOfAbsence have some
>> additional not visible blank characters? You can test this with an art
>> hoc query like:
>>
>> SELECT Attendance.Teammate, Len([Teammate]) AS LenOfTeammate,
>> Attendance.DateOfAbsence, Format$(IsDate([DateOfAbsence]),"Yes/No") AS
>> IsDate
>> FROM Attendance;
>>
>> Ulrich
Yes it should be possible. Two ideas out of my head:
Create a second subquery in the same manner, where you pic up the max 
DateOfAbsendce by the given teammate name and then calculate the the 
difference with datediff. Instead using now() i would prefer the date() 
function.

Another way is to join the base table with a separate query and then do 
some calculations:

Create a new query named LastDayOfAbsence:

SELECT Teammate, Max(DateOfAbsence) AS MaxDateOfAbsence
FROM Attendance
GROUP BY Teammate;

and extend the primarily query with a join on this 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,
     DateDiff("d",[t3].[MaxDateOfAbsence],Date()) AS DiffLast
FROM Attendance AS t1 LEFT JOIN LastDayOfAbsence AS t3
     ON (t1.DateOfAbsence = t3.MaxDateOfAbsence) AND (t1.Teammate = 
t3.Teammate);

I have not tried to combine the additional query into the first sql 
because it is more clearly and maintainable.

Ulrich

[toc] | [prev] | [next] | [standalone]

#11079

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-04 23:59 +0200
Message-ID	<msd44n$6oj$1@dont-email.me>
In reply to	#11078

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

[toc] | [prev] | [next] | [standalone]

#11080

From	sara.mallicoat@genco.com
Date	2015-09-09 12:21 -0700
Message-ID	<74d6db9c-0326-40d7-b0ab-421f1717aa7c@googlegroups.com>
In reply to	#11079

Sorry, I haven't been able to get back to my query design since Friday.  I just plugged this in and it was very slick!  Thanks so much!!!  

Okay, so if I am not a pain I have another issue I can't seem to resolve.  I want to be able to pull the last 5 dates of absences by teammate.  The top function only seems to be pull the top 5 records - I am not getting it to sort by teammate.  What am I missing?  

Thanks!
Sara

On Friday, September 4, 2015 at 4:59:37 PM UTC-5, Ulrich Möller wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#11081

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-09 23:37 +0200
Message-ID	<msq8n6$eio$1@dont-email.me>
In reply to	#11080

Am 09.09.2015 um 21:21 schrieb sara.mallicoat@genco.com:
> Sorry, I haven't been able to get back to my query design since Friday.  I just plugged this in and it was very slick!  Thanks so much!!!
>
> Okay, so if I am not a pain I have another issue I can't seem to resolve.  I want to be able to pull the last 5 dates of absences by teammate.  The top function only seems to be pull the top 5 records - I am not getting it to sort by teammate.  What am I missing?
Don' t worry. :-)
> 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
TOP limits the rows of the resultset and is therefor not usable in this 
case. What you probably want do to is something like 'TOP N per Group' 
and this can be achieved by an additional where condition. Thinking of a 
filter you have to use an IN expression where the arguments come from 
another select which will then restrict the results:

In your case extend the query with

....
FROM Attendance AS t1

WHERE ID IN (
   select top 5 ID FROM Attendance AS t2
   where t2.Teammate = t1.Teammate
   Order by t2.Teammate, t2.DateOfAbsence desc, ID
)

Ulrich

[toc] | [prev] | [next] | [standalone]

#11083

From	sara.mallicoat@genco.com
Date	2015-09-10 10:19 -0700
Message-ID	<96408438-b2b1-4188-867d-edad5805a21f@googlegroups.com>
In reply to	#11053

Ulrich - 
I ended up writing the code as below because I only wanted to see the Teammate,date, hours and reason for the last 3 absences.  It is it's own query so I can eventually tie them all together for their final report card.  
  
SELECT t1.ID, t1.Teammate, t1.DateOfAbsence, t1.HoursAbsent, t1.AbsenceCode, t1.Comments
FROM qryAttendance AS t1
WHERE (((t1.ID) In (select top 3 ID FROM Attendance AS t2 
   where t2.Teammate = t1.Teammate 
   Order by t2.Teammate, t2.DateOfAbsence desc, ID)));

Thanks for your help... I might be back for an additional question because I have some more chunks to work on.

[toc] | [prev] | [next] | [standalone]

#11094

From	sara.mallicoat@genco.com
Date	2015-09-16 09:12 -0700
Message-ID	<b5d92cf0-a465-492f-b406-5f0d67b56568@googlegroups.com>
In reply to	#11083

I can't seem to get a new post started, so I will just have to add to this one...  I am having trouble with the dsum() function?  Can anyone help me on this issue?

This is what I want my outcome to be but I am getting an error message:

Teammmate	DateOfAbsence	Points	RunningSum
John Doe	3/9/2015	1	#Error
...

SQL code:  
SELECT Teammate, DateOfAbsence, Points, DSum("[Points]","[Copy of DaysSinceLastAbs_RUNNING TOTAL]","[Teammate]=" & [Teammate] & " And [DateOfAbsence]<=#" & [DateOfAbsence] & "#") AS RunningSum
FROM [Copy of DaysSinceLastAbs_RUNNING TOTAL]
ORDER BY Teammate, DateOfAbsence;


QRY pulling from looks like this:
ID       Teammmate   DateOfAbsence   Points	(with a few other files)
auto#     John Doe	3/9/2015	1	

I have access 2013.

Thank you!
Sara

[toc] | [prev] | [next] | [standalone]

#11095

From	Patrick Finucane <patrickfinucanetx@gmail.com>
Date	2015-09-16 09:50 -0700
Message-ID	<52f5763c-da02-4cfe-a379-b564681b1b9a@googlegroups.com>
In reply to	#11094

On Wednesday, September 16, 2015 at 12:13:00 PM UTC-4, sara.ma...@genco.com wrote:
> I can't seem to get a new post started, so I will just have to add to this one...  I am having trouble with the dsum() function?  Can anyone help me on this issue?
> 
> This is what I want my outcome to be but I am getting an error message:
> 
> Teammmate	DateOfAbsence	Points	RunningSum
> John Doe	3/9/2015	1	#Error
> ...
> 
> SQL code:  
> SELECT Teammate, DateOfAbsence, Points, DSum("[Points]","[Copy of DaysSinceLastAbs_RUNNING TOTAL]","[Teammate]=" & [Teammate] & " And [DateOfAbsence]<=#" & [DateOfAbsence] & "#") AS RunningSum
> FROM [Copy of DaysSinceLastAbs_RUNNING TOTAL]
> ORDER BY Teammate, DateOfAbsence;
> 
> 
> QRY pulling from looks like this:
> ID       Teammmate   DateOfAbsence   Points	(with a few other files)
> auto#     John Doe	3/9/2015	1	
> 
> I have access 2013.
> 
> Thank you!
> Sara

Do you need quotes around "[Copy of DaysSinceLastAbs_RUNNING TOTAL]"?

Or...do you need [] inside the quotes?

From your form's code module, enter
  STOP
and in the immediate window enter
  ? DSum("[Points]","[Copy of DaysSinceLastAbs_RUNNING TOTAL]","[Teammate]="   &   [Teammate] & " And [DateOfAbsence]<=#" & [DateOfAbsence] & "#")

as it appears your RunningSum calc is invalid.

[toc] | [prev] | [next] | [standalone]

#11096

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-16 21:02 +0200
Message-ID	<mtce7e$bck$1@dont-email.me>
In reply to	#11094

Am 16.09.2015 um 18:12 schrieb sara.mallicoat@genco.com:
> I can't seem to get a new post started, so I will just have to add to this one...  I am having trouble with the dsum() function?  Can anyone help me on this issue?
>
> This is what I want my outcome to be but I am getting an error message:
>
> Teammmate	DateOfAbsence	Points	RunningSum
> John Doe	3/9/2015	1	#Error
> ....
>
> SQL code:
> SELECT Teammate, DateOfAbsence, Points, DSum("[Points]","[Copy of DaysSinceLastAbs_RUNNING TOTAL]","[Teammate]=" & [Teammate] & " And [DateOfAbsence]<=#" & [DateOfAbsence] & "#") AS RunningSum
> FROM [Copy of DaysSinceLastAbs_RUNNING TOTAL]
> ORDER BY Teammate, DateOfAbsence;
>
>
> QRY pulling from looks like this:
> ID       Teammmate   DateOfAbsence   Points	(with a few other files)
> auto#     John Doe	3/9/2015	1	
>
> I have access 2013.
>
> Thank you!
> Sara
>
Hi Sara,

can you please show us some example rows of the table  [Copy of 
DaysSinceLastAbs_RUNNING TOTAL] ?
I suppose calculation the running sum can be done without the dsum function.

Ulrich

[toc] | [prev] | [next] | [standalone]

#11097

From	"Phil" <phil@stantonfamily.co.uk>
Date	2015-09-16 20:42 +0100
Message-ID	<mtcgn8$59s$1@speranza.aioe.org>
In reply to	#11096

On 16/09/2015 20:02:15, Ulrich_Möller wrote:
> Am 16.09.2015 um 18:12 schrieb sara.mallicoat@genco.com:
>> I can't seem to get a new post started, so I will just have to add to this one...  
I am having trouble with the dsum() function? Can anyone help me on this
issue? >>
>> This is what I want my outcome to be but I am getting an error message:
>>
>> Teammmate	DateOfAbsence	Points	RunningSum
>> John Doe	3/9/2015	1	#Error
>> ....
>>
>> SQL code:
>> SELECT Teammate, DateOfAbsence, Points, DSum("[Points]","[Copy of DaysSinceLastAbs_RUNNING TOTAL]","[Teammate]=" 
& [Teammate] & " And [DateOfAbsence]<=#" & [DateOfAbsence] & "#") AS
RunningSum >> FROM [Copy of DaysSinceLastAbs_RUNNING TOTAL]
>> ORDER BY Teammate, DateOfAbsence;
>>
>>
>> QRY pulling from looks like this:
>> ID       Teammmate   DateOfAbsence   Points	(with a few other files)
>> auto#     John Doe	3/9/2015	1
>>
>> I have access 2013.
>>
>> Thank you!
>> Sara
>>
> Hi Sara,
> 
> can you please show us some example rows of the table  [Copy of
> DaysSinceLastAbs_RUNNING TOTAL] ?
> I suppose calculation the running sum can be done without the dsum
> function.
> 
> Ulrich
> 
> 

I certainly think that ALL the square brackets should be removed
If that doesn't fix the problem, experimentally remove the 
 & " And [DateOfAbsence]<=#" & [DateOfAbsence] & "#"
If that works, you then know the bit that is giving the problem

Phil

[toc] | [prev] | [next] | [standalone]

#11098

From	sara.mallicoat@genco.com
Date	2015-09-16 13:12 -0700
Message-ID	<a52eee6e-ca48-4fd7-aa32-b2293270c357@googlegroups.com>
In reply to	#11096

ID	Teammate  DateOfAbsence	 prevDateOfAbs	DiffAbs	AbsenceCodePoints AttCredits
828	John Doe	8/27/2015			U	1	0
880	James Doe	9/4/2015			U	1	0
413	Andrew Doe	5/11/2015			U	1	0
542	Drew Doe	2/1/2015			U	1	0
543	Drew Doe	2/15/2015	2/1/2015	14	U	1	0
544	Drew Doe	2/25/2015	2/15/2015	10	U	1	0
545	Drew Doe	8/1/2015	2/25/2015	179	U	0.5	1.5
662	Anthony Doe	8/1/2015			NON	0	0
790	Anthony Doe	8/13/2015	8/1/2015	12	NON	0	0
791	Anthony Doe	8/14/2015	8/13/2015	1	NON	0	0
862	Anthony Doe	8/20/2015	8/14/2015	6	U	1	0
863	Anthony Doe	8/22/2015	8/20/2015	2	U	1	0
864	Anthony Doe	8/23/2015	8/22/2015	1	NON	0	0
865	Anthony Doe	8/31/2015	8/23/2015	8	U	1	0
594	Ben Doe	1/21/2015			U	1	0
595	Ben Doe	1/22/2015	1/21/2015	1	U	1	0

[toc] | [prev] | [next] | [standalone]

#11099

From	sara.mallicoat@genco.com
Date	2015-09-16 13:18 -0700
Message-ID	<25eec9d6-47b0-4fd5-9257-bda0d993c602@googlegroups.com>
In reply to	#11098

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?  

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?

[toc] | [prev] | [next] | [standalone]

#11100

From	Ulrich Möller <knobbi38@arcor.de>
Date	2015-09-17 04:05 +0200
Message-ID	<mtd712$tbb$1@dont-email.me>
In reply to	#11099

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

[toc] | [prev] | [next] | [standalone]

Page 1 of 2 [1] 2 Next page →

csiph-web

Row numbers in a query to keep track of absences?

Contents

#11053 — Row numbers in a query to keep track of absences?

#11054

#11057

#11063

#11074

#11075

#11076

#11077

#11078

#11079

#11080

#11081

#11083

#11094

#11095

#11096

#11097

#11098

#11099

#11100