Expression: Finding Difference between Records in Same Table

  • Thread starter Thread starter Alan Balthrop
  • Start date Start date
A

Alan Balthrop

I have created a table for goals scored by a soccer team. I want to create a querry to determine how much time elapsed between goals. I have
the time data stored in field counting the number of seconds (1 to 3600 to represent four-15 minute quarters).

I know the calculation is done by subtracting one time from another (3304 - 3246 = 58 seconds), but how do I write an expression asking for
that when the data I need is in the same column of the same table (the times are in different records, but the two parts of this expression
are in the same column of the table).


Thanks for any help you can provide this novice!!



Alan
 
Can you provide the relevant table structure.

Gerald Stanley MCSD
-----Original Message-----
I have created a table for goals scored by a soccer team.
Click to expand...
I want to create a querry to determine how much time
elapsed between goals. I have
the time data stored in field counting the number of
Click to expand...
seconds (1 to 3600 to represent four-15 minute quarters).
I know the calculation is done by subtracting one time
Click to expand...
from another (3304 - 3246 = 58 seconds), but how do I write
an expression asking for
that when the data I need is in the same column of the
Click to expand...
same table (the times are in different records, but the two
parts of this expression
 
Can you provide the relevant table structure.
Click to expand...


The Columns in the table are as follows

GoalID (primary key, text 6 characters)
Game# (secondary key to Games table: provides what game the goal occured in)
QTR (what period the goal happend in)
Time (secordary key to Time table. time the goal was scored in seconds: 1 is :01 of the 1st quarter, 3600 is 15:00 of the 4th Quarter)
Goal (secondary key to Names table to retrieve name of player who scored goal)
Special (secondary key to GoalType table to refer to a special situation, ie Powerplay goal, unassisted goal, Shorthanded goal, etc)
Assist (secondary key to Names table to retrieve name of player who scored goal)


Example of Data in this table

11501, 1105, 1, 538, 64, 3, 0
(GoalID 11501, scored in Game#1105 in the 1st quarter at 8:58 by Hayden, unassisted goal, no player earned the assist)

11502, 1105, 2, 1267, 153, 0, 147
(Goal ID 11502 scored in Game#1105 in the 2nd quarter at 6:07 by Tatu, no special situation, assisted by Stavrou)

11503, 1105, 2, 1651, 153, 1, 154
(GoalID 110503 scored in Game#1105 in the 2nd quarter at 12:31 by Tatu, a powerplay goal assisted by Troy)



Thanks!


Alan
 
Back
Top