Access Report on consecutive numbers in a field

Wb999
NewLounger
Posts: 16
Joined: 19 Sep 2013, 22:00

Access Report on consecutive numbers in a field

Post by Wb999 »

I have a table:
PupilID
Subject
TestDate
TestTime
TestSlot - this is a number field

I need to report pupils who have a test in 3 consecutive slots, eg
Pupil ID Subject TestSlot
A English 1
Science 2
Art 3

B French 11
Spanish 12
Maths 13

A crosstab query give me all pupils and their TestSlots but with over 8000 it is eye-popping to identify the relevant students.
Is it possible to query on just those pupils with 3 consecutive test slots?
Many thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access Report on consecutive numbers in a field

Post by HansV »

1) Does each pupil have 3 test slots?
2) If not, can there be more than 3?
3) And if there can there be more than 3, what would you like to report if a pupil has - for example - 4 consecutive time slots 1,2,3,4? Would you like to see 1,2,3,4 or two separate series of 3 consecutive time slots 1,2,3 and 2,3,4?
Best wishes,
Hans

Wb999
NewLounger
Posts: 16
Joined: 19 Sep 2013, 22:00

Re: Access Report on consecutive numbers in a field

Post by Wb999 »

1) - no
2) yes, some have fewer than 3 and some can have up to 5
3) 1 series of 1,2,3,4

Many thanks
Wendy

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access Report on consecutive numbers in a field

Post by HansV »

Thanks - I'll work on it, but it looks complicated at first sight.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Access Report on consecutive numbers in a field

Post by HansV »

I have attached a small sample database. I named the table tblTests and I used two queries.
The first selects the PupilID and TestSlot of those records for which the next two TestSlot values for the same PupilID are also present:

SELECT PupilID, TestSlot
FROM tblTests
WHERE (SELECT Count(*) FROM tblTests AS T WHERE T.PupilID=tblTests.PupilID AND T.TestSlot Between tblTests.TestSlot And tblTests.TestSlot+2)=3

I named this query qrySeq. The second query is based on tblTests and tblSeq, and it selects the records from tblTests where TestSlot is between the value of TestSlot in the query and that value + 2. To avoid duplicates, the Unique Values property of this query has been set to Yes. It is named qrySeries.

SELECT DISTINCT tblTests.PupilID, tblTests.Subject, tblTests.TestSlot
FROM tblTests INNER JOIN qrySeq ON tblTests.PupilID = qrySeq.PupilID
WHERE tblTests.TestSlot Between [qrySeq].[TestSlot] And [qrySeq].[TestSlot]+2
ORDER BY tblTests.PupilID, tblTests.TestSlot
Consecutive.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Wb999
NewLounger
Posts: 16
Joined: 19 Sep 2013, 22:00

Re: Access Report on consecutive numbers in a field

Post by Wb999 »

:clapping: :thankyou: :clapping:
perfect