I have a very simple database for tracking a Nascar pool. I changed the database this year to include two more picks on drivers (7 choices total). I made a query for the players total points (listed below). The query works great along with the rest of the data base. But when I try to make a report with this query it will not run. I have used the report from last year, run the reports wizard and every combo I can think of but I always get a "to many fields defined" error or another one that says something about a file may be in use somewhere else (have not seen that one for awhile so I can't remember excataly what it said).

If I take out two of the "players points" it will work fine. I can make different combonations of this expression and the report will work IE: players Picks 5. over and over will work, but when I include all 7 picks I get the errors.

Following is the expression build I have in the Players Query, the report links to the Expr1 field.

Expr1: [Players Points 1]![Total]+[Players Points 2]![Total]+[Players Points 3]![Total]+[Players Points 4]![Total]+[Players Points 5]![Total]+[Players Points 6]![Total]+[Players Points Rookie]![Total]

What makes it more confusing is if I tweak the Expresion above to show something like (or any combo I want):

Expr1: [Players Points 1]![Total]+[Players Points 2]![Total]+[Players Points 3]![Total]+[Players Points 4]![Total]+[Players Points 5]![Total]+[Players Points 4]![Total]+[Players Points 5]![Total]

It will work, its only when I try to have all seven fields added up that I get the problems. I have tried this will all of the Players Points querys so I don't think there is anything wrong with them.

IE: Expr1: [Players Points 1]![Total]+[Players Points 1]![Total]+[Players Points 1]![Total]+[Players Points 1]![Total]+[Players Points 1]![Total]+[Players Points 1]![Total]+[Players Points 1]![Total]

Expr1: [Players Points 2]![Total]+[Players Points 2]![Total]+[Players Points 2]![Total]+[Players Points 2]![Total]+[Players Points 2]![Total]+[Players Points 2]![Total]+[Players Points 2]![Total]

and so on, with 3, 4, 5, 6 and Rookie. All of these combos work great.


I also ran a test to see how many fields this was looking in to, the count came up with 11 (like I say this is a very simple database).


I'm new at Access so I am hoping this is just small thing I have overlooked, but I'm stumped...

Thanks for everyones help,

Kelvin

Kelvin,

welcome to Utter Access

What do Palyers Points 1,2,3,4,5 represent? Initial thinking is that your DB may not be Normalized but I can not say that with any certainty. Can you give a little more detail? Where are the Combos Coming into play?

Earnhart all the way!

Edited by: jmcwk on Tue Feb 6 13:49:22 EST 2007.

The players Points are based off of the driver they have selected IE: Jimmie Johnson may be the Players first pick. I have a query that will then add up all of Jimmie Johnsons points and give them to the player. This is the same for the other 6 drivers picks.

I have a report that will pull up:
Drivers Total Points: this is the points that a certain driver (IE: Jimmie Johnson) has so far for the season.
Drivers Points: this pulls up the points a certain driver has for each race
Players Points: this is the report I am having problems with
Players Picks: this show what drivers each player chose: There are 6 groups with certain drivers listed in a pull down box and then a Rookie group.

All of the menus, reports and queries seem to be running great so I am not sure why this one report is being a pain.

The only thing changed from the database last year was the addtion of two more driver groups (last year you only had five groups of drivers to choose from and that cause to many ties so we bumped it up to seven groups.
Hope this is what you were looking for, Kelvin

Kelvin,

Are you saying that the players picks are in seperate groups? Ie you have a table for Jimmie Johnson,Dale Earnhart,Jimmie Stewart etc. Or do you have something like

tblGroups
pkGroupID
Group

tblDrivers
DriverID
fname
lname
fkGroupID

Lol, sorry just noticed your Earnhart comment, I agree Jr. is a great driver.. Unfortunatly I need to choose either Dale or Jimmie Johnson for group 1, what a rough decision..

Sorry for the terrible replies to your questions. I should let you know the first time I used Access to make a data base was last year when I made this one. Well a year later they wanted me to make these updates. So I have not even looked at Access for a year (talk about a rookie).

The way the system works is when you enter a drivers name in the data base you need to choose if he is in group 1, 2, 3, 4, 5, 6 or Rookie. This will the put that driver into three different files:

1. A table that lists the drivers name, group and points for each race
2. A query for each group (group 1, group 2, etc), this only lists the drivers name and the group he is in)
3. A query that also shows each driver and the points for each race

When you enter a players information you are asked for the players name and other personal info, then you have 7 drop down boxes in which each of the drivers for that group are listed. You need to choose one driver out of each drop down box.

this will. (to be continued)

Players picks (continued)

Put the players information in:

1. a table that shows the players name and drivers picks for each group
2. a query that shows the players name and the drivers pick for each group
3. a query that shows the players name, the drivers name and points for that driver in each group.
There are seven of these querys: called Players Points 1, Players Points 2, Players Points 3, etc. This is where the information for the players total points query comes from.
4. a query "Players Total Points" this has the players name and the players total points (Expr1) information in it.

I'm sure I could have made this a lot easier, but it must have made sense to me last year when I designed this thing, lol.

Thank you again,
Kelvin

Kelvin,

still not real clear to me but can you attach a stripped copy of your Db with no sensitive data and no larger than 500kb? Will be heading to lunch in about 10 minutes but I shall return.

John I hope you don't mind but I just e-mailed you the database, it is only about 3mb and there is no real information in it yet, just test info..

Thanks for you help again,
Kelivn

Kelvin,

Received it and will take a look after I return from lunch

Kelvin,

You need to use a totals query in your groups querys and sum the totals rather than the select query as tyou have them. Sent the Db back to you with this correction.

Am still curious on this:

How are the players awarded points? Do they get so many for a 1st,2nd,3rd etc?
How are the Drivers awarded points? Do they get so many for a 1st,2nd,3rd etc?

What does the Group represent?

They Have the Daytona 500 and they select 6 groups ???


I think I would have something like this:

tlkpPlayers
pkPlayerID
lname
fname
etc.

tlkpRaceID
pkraceId
Race
RaceDate
etc.

tkplDrivers
pkDriverID
lname
Fname
etc.

tlkpGroups
pkGroupID
Group

tblRaceDetail
lname
fname
pkDetailID
fkPlayerId
fkDriverID
fkGroupId
fkRaceID

Then a couple of tables based on the Win Loss or placement of the Driver in the race assign a point value

HTH

Edited by: jmcwk on Tue Feb 6 16:24:43 EST 2007.

Problem solved!!! Thanks so much for your help.. What a great site...

Kelvin,

Your Welcome And Good Luck with your Project?

For those following this thread the problem was the OP was trying to get a total using the total field in a select query whereas it should have been a Totals Query and grouped by DriverID. Once it was changed the problem was resolved.

And Yes You are correct THIS IS A GREAT SITE !