Prev: Add percentages
Next: Joining Multiple Tables
From: S on 1 Mar 2010 15:49
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?
> > .
> >
First  |  Prev  | 
Pages: 1 2
Prev: Add percentages
Next: Joining Multiple Tables