"Too many fields defined" error

[MorningSun]

I am trying to do a multitude of calculations within a query based on several different tables of information. I keep getting a "Too many fields defined" error when I add the 21st field to the query. Some of the calculations are quite complex. I know that my database is not normalized well but there is nothing I can do about it now. Why would I get this error with only 20 fields in a query?? Does the query take into account how many fields are being calculated upon? I have compacted my database over and over and I have tried recreating the query in a new query.

Any ideas?
Thanks!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[DanChard]

I think if you posted the SQL behind your query then someone might spot how it can be fixed/improved. Some idea of the how the DB is organised would be quite handy too...

 

[MorningSun]

Here's the SQL (I used the wizard for this so I wouldn't know an error here if it jumped up and bit me):

SELECT tblCSAudit.AuditNo, tblCSAudit.Quarter, tblCSAudit.Location, tblCSAudit.Team, tblCSAudit.Manager, tblCSAudit.AuditType, qryCSAuditTotalsSub.FileMaintenanceAudited, qryCSAuditTotalsSub.FileIncorrect, qryCSAuditTotalsSub.GSSAudited, qryCSAuditTotalsSub.GSSIncorrect, qryCSAuditTotalsSub.AgreementAudited, qryCSAuditTotalsSub.AgreementIncorrect, qryCSAuditTotalsSub.MagicAudited, qryCSAuditTotalsSub.MagicIncorrect, qryCSAuditTotalsSub.DataAudited, qryCSAuditTotalsSub.DataIncorrect, qryCSAuditTotalsSub.IRIMSAudited, qryCSAuditTotalsSub.irimsTransfer, qryCSAuditTotalsSub.GPCAudited, qryCSAuditTotalsSub.GPCIncorrect, qryMerlinAudited.MerlinAudited
FROM ((tblCSAudit LEFT JOIN qryCSAuditTotalsSub ON tblCSAudit.AuditNo = qryCSAuditTotalsSub.AuditNo) LEFT JOIN qryMerlinAudited ON tblCSAudit.AuditNo = qryMerlinAudited.AuditNo) LEFT JOIN qryMerlinIncorrectTotals ON tblCSAudit.AuditNo = qryMerlinIncorrectTotals.AuditNo
GROUP BY tblCSAudit.AuditNo, tblCSAudit.Quarter, tblCSAudit.Location, tblCSAudit.Team, tblCSAudit.Manager, tblCSAudit.AuditType, qryCSAuditTotalsSub.FileMaintenanceAudited, qryCSAuditTotalsSub.FileIncorrect, qryCSAuditTotalsSub.GSSAudited, qryCSAuditTotalsSub.GSSIncorrect, qryCSAuditTotalsSub.AgreementAudited, qryCSAuditTotalsSub.AgreementIncorrect, qryCSAuditTotalsSub.MagicAudited, qryCSAuditTotalsSub.MagicIncorrect, qryCSAuditTotalsSub.DataAudited, qryCSAuditTotalsSub.DataIncorrect, qryCSAuditTotalsSub.IRIMSAudited, qryCSAuditTotalsSub.irimsTransfer, qryCSAuditTotalsSub.GPCAudited, qryCSAuditTotalsSub.GPCIncorrect, qryMerlinAudited.MerlinAudited
ORDER BY tblCSAudit.AuditNo, tblCSAudit.Quarter, tblCSAudit.Location, tblCSAudit.Team, tblCSAudit.Manager, tblCSAudit.AuditType;

