If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculations in a query...Is there a max count?
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 |
#2
|
|||
|
|||
On Mon, 14 Feb 2005 17:13:01 -0800, HD87glide
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] |
#3
|
|||
|
|||
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 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] |
#4
|
|||
|
|||
On Mon, 14 Feb 2005 19:17:05 -0800, HD87glide
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Count Query with no values | The Pikey | Running & Setting Up Queries | 1 | January 10th, 2005 03:00 PM |
Counting rows in a Query | Janet | General Discussion | 9 | January 9th, 2005 05:13 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |