Solved

MS  Access 2007:  "Query is Too Complex!"

Posted on 2009-04-14
21
946 Views
Last Modified: 2012-05-06
Dear Experts:

I created a dozen of queries, which are individually  working great.

When I created a master query which contained a multiple number of queries  and ran it,

I got a warning as in the above.

How may I fix the trouble?

Thanks a lot!
0
Comment
Question by:duta
  • 11
  • 5
  • 3
  • +1
21 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24143429
Post screenshot(s) of your queries, or post the SQL for them.  And tell me exactly what results you're trying to get.
0
 

Author Comment

by:duta
ID: 24143503

Thank you so much for your kind, prompt reply.

I may not post even a screen shot of the queries for some reasons.

Here is what I have tried to go around the problem so far:

The ultimate goal is to create a periodical business report from dozens of
queries.

When I tried to create a report using Report Wizard, I had a problem too:

When I inserted one query, I could successfully create a report.  Obviously  I need to put many more queries in the report.  When I inserted more than one query, the report was not created.  The Access issued the followed warning:

"you have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that."


Thanks a lot!






0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24143577
Are you trying to create multiple reports or a single report?  If you are trying to create multiple reports IN a single report then that's not possible.
0
 

Author Comment

by:duta
ID: 24143585
I am trying to create a single report from a multiple number of queries.
0
 
LVL 5

Accepted Solution

by:
brandonvmoore earned 200 total points
ID: 24143609
When you design a query, you can't just throw tables into the query designer and expect Access to know exactly how you want them joined.  You can use the Relationships form to let it know ahead of time what the relationship should be, but you still had to give it that information.

Now think of each of your queries as a table.  You can't just throw queries into a report and expect it to know how to relate the information from various queries together.

You should create a single query that has all the information you need.  If it's not possible to extract the data from a single query, then you can include that query INSIDE another query.  Then give the top level query to the report.
0
 

Author Comment

by:duta
ID: 24143742
Thank you so much for your precious tip.  I will try to create a single query.

0
 

Author Comment

by:duta
ID: 24143958
bradonvmoore, I am very close to fixing the problem thanks to your great tip. Still there is a minor problem.

I created two new super-queries, each of which includes the critical information from sub queries.

I wish I could put in one super-query info from all of the eight sub-queries. But I could not.

When I put info from more than four queries, I got a warning "Query is too complex."

The warning was gone when I removed the info from the fifth query.

The questions are:

Is there a way to put all of the eight sub queries in one super query?

The query for one of the super query is  like the follows:

SELECT
Qry_Total_Income.Total_Income,
Qry_TotalAsset.Total_Asset,
Qry_TotalLiabilities.Total_Liab,  
Qry_TotalRevenue.Total_Revnue
FROM  
Qry_Total_Income,
Qry_TotalAsset,
Qry_TotalLiabilities,
Qry_TotalRevenue;


Thanks a lot!



0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24144036
Unfortunately I'm afraid you misunderstood me.  The approach you are taking 'might' work ok if you join your tables though.  The way you are joining them now (ie. or rather, not joining them) is called a cartesian join and it is not going to give you the results you're looking for.   Take the following two tables for example

tblParents
(ParentID,Name)
-----------
1,John
2,Bill

tblChildren
(ChildID, Name, ParentID)
-----------
1,Susy,1
2,Timothy,1
3,Joel,2
4,Joseph,2

Now lets say I do the following SQL statement:
SELECT tblParents.Name AS ParentName, tblChildren.Name AS ChildName
FROM tblParents, tblChildren

This would result in the following output:

(ParentName, ChildName)
-------------------------------
John, Susy
John, Timothy
John, Joel
John, Joseph
Bill, Susy
Bill, Timothy
Bill, Joel
Bill, Joseph

As you can see, it just outputed every possible combination of the rows from the 1st table and the rows from the 2nd table.  This is because we didn't specify how the two tables are related.  If we want to see a listint of parents next to children that belong to that parent then we would use the following statement:

SELECT tblParents.Name AS ParentName, tblChildren.Name AS ChildName
FROM tblParents INNER JOIN tblChildren on tblParents.ParentID = tblChildren.ParentID

This would yield the expected results:

(ParentName, ChildName)
-------------------------------
John, Susy
John, Timothy
Bill, Joel
Bill, Joseph


Now, if I were to enclose the entire FROM clause (not including the word FROM) from that statement in parenthesis, I could then append another INNER JOIN clause to the end and join another table.  I could do that again to join another table as well.  It might be possible to use that strategy to get access to all the data you need in the report, but it's not the best or even a good way to do it.

