I have a query that outputs "student name" and  "entry#"   Entry# are in 
ascending order.  Some students may only have 1 entry# or multiple entry#'s 
depending on how many routines they are in.

What I need to be able to do is calculate the difference between studen'ts 
entry#'s and see if they have at least 5 entries between them.

Is this possible?

Not sure that I'm understanding this correctly - to me, "difference" implies
a mathematical calculation (subtraction).  But if what you're wanting to get
is a list of student names for student with at least 5 entry# records, use a
totals query:

SELECT [Student Name], Count([Entry#]) AS [CountOfEntry#]
FROM YourTableName
GROUP BY [Student Name]
HAVING (Count([Entry#]) >= 5;

To do this in the query design grid, add the "Student Name" and "entry#"
fields, then click the totals symbol (Greek sigma, like a W on its side).
In the Total row which then appears in the grid, select Group By for Student
Name and Count for entry#; put >= 5 in the criteria row for entry#.

BTW, including spaces and/or symbols such as # in your field names will
force you to enclose those names in square brackets when you refer to them;
it is neither standard practice nor good practice.

HTH,

Rob


"S" <S@discussions.microsoft.com> wrote in message 
news:50AEB1C9-66DB-47E2-8519-45B865C54D27@microsoft.com...
>I have a query that outputs "student name" and  "entry#"   Entry# are in
> ascending order.  Some students may only have 1 entry# or multiple 
> entry#'s
> depending on how many routines they are in.
>
> What I need to be able to do is calculate the difference between studen'ts
> entry#'s and see if they have at least 5 entries between them.
>
> Is this possible? 

Let me give an example to better show what I am lookinf for...

StudentName    Entry#
John Doe            001
John Doe            015
John Doe            019
John Doe            045

So I am looking for a query to calculate how many entries are inbetween each 
entry for each student

So John Doe is entry# 001, then 015  (so theres 14 entries inbetween) then 
from entry 015 to 019 (theres 4 entries) then from 019 to 045 (26 entries)

I only need to know when there is less than 5 entries.

SO the query result I am looking for is:

John Doe         015       019        less than 5 entries

"Rob Parker" wrote:

> Not sure that I'm understanding this correctly - to me, "difference" implies
> a mathematical calculation (subtraction).  But if what you're wanting to get
> is a list of student names for student with at least 5 entry# records, use a
> totals query:
> 
> SELECT [Student Name], Count([Entry#]) AS [CountOfEntry#]
> FROM YourTableName
> GROUP BY [Student Name]
> HAVING (Count([Entry#]) >= 5;
> 
> To do this in the query design grid, add the "Student Name" and "entry#"
> fields, then click the totals symbol (Greek sigma, like a W on its side).
> In the Total row which then appears in the grid, select Group By for Student
> Name and Count for entry#; put >= 5 in the criteria row for entry#.
> 
> BTW, including spaces and/or symbols such as # in your field names will
> force you to enclose those names in square brackets when you refer to them;
> it is neither standard practice nor good practice.
> 
> HTH,
> 
> Rob
> 
> 
> "S" <S@discussions.microsoft.com> wrote in message 
> news:50AEB1C9-66DB-47E2-8519-45B865C54D27@microsoft.com...
> >I have a query that outputs "student name" and  "entry#"   Entry# are in
> > ascending order.  Some students may only have 1 entry# or multiple 
> > entry#'s
> > depending on how many routines they are in.
> >
> > What I need to be able to do is calculate the difference between studen'ts
> > entry#'s and see if they have at least 5 entries between them.
> >
> > Is this possible? 
> 
> .
> 

Your question is not clear.

It might help to post a few sample records and the desired outcome.  Also, 
post the actual field names and data types and your table name.

I think you want to determine the difference between successive entry numbers 
per student.  Generically, you might be able to use a query that looks like 
the following.  It uses a correlated sub-query to get the prior entry number 
and then does the math.  Of course if entry number is not a number field then 
this will fail to give you the correct results or it will error.

SELECT [StudentName], [EntryNumber]
, [EntryNumber] -  (SELECT Max([EntryNumber])
                  FROM [TABLE] as TEMP
                  WHERE TEMP.[EntryNumber] < ]Table].[EntryNumber]
                  AND TEMP.[StudentName] = [Table].[StudentName])
   as TheDifference
FROM [TABLE]

You need to replace the table and field names with your table and field names.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

S wrote:
> I have a query that outputs "student name" and  "entry#"   Entry# are in 
> ascending order.  Some students may only have 1 entry# or multiple entry#'s 
> depending on how many routines they are in.
> 
> What I need to be able to do is calculate the difference between studen'ts 
> entry#'s and see if they have at least 5 entries between them.
> 
> Is this possible?

My Table is "Dancers For Each Routine"
Fields Are:
ID (AutoNumber)
Dancer ID (Number)
Entry ID (Number)

Sample records are
Dancer ID      Entry ID
5                  17
5                  45
5                  52
5                  54
7                  10
7                  73
7                  80

You are correct. I am looking for the difference between successive entry 
numbers 
per student.

So the difference for Dancer ID 5 records would be
Dancer ID   Entry ID    TheDiffernce
5                  17
5                  45          28     
5                  52          7
5                  56          4
7                  10
7                  73           63
7                  80           7


I would only need the following outcome
Dancer ID  5   has less than 5 numbers from enrty id 52 and entry id 56.

I treid what you gave my and I'm not getting those results.  A lot of 
positive and negative numbers.




"John Spencer" wrote:

> Your question is not clear.
> 
> It might help to post a few sample records and the desired outcome.  Also, 
> post the actual field names and data types and your table name.
> 
> I think you want to determine the difference between successive entry numbers 
> per student.  Generically, you might be able to use a query that looks like 
> the following.  It uses a correlated sub-query to get the prior entry number 
> and then does the math.  Of course if entry number is not a number field then 
> this will fail to give you the correct results or it will error.
> 
> SELECT [StudentName], [EntryNumber]
> , [EntryNumber] -  (SELECT Max([EntryNumber])
>                   FROM [TABLE] as TEMP
>                   WHERE TEMP.[EntryNumber] < ]Table].[EntryNumber]
>                   AND TEMP.[StudentName] = [Table].[StudentName])
>    as TheDifference
> FROM [TABLE]
> 
> You need to replace the table and field names with your table and field names.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> S wrote:
> > I have a query that outputs "student name" and  "entry#"   Entry# are in 
> > ascending order.  Some students may only have 1 entry# or multiple entry#'s 
> > depending on how many routines they are in.
> > 
> > What I need to be able to do is calculate the difference between studen'ts 
> > entry#'s and see if they have at least 5 entries between them.
> > 
> > Is this possible?
> .
> 

I mistyped something.  Got it working great!
Thank you

"S" wrote:

> My Table is "Dancers For Each Routine"
> Fields Are:
> ID (AutoNumber)
> Dancer ID (Number)
> Entry ID (Number)
> 
> Sample records are
> Dancer ID      Entry ID
> 5                  17
> 5                  45
> 5                  52
> 5                  54
> 7                  10
> 7                  73
> 7                  80
> 
> You are correct. I am looking for the difference between successive entry 
> numbers 
> per student.
> 
> So the difference for Dancer ID 5 records would be
> Dancer ID   Entry ID    TheDiffernce
> 5                  17
> 5                  45          28     
> 5                  52          7
> 5                  56          4
> 7                  10
> 7                  73           63
> 7                  80           7
> 
> 
> I would only need the following outcome
> Dancer ID  5   has less than 5 numbers from enrty id 52 and entry id 56.
> 
> I treid what you gave my and I'm not getting those results.  A lot of 
> positive and negative numbers.
> 
> 
> 
> 
> "John Spencer" wrote:
> 
> > Your question is not clear.
> > 
> > It might help to post a few sample records and the desired outcome.  Also, 
> > post the actual field names and data types and your table name.
> > 
> > I think you want to determine the difference between successive entry numbers 
> > per student.  Generically, you might be able to use a query that looks like 
> > the following.  It uses a correlated sub-query to get the prior entry number 
> > and then does the math.  Of course if entry number is not a number field then 
> > this will fail to give you the correct results or it will error.
> > 
> > SELECT [StudentName], [EntryNumber]
> > , [EntryNumber] -  (SELECT Max([EntryNumber])
> >                   FROM [TABLE] as TEMP
> >                   WHERE TEMP.[EntryNumber] < ]Table].[EntryNumber]
> >                   AND TEMP.[StudentName] = [Table].[StudentName])
> >    as TheDifference
> > FROM [TABLE]
> > 
> > You need to replace the table and field names with your table and field names.
> > 
> > John Spencer
> > Access MVP 2002-2005, 2007-2010
> > The Hilltop Institute
> > University of Maryland Baltimore County
> > 
> > S wrote:
> > > I have a query that outputs "student name" and  "entry#"   Entry# are in 
> > > ascending order.  Some students may only have 1 entry# or multiple entry#'s 
> > > depending on how many routines they are in.
> > > 
> > > What I need to be able to do is calculate the difference between studen'ts 
> > > entry#'s and see if they have at least 5 entries between them.
> > > 
> > > Is this possible?
> > .
> >