As for organization, Each "audited" and "incorrect" reference in the above is a calculation based on supporting tables. All addition of multiple items that could be audited and whether or not they are correct (that's where normalization should have been a part and it is not).

Hope this gives you a little more insight. Thanks!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[Golom]

You are GROUPing BY every field in the SELECT so SELECT DISTINCT ... should give you the same effect and you can eliminate the GROUP BY ... but I don't thing that's causing the problem.

This query is based in part, on other queries (qryCSAuditTotalsSub, qryMerlinAudited and qryMerlinIncorrectTotals). I suspect that, because Access dynamically runs all of them when you run this one, the problem is with the total fields in all queries ... not just the fields in this one.

To check that, go to each of those other queries and do a "Make Table" with them, then reference the tables rather than the queries in this query.

 

[MorningSun]

Someone else mentioned doing a Make Table but I have never done that before. Guess I should read up on that! If you have any quick pointers or advice on Make Tables, let me know. Thanks so much for the suggestion!! I wasn't aware that Access would count the fields in the queries that I was pulling from. Seems to defeat the purpose of drilling down info with queries. Just my opinion.... =)

Thanks again! HAPPY HOLIDAYS!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[MorningSun]

Okay, here's the next problem. I was able to change the query to a Make table query. But, I still then had to use the resulting table in an additional query to get the last calculation (that was causing the too many fields error). So now I have tblCSTotals (result of make table query) in a query with qryMerlinIncorrect (which adds up the last of the scores that I needed to fit in)called qryCSAuditScores Now, how do I get the Make Table query to refresh when new records are added and the qryCSAuditScores is run? Right now, I have to run the make table query and THEN the qryCSAuditScores that the report will be based on. I want to be able to have the report run at a push of a button and include ALL calculations....

AND to confuse the situation even more, it appears that even though the make table query works (but isn't updating as records are added) the Report still tells me that I have too many fields defined.... ???? I'm at a loss.

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[MorningSun]

One more note, the more I try to drill down, the more confusing it gets. Here's the current situation. It seems that the MerlinIncorrect total is the one giving me the trouble. I have a query that runs just fine called qryMerlinIncorrectTotals. All it has is the Audit Number and the calculation. When I try to add that query to any other query, that's when I get the Too many fields defined error. ALSO, the query runs fine alone, but if I try to change it from a Select Query to a Make Table query, it gives me "Too many fields defined" once again.....

Does this make any sense at all?

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[DanChard]

Hi there,

I'm pretty much at a loss when it comes to makeable queries but if you post the SQL from the following 3 queries of yours:

qryCSAuditTotalsSub,
qryMerlinAudited,
qryMerlinIncorrectTotals

It should be possible to get everything into a single query and then go from there. This is assuming that none of the 3 queries above select from any other queries.

Thanks,
Dan

 

[MorningSun]

They all select from other queries. It seems that I can get everything to work until I try to add in the MerlinIncorrectTotals. It appears that everything works fine with the MerlinIncorrectTotals on it's own but just not in combination with ANYTHING else. MerlinIncorrectTotals has the exact same number of fields as the MerlinAudited so I don't know why it would work for one and not the other.

I am truly stumped!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[DanChard]

I try to avoid selected from queries within queries whenever I possibly can for exactly this reason. I'm sure it is possible to achieve whatever it is you're trying to achieve without changing the database layout but it's made very difficult to work out exactly what's going on without being able to see all the SQL behind the final query in one place.

qry_a
=====
select first_name, last_name, date_of_birth
from people
where middle_name like 'J%';

qry_b
=====
select first_name, last_name
from qry_a
where format(date_of_birth,'mm') = '12';

qry_final
=========
seleect first_name
from qry_b
where last_name = 'SMITH';

qry_final will return all records from the 'people' table where the middle name begins with a J, last_name is 'SMITH' and where the date_of_birth is in December. However, it's not obvious from qry_final what is happening and you have to check two other queries just to find out! It would be much simpler to have everything in a single query and it's almost always possible to do so.

I would suggest re-arranging your query so that it does not select from any other queries and pasting the SQL here. It's much easier to work out what's going on then as it doesn't require any pre-requisite knowledge of what other queries involved (or queries those queries use as a source) are doing.

 

[MorningSun]

If I try to put it all into a single query without creating subqueries, I get "Query is too complex". The SQL is entirely too long to post here. Thing is, I am adding so many variables per field that I have had to break each calculation into several calculations due to space limitations per field. IE Merlin Audited Total= Merlin Audited1+Merlinaudited2+....+MerlinAudited13. That is why I did the subqueries. To pull only the Merlin Audited Total without all of the calculations but apparently the final query is adding all the fields anyway and maxing out at 255.

I'd be happy to zip the file to send to you so you can see the mess I've gotten myself into. There has to be a solution to this but it's looking bleak as of now!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[DanChard]

Yes OK, perhaps being able to see the DB for myself might be useful.

You can mail it to:

monkeydchardattalk21.commonkey

but kill those monkeys on either side first...

 

[MorningSun]

I'm sorry, I don't get the address. Did you mean to leave out the @aol or other provider? Thanks

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[MorningSun]

Nevermind, I get it but my computer is not letting me send it. I'll keep trying.

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[lespaul]

Dawn,

It looks like you have an unnormalized table (first clue:
Merlin Audited1+Merlinaudited2+....+MerlinAudited13 - now this may be your query names, I don't know!)

Anytime you have fields named 1 and 2 and 3... and 13, your structure isn't normalized. If you have the ability to change the table structure, your queries would be much easier to create and you would be able to extract the data more easily.

Here's a link to an article on JeremyNYC's website that explains what normalization is, why you should do it and how to do it.

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

If you can't restructure, please give us the table names, fields in each table (that is relevant to what you want to accomplish), how the tables are related (PKs and FKs), some sample RAW data, and a sample of what you want your final results to look like. In an unnormalized database especially in order to help you we need a lot of information.

Leslie

 

[MorningSun]

Yes, the data is not normalized and there is nothing that I can do about it now. I've had the unfortunate pleasure of building a "working" database. My users are using it as I am building it. There's already some 1000 records in some of the tables. YUK! The joys of being self taught. The data is so huge that I don't know that listing it here would be so easy. I am willing to send a zip file for anyone to look at. In a nutshell, I have tblCSAudit, tblFileMaintenance, tblMerlin, tblMerlin2, tblMerlin3, tblMerlin4 that all contain a series of items that are audited during quality assurance audits. Fields included in each table are AuditNo (PK in tblCSAudit FK in others)and a series of Audited and NotApplicables for each audit form. I have to add all of the NA's from each table as well as the Audited value in order to come to a percentage accuracy. IE, (MerlinAudited-MerlinIncorrect)/ MerlinAudited = MerlinAccuracy. Problem being, Merlin audited consists of A LOT of items.

I doubt that this helps you very much but it's very complex and then not normalized on top of it all!! Let me know if you'd like me to send the db to you or post it elsewhere.

Thanks so much!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[mansii]

Sun,

this link might be useful:

http://support.microsoft.com/defaul...port/kb/articles/Q192/7/16.asp&NoWebContent=1

mansii