VBA looping and recording of output (1 Viewer)

xxxpipxxx

New member
Local time
Today, 14:49
Joined
Sep 29, 2015
Messages
2
Hi Folks, I am new on this forum and very much at early stage of learning VBA.

I have this problem to solve, and would just appreciate any ideas on a starting point.

I have a table which ( amongst others) has the variables

NameOfHorse(String);RaceDateTime(Date/Time);FinishPosition(Int)

The horse and RaceDateTime of race (together with track not shown) creates a unique combination for each record.

My aim is to populate another field with a record of the FinishPosition for the last "n" races. In the form, for example, "143861" ( as a String).

In this example, I guess I would needto gather an array taken from the FinishPosition Variable from the previous six races for that horse and transpose it into a text field with the most recent Finish Postion ( ie the previous race) being at the right of the string.

So it would read for example:

Shergar 07/06/2015 2 143861 and be interpreted as "In this race ran on 017/06/2015 Shergar came 2nd, and in the previous 6 races Shergar (most recent first) came 1st, 6th, 8th, 3rd, 4th and 1st. (ie recent form)

(I will actually use HorseId rather than HorseName in query, but this is just to illustrate)

The problem I am envisaging is how to use dateTime field to collect the data in the correct order

Any pointers would be much appreciated, and I will come back with my final code to share..

Cheers

Phil
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:49
Joined
Sep 12, 2006
Messages
15,614
personally, I would store this as an extra field in the horse record, and update it after each race with the latest position.

so you have 121, and then the horse comes third, so make it 1213.

it won't ever change, as long as you enter the races in data order.

I am more inclined to think that timeform say, do that, rather than evaluate the history on the fly. You can include year spacers as well.

12/01-11

other than that, you would have to iterate a recordset of the previous races to obtain the history. There is another thread discussing a function called concatrelated() on this same topic.

It's easy to get a query of all the races a given horse has run, sorted in data order.

just one point. If you are just storing a zero for "down the field", then you are ok, otherwise you can't distinguish between 11 as two wins, and 11 as eleventh.

this thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=281215
 
Last edited:

xxxpipxxx

New member
Local time
Today, 14:49
Joined
Sep 29, 2015
Messages
2
Wow That was a quick reply!!

That makes sense, They would be permanent records - so the key thing here is to have the table in date order, so the loop moves forward and collects data in correct order?

I obviously need to do this for every horse seperately, so this will need to be specified in code too.

I will look up the concatanate thread you mentioned now.

many thanks. Will let you know how I get on:)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:49
Joined
Sep 12, 2006
Messages
15,614
one reason why its worth storing this, is that evaluating a history for every horse time and time again is likely to be a relatively slow process. if you only do it a race at a time, it may not matter, but for every horse in training it might be prolonged.

I would probably have a query that selects all the horses, and a separate query that that selects the race history for one horse

then you can use this sort of logic to examine the horses in an outer loop, and pick up the results for each in an inner loop. The ConcatRelated function deals with the inner loop.

Code:
 open horses query record set

        open single horse detailed query
       while not all races checked
             accumulate the results
             next detailed record
       wend
       close the detailed query
       
       store or use the accumulated result
       next horse record
 wend
 close the horses query
 

Users who are viewing this thread

Top Bottom