Query Issue: Too Many Fields defined

[Timothy Andrepont]

First thanks for your help in the past. It is greatly appreciated and allowed me to move forward.

Quick summary (See Attached)

Data is stored in 11 tables each with matching primary key. Each table has in the range of 20 fields of which only numbered data type are being used in calculations.

Each Data table has 3 augmenting queries. All these queries work individually.

Summarizing query "QStationCountList_Sum" works fine.

Calculating a summation score by each "Master_PS_ID" works fine if I only try to summarize data by 3 of the calculating queries. As you can see in the attached database "QStationTotalScore1_6" query based on 6 gives "Too many fields defined". (I have tried so many fixes I can not remember and received messages such as "To Complex") While a similar query on 3 works fine.

At this juncture I plan to build 4 new partial sum queries each from 3 collecting queries and then build a 5 Summation query adding the results from the Sum queries. Surely there is a better way.

Am I over looking something obvious? Any suggestions welcome.

Timothy

 

[billmeye]

I believe Access has a limit to how many characters can be in a single query, something like 65,000 or such. When you have nested queries, Access is actually making one very big SQL statement to resolve them and I think you are exceeding the character limit. I quickly changed your sub-querires to MakeTable queries and used those tables in your overall query QStationTotalScore1_6. As you can see it works. My suggestion is change your flow to either delete/make tables prior to calling your overall query (may not work in a runtime environment). Or, just do Delete queries to clear the data and them run Append queries to update table with latest data.

 

[Timothy Andrepont]

Billmye,


Thanks for your quick response.

This will move me into a whole new section of Access I have not yet used.

I guess it is time to move ahead.

I will also have to learn how to automate the append table process.

Thanks for your help.

Timothy

 

[billmeye]

You'll figure it out, just keep asking questions on the boards and you'll be there in no time.