Counting number of runs for each runner using update query

[simbamfalme]

Hi guys. Im pretty new to using access and am creating a database to store records of the runners and the runs they take part in. I have five tables, three of which are important for this question i have:

Runner
-RunnerID (Primary Key)
-RunnerName
-NoOfRuns
-DOB
-BestTime

Runs
-RunID (primary key)
-LocationID......;this is from a location table
-RunDate

and a final link table which has no primary key but 2 foreign keys

Results
-RunID
-RunnerID
-RunnerTime

I need a way to update Runner.NoofRuns i.e count the number of runs each runner takes part in. I tried using the DCOunt function but it didnt work

then i tried using queries and came up with a select query :


SELECT Runner.RunnerID, Count(Results.RunnerID) AS CountOfRunnerID
FROM Run INNER JOIN (Runner INNER JOIN Results ON Runner.RunnerID = Results.RunnerID) ON Run.RunID = Results.RunID
GROUP BY Runner.RunnerID;


and then tried to use an update query to update NoOfRuns to CountOfRunnerID from the above query, but got this error:

Operation must use an updateable query


on looking this error up, i found a solution was saing the records of the first select query to a temporary table, and then running the update query from to update NoOfruns from the temp. table. i used the following queries:

The make table query:


SELECT Runner.RunnerID, Count(Results.RunnerID) AS CountOfRunnerID INTO tempruncount
FROM Run INNER JOIN (Runner INNER JOIN Results ON Runner.RunnerID = Results.RunnerID) ON Run.RunID = Results.RunID
GROUP BY Runner.RunnerID;


and the update query:


UPDATE (Runner INNER JOIN tempruncount ON Runner.RunnerID = tempruncount.RunnerID) INNER JOIN Results ON Runner.RunnerID = Results.RunnerID SET Runner.NoOfRuns = [tempruncount]![CountOfRunnerID];


And this seems to work.

However, the process of adding a table and updating from there is too long and i dont like the idea of an ectra table. is there another possible way of working around this, maybe using the DCount function, or counting the records from the update query itself, or solving the "Operation must use an updateable query" problem.

Please help me as this is very important and very urgent.

 

[plog]

You shouldn't store redundant data. Most often this means you shouldn't store data you can calculate from other data. In your Runner table you have two of these fields: BestTime and NoOfRuns. You should get rid of these fields and use an aggregate query whenever you need that information.

To get this data you should set up a query using this SQL:

SELECT RunnerID, Count(RunID) AS NoOfRuns, Min(RunnerTime) AS BestTime
FROM Results
GROUP BY RunnerID;

Now whenever you need that data, you run that query and you have it instead of a convoluted UPDATE query process that you have to run everytime you enter new data.

 

[simbamfalme]

Ok wow..the query you gave me was very simple and seemed to do the work. However, i have a problem, in that i need the noofruns and the best results of each runner to be saved somewhere on the database...in such a scenario, what do you suggest i do. btw thanks for the immediate help.

 

[simbamfalme]

one more question...why is it very bad to have such fields in your table..is it unreliable? or does it make the database very slow?

 

[simbamfalme]

Also, assuming that i have to store the data in the table...there are a few conditions i needed.

The database has a run form, where details of every form are seen, and the users can add the runners and their times (i.e the results) through a results subform. i need the noofruns field to be updated only if the runner ran that particular run, and the besttime to be updated only if the runners time in this specific run < current best time. would that be possible? please help me out guys

 

[plog]

However, i have a problem, in that i need the noofruns and the best results of each runner to be saved somewhere on the database

No you don't, you need to use the query I provided to obtain that data whenever you need it. You can bring in that query to other queries, on forms and reports you can use the DLookup function (http://www.techonthenet.com/access/functions/domain/dlookup.php) to retrieve and display it whenever you need.

If you wanted your house green you wouldn't first paint it blue then yellow, you'd mix the paint to the proper color, test it on the backside of your house then start painting. Databases are the same way--you do the proper set up and then there's less work in the future.

What advantage do you think storing the data gives you that my method doesn't?

 

[simbamfalme]

i fully agree that the data shouldnt be stored. however, i am working on an already created database...one that was created by the previous designer, and it has the 2 fields noofruns and besttime in the runner table. also, there is a set of data in .txt format i have been given (previous older data) that has to be added to the system, and this data has the 2 fields, hence i have to store the values. i know your system is much better but i dont have a choice.

 

[plog]

So this data doesn't have corresponding data in Results? That is, you know that Steve ran 18 times, but you can't look in the Results table and find those 18 runs?

 

[simbamfalme]

i dont exactly understand what ur asking. umm from what i understood this is my response:
There will be 18 results of steve in the results table

 

[simbamfalme]

there will be 18 results, but i need to count those and place them in the runner table

 

[plog]

It might have been mentioned before in this post a time or 7, but no, no you do not need to place them in the runner table if it can be deduced from the Results table.

I officially give up.

 

[simbamfalme]

never mind...i know its really weird but anyway thanks for the help. the query you gave me really helped me alot. thanks