Query too complex error...

[andrewhanna]

I don't know a whole lot about Access or SQL queries. The error is supposed to mean that there are too many JOIN's or the SELECT statement is too big. I broke down the query, and now when I put anything into the new main query...I still get this error.

 

[David R]

Can you post the SQL version of your query?

 

[andrewhanna]

I am using Access 2000 but there are about 100 queries in the database. The main query calls the others to order them for the report. I had to split up that query because it was too complex and now it works, with about 30 other queries attached. But when I try to do another one...it gives the too complex error. Here is the one that is now giving the error.

SELECT Count(CallNumberTotal.CallNumber) AS CountOfCallNumber2
FROM [Isolation], Eating, Dissociation, Employment, BodyHarm, PartAlien, FamAlien, STD, [Region Bancroft], [Region Belleville], [Region CentreHastings], [Region PEC], [Region Trenton], [Region Tyendinaga], [Region Unknown], Flashbacks, Housing, DID, Disability, Pain, Drugs, RelationProbs, Mutilation, Orientation, Insomnia, Nightmares, PastSuicide, Suicide, Pregnant, Anger, Counsel, Survivor, CallNumberTotal;

 

[David R]

Unless I am totally misreading your query, you have a serious data normalization problem. I realize you said you don't have much Access experience, do you even know what I mean when I say that? I'm not trying to be condescending, just get a starting point to help you..

Can you explain in English what you are trying to do and WHY you have to have 100 queries? It looks like you have queries dedicated to pulling information from specific regions when you should have a [Region] field and Group By each value. Ditto the [Disorders] at the end, and what looks like [Causes] at the beginning.

How many tables do you have and what fields are in them? If you have 25 regions or whatever you can summarize with 'etc...'

Thanks,
David R


[This message has been edited by David R (edited 04-26-2002).]

 

[andrewhanna]

Okay, here it is. I have written a C++ program to track calls. Now, the end result needs to be a report. However, I don't have the time to code a report window from scratch for this application. Most of the fields in the database are yes/no fields. However, there are also some combo boxes in the program. That is why there are multiple queries (need to get the total times that each choice in a combo was selected).

Now, I had a friend from college help with the database (because my personal expertise is in programming logic, not databases). Don't worry about sounding condescending, if you can help...thats all I care about lol.

The report needs to issue date to date totals, usually one report per month. However, along with that monthly report, I need a year to date total. The way it is setup now, is the only way my friend thought it could be done...but his knowledge of databases is limited as well.

Now, as for the Normalization. I recognize the term and am going to look it up, but no, I am an idiot when it comes to this type of thing....please forgive my stupidity!

 

[David R]

Nope, not accusing you of being an idiot at all. If you want to see where I came from, check my original post here, with...guess what? A horribly unnormalized database. http://www.access-programmers.co.uk/ubb/Forum3/HTML/002378.html
My favorite educational method is being hit over the head with a 2x4 repeatedly until the light goes on. That being said...

It looks like these are both Problems and Regions in your database, and you want to count how many calls about each problem, right? Do you need to break this down by Region or is that a separate report?
So Isolation], Eating, Dissociation, Employment, BodyHarm, PartAlien, FamAlien, STD, Flashbacks, Housing, DID, Disability, Pain, Drugs, RelationProbs, Mutilation, Orientation, Insomnia, Nightmares, PastSuicide, Suicide, Pregnant, Anger, Counsel, Survivor are all Problems people might call about? And they're all separate fields because you have Yes/No checkboxes associated with them, and people may be calling for more than one reason? Do you collect any more personal data on your callers than just a CallID and maybe a time/person they spoke to? Anything individual to the person/call, irregardless of why they called. [Region] might fall into this, or maybe [Region] is where they're calling to?

I think I understand but I want to be sure before we leap into restructuring your data. Also, is this a database already in use, or does it just have data you need to analyze, or is it still being created and has no 'real' data in it?

 

[andrewhanna]

The data that is currently in the database will be deleted. The program that I built in C++ writes all the data to the database.

Now, it works like this. The program is for a sexual assault centre. So, no names are ever used or any other personal information...just the stuff that you see. *Remember that there is more...but I had to split it up.

You are right about the yes/no boxes. A caller may call in and be discussing several of those options. In the program, places where more than one option can exist, is a yes/no in the database (checkbox in the program). But, there are also places where they may only choose one option (drop-down box in the program) and that is a text data type in the database.

The totals work like this. Each month the Centre has to report to the government on how many total calls there were that month, and how many people called from which region...ie. Toronto had 25 callers. Ottawa had 32 callers.
As well, how many called about Abortion that month. And so on...

In the report as well, they also need a year to date total for everything. Essentially, the report will have two columns. One will state the totals for the start and end date that the Centre chooses (usually monthly) and another column for the year-to-date totals.

The other thing about the setup of the database, is I had to make it so I didn't overload the program with recordset classes. That is why everything is in one table.

Thats all, I hope you were able to follow that.

 

[David R]

Unfortunately, I did follow it. The problem is that the last C coding I did was in C, not C+ or C#. SO I'm not sure about the interactions between your front-end and a properly structured database. I'll refer you instead to read up on database normalization and see if you can't get a better structure going. You should NOT need 100 queries to get the results you ask for. A couple simple reports in Access can do it with the right data, and while I know how to make this front-end in Access, I don't know how to help you with a C++ coded one.

Your main table should include call-specific data; as you said, no personal data is collected, but Region is a text field and could be a combo box of the different regions, NOT separate fields Yes/No.
Reasons for calling can be stored in a separate table. That table should have its own separate Primary Key, but more importantly a field as the same type as the PK of your main table. When you store that key in this table you're telling it "this subrecord goes with THAT record on the main table". Tools>Relationships will allow you to make a one-to-many relationship between the two.
I would personally put all of the Reasons in a combo box and allow the user to select as many as they need. Alternatively you can use checkboxes to populate these subrecords. If this is a write-only form and you'll never have to go back to it to change existing data, you should be able to store these subrecords fairly easily with C++. Just store the CallID in its field and the Reasons that are selected and your table will be correct.

Now when you make your reports (in Access I assume?), look into the Grouping options and Totals Queries. You can make several subreports that show all the breakdowns you are looking for with this structure.

HTH,
David R

 

[andrewhanna]

Thank you for all the help David. I really appreciate it. I will take a look at it and try to figure it out.

Thanks again.

Andrew

 

[andrewhanna]

I have to be entirely honest with you, David. I don't have the time to re-write the database. Whenever the database is changed like that...I have to re-write the program. But I am coming down to the wire. Is there no way to figure out the queries and so on without re-writing the database?

 

[David R]

There's almost always a way. Whether it will be practical to implement is another question...

Look into UNION queries, Make-Table and Append Queries to make temporary tables in the proper structure, and then you can write reports off of those. Otherwise it would be more of a subcontracting operation to get it done for you, sorry...