Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2002
    Posts
    5

    Unhappy Unanswered: Newbie stumped ... any help.. Please!

    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!
    Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    29
    If your table is named "fishing" and the field is "totalweight" then this expression will number the weights descending sequentially starting with the highest weight equaling 100.

    Points: IIf([totalweight]=0,0,(Select Count (*)+ (100-(Select Count (*) FROM [fishing]) ) FROM [fishing] as Temp WHERE [Temp].[totalweight] < [fishing].[totalweight])+1)

  3. #3
    Join Date
    Jun 2002
    Posts
    5

    Thanks!! But.....

    It worked like a charm in the query and the data posts to the form, but it's not updating the field on the table?

  4. #4
    Join Date
    Feb 2002
    Posts
    29
    It's usually not a good idea to put calculated values into a table.

  5. #5
    Join Date
    Jun 2002
    Posts
    5

    Advice??

    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.

  6. #6
    Join Date
    Feb 2002
    Posts
    29
    I'm not quite sure I understand what you are trying to do. Is this done on a yearly basis?

  7. #7
    Join Date
    Jun 2002
    Posts
    5

    Sorry....

    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?

  8. #8
    Join Date
    Feb 2002
    Posts
    29
    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.

  9. #9
    Join Date
    Jun 2002
    Posts
    5

    alrighty...

    Thanks again... I'll give that a whirl.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •