Calculations in a query...Is there a max count?

[Guest]

Hello Group! I have a complicated question. I have one table that holds one
record for each person. In that record there are fourteen weeks of scores.
The fields are S1, T1, R1, S2, T2, R2...S14, T14, R14. I have a need to keep
all fourteen weeks of the league together.

For each week in the league there are five additional fields that are then
calculated from the weekly scores, total, adjusted total, weekly avg, week
over/under, aggragate. In the form that captures this data all of the
calculations work fine. I am now trying to produce a query that will do all
five calculations per week for the fourteen week league timeframe, for each
member.

When I use the build option to construct a field now it hangs MS-Access and
I have to do a CTRL-ALT-DEL and kill the process. My question is, is there a
max count for the number of fields that a query can perform calculations on?
I have a need to keep all the data together due to the fact that some of the
calculated fields in week two depend on the results from week one.

Any ideas?

Thanks,
Jeff

 

[John Vinson]

Hello Group! I have a complicated question. I have one table that holds one
record for each person. In that record there are fourteen weeks of scores.
The fields are S1, T1, R1, S2, T2, R2...S14, T14, R14. I have a need to keep
all fourteen weeks of the league together.

Ummm...

I doubt it. Your problem is that your table IS INCORRECTLY DESIGNED.

You have a one to many relationship between people and scores. You can
"keep this together" using *two* tables: one for your person
information, related one to many to another table with PersonID (link
to the "one" side table), Week, S, T, and R (whatever these are). If
you have seven weeks of scores you have seven records; if you have
fourteen weeks, you have fourteen records.

For each week in the league there are five additional fields that are then
calculated from the weekly scores, total, adjusted total, weekly avg, week
over/under, aggragate. In the form that captures this data all of the
calculations work fine. I am now trying to produce a query that will do all
five calculations per week for the fourteen week league timeframe, for each
member.

Easy with a Totals query based on the normalized structure. As you can
find, much more complicated if not.

When I use the build option to construct a field now it hangs MS-Access and
I have to do a CTRL-ALT-DEL and kill the process. My question is, is there a
max count for the number of fields that a query can perform calculations on?
I have a need to keep all the data together due to the fact that some of the
calculated fields in week two depend on the results from week one.

Use DLookUp to find them in the normalized table.

John W. Vinson[MVP]

 

[Guest]

Hello John, Thanks for the reply! The way that the table is set up I can
remove the fields from week two thru week fourteen and add a field week. My
question back to you is how can I get all fourteen weeks to show up on the
form at one time? Would I open the table fourteen times? or can I create an
array?

Thanks,
Jeff

 

[John Vinson]

Hello John, Thanks for the reply! The way that the table is set up I can
remove the fields from week two thru week fourteen and add a field week. My
question back to you is how can I get all fourteen weeks to show up on the
form at one time? Would I open the table fourteen times? or can I create an
array?

Use a Form based on the person table, with a continuous Subform based
on the scores table. You'll see fourteen rows.

John W. Vinson[MVP]