Difference between numbers

[S]

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?

 

[Rob Parker]

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]

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

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

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?

.

 

[John Spencer]

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

as TEMP
WHERE TEMP.[EntryNumber] < ]Table].[EntryNumber]
AND TEMP.[StudentName] =

.[StudentName])
as TheDifference
FROM

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]

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.

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

as TEMP
WHERE TEMP.[EntryNumber] < ]Table].[EntryNumber]
AND TEMP.[StudentName] =

.[StudentName])
as TheDifference
FROM

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

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?

.

 

[S]

I mistyped something. Got it working great!
Thank you

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.

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

as TEMP
WHERE TEMP.[EntryNumber] < ]Table].[EntryNumber]
AND TEMP.[StudentName] =

.[StudentName])
as TheDifference
FROM

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

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?

.