Remember Variables After Database Exit

[Lightwave]

Some may have seen previous posts but I have been developing a timing database and I'm getting on well with it.

I've set up a series of variables that will be required to alter queries when looking at competitor times.

For instance a running race where they are times from one point the start to the finish eg a marathon. Lets take as an example the London Marathon and lets say it started at 10am.

The background is that the competitors could be standing next to the RFID mat (a giant receiver that picks up race numbers as competitors cross it) prior to the start and trigger a reading on their timing chip!

Not a massive problem as we get multiple reads and we just do a query to delete all those prior to 10am and then take their first read after 10am and ignore the other reads until x hours later when they come back across the finish.

My current thinking is
I've got a timing database and a Time Settings Table in which for the race the 10am start time is entered into the and stored.

I store the details of this race in a Time settings table and at the moment I set up the the variable value when I open the form.

It should be noted that at present there will be a new copy of the database everytime we do a new race so the time settings table only ever contains one record. In the form I don't allow additions or deletions. (I know I should really be having multiple races and having a new record of the specific time settings for each race. At the moment that's a bit beyond me and I am trying to work on getting the database to work on a single race before I tackle a db that can handle multiple races)

I was thinking that when I wanted to do a calcultation of times from the chips I could
......on the sly open up the time settings form but not make it visible
....... set the required variable in the on load event from the fields placed on that form
....... run the query with reference to the stored table variable (eg start time of 10am)

My question is this.
Can anyone tell me the proper method of doing this???
I assume that when a database is closed all variables are forgotten and hence my definition of variables in a table is broadly correct.

Thanks

M


Ideally it would be great to open the database up and all the variables would be dragged from that table.

Any pointers?

I've never used recordsets before but I am about to for a set of queries.

Thanks in advance

Mark

 

[CameronM]

Just a few thoughts. If you record different start times for all competitors - as I gather from reading your post - could you not end up with the situation that someone who actually crossed the start line late could have a better time than the winner? For example, what if I wait for the gun to go off at 10am, have a cup of coffee, and wait until the 'crowd' of runners leave. I then run across the start line at 10:30am, when there are no other runners to get in my way, meaning I can get an awesome time.

Setting the start time at 10am would not be very good if the start of the race is delayed either - everyone standing on the RFID mat at 10 am would have longer times recorded, as the system would think that they had begun the race.

I would suggest you ignore all RFID input at the start of the race and only record when the competitor actually crosses the line. You can then have a big button labelled "START RACE", which would record the exact time the starters gun fired. Each runners race time would then be the difference between the actual start time and the time they crossed the finish line.

Technically there are a couple of ways to store values. The first is to use public variables, which can be declared in a Module that is run when your application starts or at some other useful time. These can the be referenced anywhere in your application. In the database attached I have stored the start time in the public variable as well as save the value into a table using a simple form.

Create a new module called basRaceDetails then add this code;

'declare varible for use in application
Public myStartTime As Date
Public Sub SetStartTime()
    'create a sub that is called to set the value of the variable
    myStartTime = Format(Now(), "Long Time")
End Sub

Call the sub SetStartTime from a button click:

Private Sub cmdStartRace_Click()
    'call the sub that sets the start time
    SetStartTime
    
    'display a message
    MsgBox "Race started at " & myStartTime
    
    'store the value in a field on the form
    Me.txtStartTime = myStartTime
End Sub

 

[Atomic Shrimp]

I would suggest you ignore all RFID input at the start of the race and only record when the competitor actually crosses the line. You can then have a big button labelled "START RACE", which would record the exact time the starters gun fired. Each runners race time would then be the difference between the actual start time and the time they crossed the finish line.

I don't know the details for sure, but I suspect that's not going to be possible - you want to record the actual time it took each runner to travel from start to finish - for a large race, it might logistically take half an hour to get everyone across the start line - and to keep the timings fair, I think you have to start the timing for any individual runner at the moment they cross the line, not when the gun goes off - that is, as I understand it, part of the reason for using RFID.

 

[Lightwave]

Many thanks Cameron/Mike you both have points ...

Mike that's what we do for the majority of our run races but the database will be doing races which are not first past the post and we will probably offer this facility to race organisers at some point in the future. In reality run races are pretty much only organised on a first past the post format and while technically someone behind the leaders could run the race quicker its the first across the line that wins.

It is nice though to have the ability to record speed over the course because although it's awkward for spectators , media and sponsors its good for competitors further back in the field were people of equal ability haven't had a chance to be equalised in terms of ability, start time and location.

