Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-access > #11081
| 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-09 23:37 +0200 |
| Organization | A noiseless patient Spider |
| Message-ID | <msq8n6$eio$1@dont-email.me> (permalink) |
| References | (5 earlier) <mscme4$jd6$1@dont-email.me> <9e6956b1-9d8d-494c-8d38-0a1167db3b2b@googlegroups.com> <msctq7$ffc$1@dont-email.me> <msd44n$6oj$1@dont-email.me> <74d6db9c-0326-40d7-b0ab-421f1717aa7c@googlegroups.com> |
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
Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll 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