Reading next record in a query

[The Mad Russian]

I have a database that keeps track of hockey games which I work at. In one
table, I have the season's schedule. I want to put together a query that
has the complete schedule, but also I want each record to show when the next
game is (for example, on game 3's record, I need to know date of game 4).

What's the best way to get a query to read the next record before the data
gets passed to a report?

Thanks for your help.

Jeff

 

[Michel Walsh]

Hi,


Bring the table twice, in a query, one will get an _1 append to the end of
its name:

ta and ta_1

On

ta.gameNumber

add the criteria

= ta_1.gameNumber - 1


so that ta_1 will refer to the next game. You may have to add additional
criteria, like ta.Team1 = ta_1.Team1, so that ta.Team2 is the opponent for
game "i", and ta_1.Team2 is the opponent for game "i+1" .


Hoping it may help,
Vanderghast, Access MVP

 

[The Mad Russian]

Thanks for your reply. Sorry I didn't back to you sooner.

I really like your idea, but unfortunately it presents another problem. Let
me give you the long version of it first...

There is always the possibility that I may have to add or remove games from
the schedule. For example, the station we broadcast on pre-empts us if the
World Series take priority. So the last game before the World Series will
have a different "next broadcast on" value than before we got pre-empted.
So I can't put a firm [GameNum] value in a table, I have to put in a query
that has a [Broadcast]=True criteria in it. Thus, if I use the [GameNum] in
a table, I can't skip over the [Broadcast]=False records in it.

For example, let's say the table has games numbered 1, 2, 3 and 4. If game
3 gets pre-empted, then the count would be 1, 2, 4, whereas your solution
depends on it being 1, 2, 3.

Now my problem is this: how do I number the records in a query? If this is
possible (and I'm sure it is to you experts...), then I could use the query
in your solution provided earlier, instead of a table.

Thanks again for your help in this.

Jeff

 

[Michel Walsh]

Hi,


You can "rank" the games:


rank: DCount("*", "tableName", ""BroadCast AND GameNumber<=" & GameNumber )

That counts the number of game with Broadcast set to True and with their
GameNumber less or equal to the "actual" on (the one of the actual record).


So, with the first two columns, we get the third:


gameNumber Broadcast rank
1 True 1
2 True 2
3 False 2
4 True 3


So, if you do that in a saved query, q1, then, in your real query, bring the
query q1 twice, instead of the original table twice, ...


Note that q1 should also "SELECT" the other fields you need from your table.



Hoping it may help,
Vanderghast, Access MVP

Thanks for your reply. Sorry I didn't back to you sooner.

I really like your idea, but unfortunately it presents another problem. Let
me give you the long version of it first...

There is always the possibility that I may have to add or remove games from
the schedule. For example, the station we broadcast on pre-empts us if the
World Series take priority. So the last game before the World Series will
have a different "next broadcast on" value than before we got pre-empted.
So I can't put a firm [GameNum] value in a table, I have to put in a query
that has a [Broadcast]=True criteria in it. Thus, if I use the [GameNum] in
a table, I can't skip over the [Broadcast]=False records in it.

For example, let's say the table has games numbered 1, 2, 3 and 4. If game
3 gets pre-empted, then the count would be 1, 2, 4, whereas your solution
depends on it being 1, 2, 3.

Now my problem is this: how do I number the records in a query? If this is
possible (and I'm sure it is to you experts...), then I could use the query
in your solution provided earlier, instead of a table.

Thanks again for your help in this.

Jeff

Hi,


Bring the table twice, in a query, one will get an _1 append to the end of
its name:

ta and ta_1

On

ta.gameNumber

add the criteria

= ta_1.gameNumber - 1


so that ta_1 will refer to the next game. You may have to add additional
criteria, like ta.Team1 = ta_1.Team1, so that ta.Team2 is the opponent for
game "i", and ta_1.Team2 is the opponent for game "i+1" .


Hoping it may help,
Vanderghast, Access MVP



In
one the
next

 

[The Mad Russian]

This is great, thanks a lot!!

One quick related question, though... is this query supposed to run so slow?
I'm not complaining, but there's only 50 records in there and it takes
better part of a minute to load.

Hi,


You can "rank" the games:


rank: DCount("*", "tableName", ""BroadCast AND GameNumber<=" & GameNumber )

That counts the number of game with Broadcast set to True and with their
GameNumber less or equal to the "actual" on (the one of the actual record).


So, with the first two columns, we get the third:


gameNumber Broadcast rank
1 True 1
2 True 2
3 False 2
4 True 3


So, if you do that in a saved query, q1, then, in your real query, bring the
query q1 twice, instead of the original table twice, ...


Note that q1 should also "SELECT" the other fields you need from your table.



Hoping it may help,
Vanderghast, Access MVP

Thanks for your reply. Sorry I didn't back to you sooner.

I really like your idea, but unfortunately it presents another problem. Let
me give you the long version of it first...

There is always the possibility that I may have to add or remove games from
the schedule. For example, the station we broadcast on pre-empts us if the
World Series take priority. So the last game before the World Series will
have a different "next broadcast on" value than before we got pre-empted.
So I can't put a firm [GameNum] value in a table, I have to put in a query
that has a [Broadcast]=True criteria in it. Thus, if I use the

[GameNum]

in
a table, I can't skip over the [Broadcast]=False records in it.

For example, let's say the table has games numbered 1, 2, 3 and 4. If game
3 gets pre-empted, then the count would be 1, 2, 4, whereas your solution
depends on it being 1, 2, 3.

Now my problem is this: how do I number the records in a query? If

this

is
possible (and I'm sure it is to you experts...), then I could use the query
in your solution provided earlier, instead of a table.

Thanks again for your help in this.

Jeff

end

of opponent
for game
4). the
data

 

[Michel Walsh]

Hi,


It can be a little bit slow, but one minute for 50 records is not
standard. Is it a table (by opposition to be a query)? Instead of "Broadcast
AND GameNumber <=" & GameNumber, try "GameNumber <=" & GameNumber & " AND
Broadcast", indexing the field GameNumber on the table. Can also change the
DCount with the equivalent SQL:

Rank: ( SELECT COUNT(*) FROM myTable As b WHERE b.GameNumber<=
a.GameNumber AND b.Broadcast)


assuming you have assigned the actual table " AS a " ( to differentiate
"b" and "a" ).

With 1000+ records, it may become interesting to append the data into a
temporary new table with an autonumber. If myTempTable has two fields, the
autonumber, call it rank, and the GameNumber, then after:


INSERT INTO myTempTable( GameNumber) SELECT GameNumber FROM myTable
WHERE broadcast ORDER BY GameNumber;


you can read the rank in the autonumber field.


Hoping it may help,
Vanderghast, Access MVP

This is great, thanks a lot!!

One quick related question, though... is this query supposed to run so slow?
I'm not complaining, but there's only 50 records in there and it takes
better part of a minute to load.

Hi,


You can "rank" the games:


rank: DCount("*", "tableName", ""BroadCast AND GameNumber<=" & GameNumber )

That counts the number of game with Broadcast set to True and with their
GameNumber less or equal to the "actual" on (the one of the actual record).


So, with the first two columns, we get the third:


gameNumber Broadcast rank
1 True 1
2 True 2
3 False 2
4 True 3


So, if you do that in a saved query, q1, then, in your real query, bring the
query q1 twice, instead of the original table twice, ...


Note that q1 should also "SELECT" the other fields you need from your table.



Hoping it may help,
Vanderghast, Access MVP

Thanks for your reply. Sorry I didn't back to you sooner.

I really like your idea, but unfortunately it presents another

problem.
Let

me give you the long version of it first...

There is always the possibility that I may have to add or remove games from
the schedule. For example, the station we broadcast on pre-empts us

if
the

World Series take priority. So the last game before the World Series will
have a different "next broadcast on" value than before we got pre-empted.
So I can't put a firm [GameNum] value in a table, I have to put in a query
that has a [Broadcast]=True criteria in it. Thus, if I use the

[GameNum]

in
a table, I can't skip over the [Broadcast]=False records in it.

For example, let's say the table has games numbered 1, 2, 3 and 4. If game
3 gets pre-empted, then the count would be 1, 2, 4, whereas your solution
depends on it being 1, 2, 3.

Now my problem is this: how do I number the records in a query? If

this

is
possible (and I'm sure it is to you experts...), then I could use the query
in your solution provided earlier, instead of a table.

Thanks again for your help in this.

Jeff



Hi,


Bring the table twice, in a query, one will get an _1 append to the

end

of
its name:

ta and ta_1

On

ta.gameNumber

add the criteria

= ta_1.gameNumber - 1


so that ta_1 will refer to the next game. You may have to add additional
criteria, like ta.Team1 = ta_1.Team1, so that ta.Team2 is the opponent
for
game "i", and ta_1.Team2 is the opponent for game "i+1" .


Hoping it may help,
Vanderghast, Access MVP



I have a database that keeps track of hockey games which I work

at.
In

one
table, I have the season's schedule. I want to put together a query
that
has the complete schedule, but also I want each record to show

when
the

next
game is (for example, on game 3's record, I need to know date of game
4).

What's the best way to get a query to read the next record before the
data
gets passed to a report?

Thanks for your help.

Jeff