When I said you needed to do it from a single query, I didn't mean that you needed to make a query of queries.  I meant you needed to start a NEW query from scratch and get the data you need directly from the tables.  You would need to refer to the existing queries to see what calculations are done, if any, and copy them to your new query.  For someone like yourself, who has limited SQL experience, this is probably going to be work.

If you would like to provide the SQL statements for each of your queries, as well as a list of all fields you plan to use in your report, I will see what I can do to further lead you in the right direction.
0
 

Author Comment

by:duta
ID: 24144081
brandonvmoore, thank you very much for your kind, patient help.

I will take time to carefully read your posts and try to follow your tips.

I will be back a while later.
0
 

Author Comment

by:duta
ID: 24144119
In a relatively short period of time, I have learned a lot about SQL query.

This task is quite complex.  I will be thrilled if I can learn how to finish this task with you genius'
kind help :)

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Assisted Solution

by:jstadler7823
jstadler7823 earned 150 total points
ID: 24144586
Why one query to query all?  This task could be completed with a blank main report with sub - reports for each query result or find a common field like a date field and have all the individual queries update a master table with the results you need.

Basically the master table would be:

Date    Qry1.Number    Qry2.Number.

Okay Im done hope I didnt screw you up here.
0
 

Author Comment

by:duta
ID: 24144606
jstadkler 7823,  thank you so much for your precious tip.

By the way, it seems to me you are extremely busy now.

I just wonder whether you may kindly elaborate a little bit more on what you just wrote:
have all the individual queries update a master table with the results you need.
__________________________________
Basically the master table would be:

Date    Qry1.Number    Qry2.Number.
__________________________________

I am stuck and struggling for hours and hours.

Sub reports seem to offer some hope for me.

Do I need to open a blank main report and then drag-drop super queries (queries of queries) into

the blank sheet of report?

Thanks a lot!
0
 

Author Comment

by:duta
ID: 24144610
And what did you mean by "the master table"?

Did you mean "the main report sheet" by the "master table"?

Thanks again!
0
 
LVL 2

Expert Comment

by:stacydr
ID: 24144683
Sometimes when I am working with a lot of queries.. and trying to connect them together  (they all have one common field - say Company_ID) I get that error.
But if you make then from Select Queries to Make-Table queries.
Then try connecting the smaller tables together.
Access doesn't have to qry each qry as the data is now hard-coded.
But all the queries need to have one common field or you won't be able to link them together.
Good Luck
0
 

Author Comment

by:duta
ID: 24144703
Dr Stacy, thank you so much for your kind tip.

I have two types of queries: One type of queries are related to income side of a business report, and the
other type  of queries are related to expense side.  As a result, there is no common common field between them.  In this situation, what may be the best way to create a report from the two types of queries?

Thanks!
0
 
LVL 2

Assisted Solution

by:stacydr
stacydr earned 150 total points
ID: 24144735
Hi-
I would create one report with the income queries.

I would create a second report with expenses.

Pick one report to be your primary. (Let's say income)
Click on the subreport icon, and choose the expense report and place it in the report header of the income report.
When the wizard asks if you want to link them - choose none or ignore and move next.
Then the reports will run independently, but they will be within one report.
stacy
0
 

Author Comment

by:duta
ID: 24144806
Dr Stacy, thank you so much.

I have a hard time creating sub reports.

I think I need to post a new question regarding how to create a sub report.

I will be back after learning how to create sub reports.


Thanks!
0
 
LVL 2

Expert Comment

by:stacydr
ID: 24144849
subreports are the same as regular reports -- they just sit in other reports hence sub.
you build them all the same way...
0
 

Author Comment

by:duta
ID: 24144861
Thanks a lot, Dr Stacy.   There are too many things I do not know.
I wish I knew a little more and so I could better follow your kind tips!

0
 
LVL 1

Expert Comment

by:jstadler7823
ID: 24152834
Hi Duta,

Sorry Yes I have been very busy.  I am curious as you said the income and expense have no common relationship.  BUt if they are for a business they would tend to be based on AP and AR operations which tend to carry dates.  If not.

Then are you just trying to have a report that shows two numbers income vs. expense can you post a screen shot of your failed query to help to understand your question?
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24152837
If you could describe what it is you want on your report, and list the names of the tables that the data comes from (don't just list the tables, but also tell which fields come from which tables), then I could help you a lot better.

And just to clarify, I don't need to know about your queries you've already created.  I just need to know what your final report is supposed to show, and what fields/tables it gets its data from.  If this is a difficult question for you to answer then just start with what your report is supposed to output.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now