By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

query is too complex, better way

P: 26
Ok let me start from the start. I have a form that has question in it and the person chooses 0 1 2 -99 for each. The form then needs to add up the numbers for the sub categories in the form. For example question 1-8 deal with communication and can rang from 0 to 16 points. The problem that I'm running into is the -99 which is needed to denote that the question does not ably. So if one of the communication questions is NA then it will make the communication score negative which is not right. In order to get around this I used a query that has the criteria <>-99. But the problem was that it was removing the whole entry for that person and not just the -99 for that question. So to get around this I used the <>-99 for each question separately and then nested the query into one larger query. This should work but with over 40 nested queries I get the error "query is too complex". So, is there a better way to get the questions added up and to ignore -99? Or is the another null value that I could use instead of -99? Can I do something different in the query to skip -99? Should I just do the calculations in the form but Im not that good at VBA.
Sorry for the long post put this is starting to agitate me thank for the help
May 15 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 2,653
Hi, BUmed.

I've read your previous thread and would make a guess that your databased is not normalized. This is, in most cases the reason for inappropriately large number of tables/queries as well as their complicatedness and inefficiency.

May 15 '08 #2

P: 26
This is true. I inherited this data base when I started this job. It is cumbersome and in inefficient. But we are switch to a new system in about 3mounths so I have to live with it for now. So any suggestion that could fill the whole for now.
May 15 '08 #3

Expert Mod 15k+
P: 31,494
Try using Null as the N/A value.

That way you can simply total all matching records and the Null records will be ignored in the calculations.
May 16 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.