Update query

[12345]

I am currently in the process of setting up a database for a swimming club.

One table in the database will hold the Personal Best times of each swimmer over different distances -

PB(SwimmerID, Distance, Time)

These personal best times will be entered for each swimmer whenever the database is setup.

The database will also be used to store the results from the competitions which the swimmers compete in. Two tables will be used for this -

Event(Event ID, Event Name, Distance, Date)
Results(Event ID, SwimmerID, Position, Time)

These two tables are linked by Event ID.

Now for the problem that I have –

I want the database to automatically update the personal best table if the swimmer swims a faster time in one of the races – the results of which are entered into the database. E.g If someones personal best for 100m freestyle is 52 seconds and they then swim 51 seconds I want the database to use an update query to update the PB table.

I have tried to write an update query to solve this but I have not had any success. If you could help me with this I would be most grateful.

Thanks

 

[PatrickJohnson]

One way you can do it is to create a separate query for the personal best times. make a query off the results table which only includes the swimmer ID, distance, and the time fields. then go to view->totals. underneath the time field, set the totals field to "Min". under the rest, make sure they say "group by." This will return each Swimmer ID and the corresponding minimum time in each event the have participated in.

Then make an update query which includes the pb table linked to the query we just made via the distance field. pull in the personal best field from the pb table and set it to update to the minof time field from the query we made.

then you can either click that update query when you want the times updated, or set it to automatically run when exiting a data entry form.

what we have essentially done here is determined the minimum time for each different swimmer and event, then asked an update query to place this in the personal best field whenever it is run.

others, please chime in if i've missed any crucial details.

 

[12345]

Then make an update query which includes the pb table linked to the query we just made via the distance field. pull in the personal best field from the pb table and set it to update to the minof time field from the query we made.

Okay thanks for the help. This is probably a really basic question but, I have got the select query to work but how do I get the update query to take the value from the select query and put it in the PB table?
Thanks

 

[12345]

Oh I have just thought of something esle.

I assume that the update query would need some sort of IF statement as the time in the PB table will only be updated if the min time in the results is less than the time already in the PB table. The swimmers PB's will be entered into the PB table when the database is setup and then they will only be updated whenever they swim faster in future events and the results of these events are input into the results section.
Hope that made sense.

 

[PatrickJohnson]

make it just like a select query, but then go up to query->update query. then you should see a new row under your fields in the query called "update to." that is where you put in the destination table and field you want to send this data to. in your case it would say something like [pb]![time]. incidentally, i forgot to mention that in that update query you should be joining distance fields and swimmer id fields. this basically tells the query "update only the records where the distance and the swimmer id match. make sense?

 

[PatrickJohnson]

actually, you need no if statement, because you can let it update no matter what, it just will update with the same data. we set a sort of conditional statement when we made the first query which limited our results to best times only. then no matter what, the lowest time is updated, even if it is the same as their previous best time.

don't worry, unless you are dealing with thousands of records, the added process time will only be fractions of a second.

however, if it does bother you, type this criteria under the Time field in the update query:

>[query_we_just_made]![time]

this will update only if the MinOf Time field from our query we made before is less than the previous best time. Make sense? I'm horrible at explaining by typing...

 

[12345]

Thanks for all the help. It seems to be getting me somewhere.

The only problem that I have is that when I try to run that update query it comes up with the message - 'Operation must use and updateable query'.
Do you have any idea why it would be doing this?

 

[PatrickJohnson]

sounds like you have the criteria to update to a query instead of a table. the "update to" needs to be set to the time field in the pb table. check that.

 

[12345]

Okay I have created a really cut down version of the database with only the necessary parts in it. If you could have a look at it for me and tell me what I am doing wrong it would be a great help.
Thanks

 

[PatrickJohnson]

i looked, but there are a couple of things that you need to do before i can even delve into it. first, the times need to be entered in the same unit (milliseconds, seconds, etc.) otherwise, access cannot compare them without extra code. additionally, once you settle on a unit to enter, it has to be in number format, not date and time.

second, you can't assign the swimmer id as the primary key of the table or you won't be able to assign multiple distances to each swimmer. a primary key must be unique to one record only, so you can't put in, say, swimmer 1 had x time for 100 meters and x time for 50 meters. you follow?

also, you don't have a field for distance in the results table. how are you going to compare times for each distance without having the distance there, silly bean?

tell you what, when i get home this evening i will whip up a sample table that does everything we've talked about so you can see what it looks like.

 

[12345]

follow?

also, you don't have a field for distance in the results table. how are you going to compare times for each distance without having the distance there, silly bean?

I had the distance in the event table which was then linked to the results table by EventID. anyway..............

Thanks for all the help. If you can create a sample database showing me how this is done it would be so much help.
Thanks

Edit: In the PB table I should also have made the fields Swimmer ID and Distance a compound key.

 

[12345]

Patrick
Did you have any luck with getting it to work?

Thank You

 

[PatrickJohnson]

working on it right now. more results soon.

 

[PatrickJohnson]

okay, here is the version i did. pick through it, let me know if you have questions.

 

[12345]

Did that work for you? When I tried to run the update query it just came up with the message 'Operation must use an updateable query'.

Thanks for the help

 

[PatrickJohnson]

check this one. i changed the update query to a make-table query that basically finds everyone's best time and remakes the table with the new info. this one worked for me.

 

[PatrickJohnson]

also, i found the reason update wasn't working. if you cahnge the joins i made to #1 joins instead of #2's it should work.

 

[12345]

Thanks for the help. I seem to be getting somewhere now.

The only problem that I have with that one is that if in the PB table you manually enter a time for say 200m, but you do not have the result of this race recorded in the results table then this time for 200 will be removed when you run the make table query.
Although it seems like all the PB's will have to be recorded in the results table in order to get this system to work!?
(Hope i'm making sense!)

 

[12345]

I tried changing it from #2 joins to #1 joins but it still returned the same error message to me. Thanks for trying anyway.