I haven't been here for awhile since I haven't had time, lately, to "play" much with Access. However, I recently started working on a new database that is geared toward a new club. The club will eventually be holding competitions. I'm hoping to already have a solution in place for us to use for those competitions. I'm planning on donating it to the organization (a non-profit) for our (their) use.
I have a good bit of the basic work done and I'm working on one of the reports now. However, I'm having problems with this one.
Here's what the report currently contains:
Event Name and Date: (e.g., ABCD Extravaganza)
Class #
1
Placing / Competitor# / Name
1 / 225 / John Doe
2 / 340 / Joe Jones
3 / 299 / James Smith
2
Placing / Competitor# / Name
1 / 299 / James Smith
2 / 340 / Joe Jones
3 / 225 / John Doe
4 / 212 / Tom Taylor
However, I'd like the report to also give the number of competitors in each group and calculate points. For example (underlined numbers are calculated wrt points and counted wrt # in class):
Class #
1
Placing / Competitor# / Name / Points
1 / 225 / John Doe / 9
2 / 340 / Joe Jones / 6
3 / 299 / James Smith / 3
# in class 3
2
Placing / Competitor# / Name / Points
1 / 299 / James Smith / 16
2 / 340 / Joe Jones / 12
3 / 225 / John Doe / 8
4 / 212 / Tom Taylor / 4
# in class 4
Points are calculated as follows:
In Class 1, John Doe is first in a class of 3. That means he gets 3 (# in class) * 3 (# in class) points for a total of 9 points. Joe Jones will get 3 * 2 = 6 points. James Smith will get 3 * 1 = 3 points.
In Class 2, James Smith gets: 4 * 4 = 16 points, Joe Jones gets: 4 * 3 = 12 points, John Doe gets: 4 * 2 = 8 points, and Tom Taylor gets 4 * 1 = 4 points.
How do I set this query and/or report up? I was trying to do it by counting the records in each group but it just wasn't working correctly. My tables consist of: Competitor (where CompID is pk and table contains competitor names, addresses), Event (where EventID is pk and table contains name and date and judge of event), Classes (where ClassID is pk and table contains Class Name, Fee, etc.). I also have a pivot table that contains EventID, ClassID, CompID, Comp# (different from ID - Comp# can vary between events but CompID stays the same) and Placing. (Placings are inputted via form that shows the Class Info along with a datasheet subform showing competitors. Program user will input placings into the form, which will feed the report)
I'm really discouraged by how much I've forgotten already.
Also, I tried the "Help" feature in Access 2000, and it wasn't much help. So . . . thanks for any help y'all can provide!
I have a good bit of the basic work done and I'm working on one of the reports now. However, I'm having problems with this one.
Here's what the report currently contains:
Event Name and Date: (e.g., ABCD Extravaganza)
Class #
1
Placing / Competitor# / Name
1 / 225 / John Doe
2 / 340 / Joe Jones
3 / 299 / James Smith
2
Placing / Competitor# / Name
1 / 299 / James Smith
2 / 340 / Joe Jones
3 / 225 / John Doe
4 / 212 / Tom Taylor
However, I'd like the report to also give the number of competitors in each group and calculate points. For example (underlined numbers are calculated wrt points and counted wrt # in class):
Class #
1
Placing / Competitor# / Name / Points
1 / 225 / John Doe / 9
2 / 340 / Joe Jones / 6
3 / 299 / James Smith / 3
# in class 3
2
Placing / Competitor# / Name / Points
1 / 299 / James Smith / 16
2 / 340 / Joe Jones / 12
3 / 225 / John Doe / 8
4 / 212 / Tom Taylor / 4
# in class 4
Points are calculated as follows:
In Class 1, John Doe is first in a class of 3. That means he gets 3 (# in class) * 3 (# in class) points for a total of 9 points. Joe Jones will get 3 * 2 = 6 points. James Smith will get 3 * 1 = 3 points.
In Class 2, James Smith gets: 4 * 4 = 16 points, Joe Jones gets: 4 * 3 = 12 points, John Doe gets: 4 * 2 = 8 points, and Tom Taylor gets 4 * 1 = 4 points.
How do I set this query and/or report up? I was trying to do it by counting the records in each group but it just wasn't working correctly. My tables consist of: Competitor (where CompID is pk and table contains competitor names, addresses), Event (where EventID is pk and table contains name and date and judge of event), Classes (where ClassID is pk and table contains Class Name, Fee, etc.). I also have a pivot table that contains EventID, ClassID, CompID, Comp# (different from ID - Comp# can vary between events but CompID stays the same) and Placing. (Placings are inputted via form that shows the Class Info along with a datasheet subform showing competitors. Program user will input placings into the form, which will feed the report)
I'm really discouraged by how much I've forgotten already.
Also, I tried the "Help" feature in Access 2000, and it wasn't much help. So . . . thanks for any help y'all can provide!
