Difference between numbers
in [Queries]
|
Prev: Add percentages
Next: Joining Multiple Tables
From: S on 28 Feb 2010 21:58 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?
From: Rob Parker on 28 Feb 2010 22:54 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(a)discussions.microsoft.com> wrote in message news:50AEB1C9-66DB-47E2-8519-45B865C54D27(a)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?
From: S on 1 Mar 2010 08:48 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(a)discussions.microsoft.com> wrote in message > news:50AEB1C9-66DB-47E2-8519-45B865C54D27(a)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? > > . >
From: John Spencer on 1 Mar 2010 09:13 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?
From: S on 1 Mar 2010 14:07
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? > . > |