I'm new to Access programming and have reached total meltdown. I've searched Google and here and either I don't know enough to know I've seen the solution, or I haven't found it yet. If there's a posted answer, just point me in the right direction!
Here's the problem: This is a fishing tournament where points are assigned based on weight of fish caught (basically, first place = 100 points, 2nd place = 99, descending sequentially until TOTALWEIGHT=0, then no points). I have a form based on a query named RESULTS that dumps the data into a table. The query is sorted by TOTALWEIGHT once the sort button is activated. I want to automatically assign points to a blank field called POINTS in the query based on values in TOTALWEIGHT.
I have stumped more than one person with this question and would appreciate any help anyone can offer!
Then is it better to query on a query to track my results from tournament to tournament? these points are used over the season to determine overall place. I had been entering them, then using a sum query for the YTD results. (I'm sorry about being so obviously clueless, but I really want to learn the best way)
Originally posted by cpod It's usually not a good idea to put calculated values into a table.
The results are tracked per tournament, 6 tournaments a year at 6 different lakes. The table stores all the data for tournament results. I query the results table for a particular lake along with the member table and have set my form on that query. That gives me all the people participating in that tournament. Their points are assigned on a per tournament basis and then tracked for the whole year. For example, in the first tournament, Team 101 may receive 100 points for winning, then in the next tournament he may receive 97 points for a fourth place finish, giving him a cumulative total of 197 for the year so far. I got the formula to work when I ran a second query off the original with TeamID, WEIGHT and the calculation. That gave me the proper points for that tournament, but it did not calculate the ties the way I need to and the recordset is not updatable and does not populate the form. Does that make any sense at all?
You can create a temporary table with one field containing the team names and another blank field called "Points" (you can create this easily by copying the member table and just deleting call the other fields).
Then create an update query with this temp table and the query you have on your results table with the calculation. Update the temp table's points field with the calculation. Then use the temp table to update the results table.