Extracting data from one table into a temp table

[Shawky]

Hi,
I have a access database to record horse racing data. I have a table which
holds all the race entry information for each horse per race. So Horse A, has
Jockey ID, Result, SP and other information. So this table has mutiple
entries for Horse A with every race he has been run in. What I want is to
extract his last 4 results and place them in a table so I can use it in a
race card style report so I get Horse A 1-2-2

So I have in the form table

Horse A ................. Res 1
Horse B ................. Res 4
Horse D .................. Res 3
Horse A ................... Res 2
Horse D .................... Res 3
Horse A .................... Res 2

I want to extract that data for Horse A into this format

HORSE A 1 2 2
HORSE B 4
HORSE D 3 3

Currently i use a form which lists the horse, and a 2nd form which uses the
horse.txt from form 1 as a parameter in the query to list horse A's results.
Then a for next loop to extract the last 4 results and write them into a temp
table. The process works but I could never get the loop to run automatically
and so have to press a command button to start the 4 cycle loop, thus having
to press this button 2 to 5 hundred times depending on the amount of horses
running that day!
There must be a simple way of doing this!

Hope somebody can help!

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Denormalize.mdb" which illustrates how to build such a loop.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[quintero]

Hi,
I have a access database to record horse racing data. I have a table which
holds all the race entry information for each horse per race. So Horse A,
has
Jockey ID, Result, SP and other information. So this table has mutiple
entries for Horse A with every race he has been run in. What I want is to
extract his last 4 results and place them in a table so I can use it in a
race card style report so I get Horse A 1-2-2

So I have in the form table

Horse A ................. Res 1
Horse B ................. Res 4
Horse D .................. Res 3
Horse A ................... Res 2
Horse D .................... Res 3
Horse A .................... Res 2

I want to extract that data for Horse A into this format

HORSE A 1 2 2
HORSE B 4
HORSE D 3 3

Currently i use a form which lists the horse, and a 2nd form which uses
the
horse.txt from form 1 as a parameter in the query to list horse A's
results.
Then a for next loop to extract the last 4 results and write them into a
temp
table. The process works but I could never get the loop to run
automatically
and so have to press a command button to start the 4 cycle loop, thus
having
to press this button 2 to 5 hundred times depending on the amount of
horses
running that day!
There must be a simple way of doing this!

Hope somebody can help!

 

[quintero]

Hi,
I have a access database to record horse racing data. I have a table which
holds all the race entry information for each horse per race. So Horse A,
has
Jockey ID, Result, SP and other information. So this table has mutiple
entries for Horse A with every race he has been run in. What I want is to
extract his last 4 results and place them in a table so I can use it in a
race card style report so I get Horse A 1-2-2

So I have in the form table

Horse A ................. Res 1
Horse B ................. Res 4
Horse D .................. Res 3
Horse A ................... Res 2
Horse D .................... Res 3
Horse A .................... Res 2

I want to extract that data for Horse A into this format

HORSE A 1 2 2
HORSE B 4
HORSE D 3 3

Currently i use a form which lists the horse, and a 2nd form which uses
the
horse.txt from form 1 as a parameter in the query to list horse A's
results.
Then a for next loop to extract the last 4 results and write them into a
temp
table. The process works but I could never get the loop to run
automatically
and so have to press a command button to start the 4 cycle loop, thus
having
to press this button 2 to 5 hundred times depending on the amount of
horses
running that day!
There must be a simple way of doing this!

Hope somebody can help!