If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Difference between numbers
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? |
#2
|
|||
|
|||
Difference between numbers
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" wrote in message ... 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? |
#3
|
|||
|
|||
Difference between numbers
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" wrote in message ... 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? . |
#4
|
|||
|
|||
Difference between 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 [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? |
#5
|
|||
|
|||
Difference between numbers
My Table is "Dancers For Each Routine"
Fields A 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? . |
#6
|
|||
|
|||
Difference between numbers
I mistyped something. Got it working great!
Thank you "S" wrote: My Table is "Dancers For Each Routine" Fields A 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? . |
Thread Tools | |
Display Modes | |
|
|