two questions about tables

[johny2x4]

Hello all. I've recently been relearning Access in the last few days and have found this site and other resources very helpful. At the moment, I have two issues related to table properties which should be easy but I'm having trouble figuring them out. I will try to give as much detail about what I'm attempting to do.

For my data, let's say I have something like this with three fields for a motorsports race(I simplified it)

Name Start Finish
John 4 5
Bob 1 3
Tom 2 6
Sarah 6 4
Alice 3 1
Chris 5 2

The name is the name of the driver. Start is their starting position on the grid. Finish is their finishing position in the race.

Now I created a new numeric field, called Top 3.

Top 3: This will be a 1 value for the drivers who finished in the top 3 and a 0 value for the drivers who finished outside the top 3 (ie. 4th, 5th and 6th).


I could just make the default value 0 for this field and then enter a 1 for the drivers who finished in the Top 3 manually in Datasheet View but I want this to be automatic, so that when I enter a Finish position of 1, 2 or 3 for a driver in a race, a 1 will appear on the Top 3 field automatically for those three drivers.

I'm not sure how to set the criteria so this would work.

I tried to make an iif statement in the Validation Rule field but it didn't work.

=IIf([Finish] < 4,1,0).

It returns with an invalid syntax error. Do you have any suggestions on what I need to do? I feel like I'm missing something obvious, can you even do an if then statement in table properties?


--------------
second question:

In this racing season, I have a total of 20 races with the same 6 drivers. I will have a separate table for each race, so there will be a total of 20 tables. After all of this is entered, I decide to make a query in which I create an expression that sums the finishing position of all drivers and then divides it by 20. This could be called the Average Finish.

So each driver will end up with a value, such as Chris might have an average finish of 5.5.


Anyway my issue is, is that the expression would be really long because I am going through each race and summing the Finish position for a total of 20 times before dividing it by 20.

Would there be an easier way to create this expression? Something that would tell Access to simply add the Finish field in all 20 races and divide by 20 at once? (Does this make sense?)

Thanks for any suggestions and let me know if you need additional info.

 

[plog]

1st question: This data should not be stored in a table, but calculated in a query. Create a query based on your table and use your formula there.

2nd question: You should have only 1 table for all of those races, not 20 tables. Simply add a new column called 'RaceNum' to designate which race the data is for. Then to get your totals its a matter of making a simple aggregate query.

 

[johny2x4]

Thanks, that was helpful. Regarding your second answer, I think I will do one race per table because there are several more fields which I didn't show in the example and it's just easier for me to have it like this.

I had one more question.

Here is a simple query I created to calculate the Average Start for each driver.

AvgStart: ([Race1]![Start]+[Race2]![Start]+[Race3]![Start])/3

This works fine but the issue is that one of the drivers only completed the first 2 of 3 races. So for that driver, there is no value in the Start field, for the last race.

Then when I run the query, there is a null value for that driver. Is there a way I can modify the syntax such that it will calculate that driver's Average Start from just the two races and ignore the missing Start value for the race he wasn't in?

 

[boblarson]

Thanks, that was helpful. Regarding your second answer, I think I will do one race per table because there are several more fields which I didn't show in the example and it's just easier for me to have it like this.

It may seem easier up front, but it will cause you no end of grief when you want to get data out. Do it the way plog said. And, that being said, you will want to make sure your data is normalized so you may not be having all the fields you envision in the same table.


And, as for Averages, again it will be easy to get the values if your table structure is properly normalized.

 

[plog]

This works fine but the issue is that one of the drivers only completed the first 2 of 3 races. So for that driver, there is no value in the Start field, for the last race.

I agree 100% with bob's statement where he agreed with me. 1 race per table is the absolutely wrong way to go because you want to look at all race data at an aggregate level.

 

[johny2x4]

Thanks. I had another question now.

Here is an example of my data.

(see attached 'table' picture)

Looks pretty simple.
But I'm having trouble with the query calculations now.

(see attached 'query' picture)

I'm trying to simply list the total points that each driver has. I want it to just list the three drivers and their total points, not how many points they had in each race separately. Where am I messing up at?

I also have no primary key, could this be the issue?

 

[plog]

I want it to just list the three drivers and their total points,I want it to just list the three drivers and their total points,

Then why do you have all that other data? If you just want those 2 fields, use just those 2 fields in your query.

 

[johny2x4]

Wow I didn't realize that was the problem. Everything is fine now, thanks again.

 

[plog]

Every field you bring into an aggregate query and GROUP BY affects the results, even if they aren't set to show up in the results.