Also the database is also going to be used in in Triathlons , the amateur side of which is defined as a timed race where people aren't supposed to draft and the fastest person over the course wins the race. They will frequently be split into waves of competitors with odd and unusual start times.

As a result your both ultimately correct in that placing across the finish is the ultimate decider and timing may also be an important factor. For the front of the field the "Start" is both spatial and temporal. It Starts at 10am at the start line, you can start later from a different location (only further away) but you can't start earlier from a beneficial location. To outright win you will always have to get to the finish line first and if you do happen to wait for everyone to leave to win you will need to overtake absolutely everyone and get to the finish first. Further down the field though were individuals start at odd times and its the time over the "stage" which is important. It would be good for some of the better club runners who maybe want to start with their partners further down the field to know their time rather than a placing.

I'll have a look at your system a bit later but in the meantime thanks

 

[gemma-the-husky]

I was thinking that when I wanted to do a calcultation of times from the chips I could
......on the sly open up the time settings form but not make it visible
....... set the required variable in the on load event from the fields placed on that form
....... run the query with reference to the stored table variable (eg start time of 10am)

i tend to use global variables

so for something like this i would do a

in a code module
public starttime as date

function readstarttime as date
readstarttime = nz(starttime,0)
end function

then in my forms module
starttime = dlookup("starttime","tblevents","event = " & anyparticularevent)
starttime = dlookup("starttime","tblevents")
{note if you only have one event per dbs, then the second line will work}

now in any query you can just put readstarttime in a column, and it will return the starttime which was set by the appropriate form

 

[Lightwave]

Thanks Gemma exactly what I was looking for.

 

[CameronM]

Many thanks Cameron/Mike you both have points ...


Also the database is also going to be used in in Triathlons , the amateur side of which is defined as a timed race where people aren't supposed to draft and the fastest person over the course wins the race. They will frequently be split into waves of competitors with odd and unusual start times.

You may need to rethink storing one global variable 'starttime' in the stiuation where you need to provide waves/staggered starts. I suspect you would need to store both the wave name, such as 'Professional Male' and the actual start time in a table.

My suggestion would be to use a sub form listing all the waves for the race, with the 'Start Wave' button on each row of the sub form. A competitor would be linked to the wave, and then you can easily record the split times for each leg of the race and the finish time as they pass over the RFID mat.

Just for kicks, I mocked up a sample db. Hope it helps with some of the logic.

 

[Lightwave]

Yep C that's exactly what I'm doing.

Each competitor is allocated a chip tag at registration and we will record what wave they are to go off in with that.

Each wave will have it's start time recorded and if it's going to be a rolling start there will effectively be no wave or wave will be set to 0.

Then we gate the times coming in to knock out duplicates (for multiple reads over the mat) also have a pre cut off (case they stray over the mat where there is a start matt) and a post race cut off for 24hour/timed races. There will be a facility to force last seen reading of multiple hits (this was a request from the timing lad and I can't see when we would need to implement it)
and I will get the race timer to store the variable of the finish lap. With regard to Gunned races the stored variable is required because some races will be odd number of laps with mid timing mats so even if you know its a point to point might not necessarily know which is the gun.

I was thinking some kind of pivot table originally with the time of day but recalculated to give the time period but a txtfield recorded next to the competitor which gets it value from comparing the lapnumber time to the variableFinishLap.

Help much appreciated

 

[Lightwave]

Gemma / Colin,

One question - I'm using Gemmas (thankyou) code to lookup the variable from the table.

Do I need to make a new function for each variable I need to look up in the table or is there a syntax for multiple look ups in one function. I'm thinking this might be where arrays come in?

 

[gemma-the-husky]

if you use dlookups its one variable per lookup

if you know you need to lookup lots of variables, you can open the table in a recordset, and get them all at one time.

we discussed this in a thread previously, and there may not actually be much more efficiency with the recordset version, but users requiring multiple reads tend to do it that way.

 

[Lightwave]

Right the penny is starting to drop why you would actually use a recordset.

I've got to get this working for a race on Sunday so think I'll leave the recordset thing for just now and come back to it.. There's not that many variables that I need to define.

Much appreciated and with all the help I'm starting to feel like I'm understanding things..

 

[RossWindows]

Hi guys, just read through the situation and although I don't quite follow (I was reading pretty fast), I do have one concern.

If you are using public or global variables, what happens if you lose power to the equipment? I can forsee a high possibility of this, where many feet of extension cable may be in use, in addition to a lot of equipment on the same line which might trip a breaker.

If you want to avoid loss of critical data then I would:
1) store all variables (temporary as they may be) in a table or tables. That way, if you have an epic fail you can reboot and start where you left off. OR
2) lug around a good UPS.
3) Use a laptop; but if you are going to distribute this to other people, they might not have a laptop, so you always want to plan for the worst-case scenario.

 

