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

From: HD87glide (HD87glide_at_discussions.microsoft.com)
Date: 02/15/05 

Date: Mon, 14 Feb 2005 19:17:05 -0800

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" wrote:

> On Mon, 14 Feb 2005 17:13:01 -0800, HD87glide
> <HD87glide@discussions.microsoft.com> wrote:
>
> >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]
>