[Lightwave]

Ross all varaibles being stored in a table
We have UPS and battery plus back up from local grid
We have palms as well.

We've had all of what you've said before.

And of course human error - where I switch off the Timing box and wonder why the computer has lost connection to the IP.. Oops

 

[Lightwave]

While the detailed design and construction of the database is being carried out now I've had a year of using another system which is a good starting point for knowing the general flow of information

 

[RossWindows]

Good to hear you're doing well with it.
I'm working on project that's similar in the fact that it seems simple enough at first but when you get down to brass tacks, it becomes difficult to wrap your head around the complexity of things. You just have to tackle one aspect at a time.

Good luck,
Ross

 

[CameronM]

24hour/timed races

Ouch, I hope that's not 24 hours of constant exercise, that just sounds far too much like hard work

 

[CameronM]

Another thing to remember is that you don't have to do all the processing and calculations when the data is being received by the RFID mat. If you simply record all the data in your table you can create queries to select the last recorded input for a given competitor. So basically you could create a log of all RFID inputs, then just use queries to display and calculate lap/final times.

The query below would return the time of the tag read just prior to the current read for a given competitor (using their tage id)

SELECT Max(a.ReadTime) AS MaxOfReadTime
FROM tblTagReads a
WHERE a.ReadID<[PutTheCurrentReadIDHere] AND a.TagID=[PutTheTagIDHere]
GROUP BY a.TagID

 

[Lightwave]

That is probably one of the biggest decisions.

As it stands the flow is as follows

1.Tag comes in and placed in a table long hex nonparced code
2.Append Query converts raw code from that Table into non-hex readable parced.
3.Delete Query on Table performed filtering duplicates removed from append.
Further Select Query's performed using variables that can be altered by Timer
(eg change of gating, change Pre and Post cut of periods)
4.Then make a Pre-Lap Table - Make table was required because the subsequent Lap Number query didn't like being based on a query for some reason.
5.Then make a Lap Number Table which produces a column that consecutively counts in time order each of the recorded mat hits where ID is the same.
6.Can't remember why I had to make it a Make Table but it was required.
7. Cross Tab that takes all the times and matches them to Competitors (separate table)

There's a lot of subtleties in there that I probably haven't explained very clearly. One in particular the ID tags are handed out as Numbers but are read as Hextags. (Competitors don't want to know that they are actually not number 22 but actually A147bf4 so there's a look up table between the competitors and the times so that anyone operating the registration hands out chip number 22 and the computer will know to match competitor 22 to the hits on A14bf4.

As it stands the problem area is probably the delete query on the append query. I made an append query because there are sometimes competitors that need extra times put in (missed lap times eg if their chiptag falls off). And I couldn't let them add to the first table because that's overwritten constantly. By doing an append query if new times are added to the target of the append they won't be overwritten anytime new tags are received. Unfortunately an append writes a complete set of new records to the target table. The delete query removes the duplicates.

Looking back on it might be better to do an update query rather than an append as there will be less duplication and might be less resource hungry. Missed competitor times could then be added to the target of the update presumably without fear of deletion. Alternatively you introduce a 3rd source table (addition to the Competitorstbl and Timestbl)(ignore look up table for now). This source would be missed times table and could even use an algorithm to convert normal times back into madrawtext to combine with raw prior to it being used to update.

It will work this weekend but I might go back to that. The Append Query followed by the Delete Query is the bottleneck at moment. Will just have to be cautious about how frequently I update from the raw time information as it comes through the system.

Anyway that's the challenge of a real time data entry

 

[Lightwave]

Just to say thanks to all on here for helping me out with the database.

I hooked it up to a timing box outside of Teviot Union in Edinburgh today and it helped time 700 runners in a charity race organised by students of Edinburgh University.

Turns out that the timing box have an internal gating so gating might not be necessary - despite this it performed as well or better than the professional bespoke database (although not live) and that's before I've had a decent go at ironing out its rough edges.

Big thanks.
(Teviot Union is one of the Student Clubs of Edinbugh University and is said to be the oldest purpose built student union buildings in the world.