473,893 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

"Query is too complex" error

Hi all, Thank you for taking the time.

I have a database with 45 tables on it. 44 tables are linked to a main
table through a one to one relationship.

My question is, is there no way i can have a query that will pull a
single field from all the tables. In other words i should have 44
fields. when i try to do that same, i get an error message saying
"Query is too complex"

i tried splitting the query into 4 different queries with each having
10 tables, but when i try to merge the 4 queries, it still gives me
the same message. any help would be greatly appreciated.

Thanks again.
Nov 13 '05 #1
8 5071
Is there a reason you have 44 1-to-1 relationships? That sounds odd in itself.
Nov 13 '05 #2
pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...
Is there a reason you have 44 1-to-1 relationships? That sounds odd in itself.


have 44 tables with the exact same fields except for one field, & that
is the department name. there are 44 different departments & this
database would track the same variables for all the departments for a
given date. the reason i had a had a one to one relationship is that i
need to query on a particular department or departments.. hope that
explains it.
Nov 13 '05 #3
Matt,
Jeeze. 44 one-to-one relationships. A lot of designers would argue (and I
agree), that for one-to-one relationships the easiest way to go is stick
them all in one table. You can use views to parse them out and present them
as 44 different tables to the user community. Now, your query is too
complex because you need 44 joins to present your 45 tables in a one-to-one
relationship. Access is basically giving up. I think you've succeeded in
exceeding the number of joins Jet will support. As a rule of thumb its wise
to design things so that a view uses no more than three joins. The rule
dates from the days when disk space, memory & processor time were expensive
and SQL that used more than three joins became a resource (read money) hog.
If at all possible, put all those tables in a one-to-one relationship back
together in one table. Then go back to the user community and identify
their needs so that as needed you can design views to present the data in a
way that is useful to them.

"Matt" <yo*******@yaho o.com> wrote in message
news:fe******** *************** ***@posting.goo gle.com...
Hi all, Thank you for taking the time.

I have a database with 45 tables on it. 44 tables are linked to a main
table through a one to one relationship.

My question is, is there no way i can have a query that will pull a
single field from all the tables. In other words i should have 44
fields. when i try to do that same, i get an error message saying
"Query is too complex"

i tried splitting the query into 4 different queries with each having
10 tables, but when i try to merge the 4 queries, it still gives me
the same message. any help would be greatly appreciated.

Thanks again.

Nov 13 '05 #4
Matt,
Boy, talk about making your life hard at the outset. Ok, your department
columns *really* need to be 44 rows in one table with all the columns you
have plus a column named "DEPARTMENT " As for presenting the departments in
44 columns, look at the pivot table view of Access XP or pivot tables in
Excel. You have way more flexibility with presenting the data columnwise,
rowwise, or whatever your users desire. 44 tables, one each for each
deparment? My head hurts.

"Matt" <yo*******@yaho o.com> wrote in message
news:fe******** *************** ***@posting.goo gle.com...
pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...
Is there a reason you have 44 1-to-1 relationships? That sounds odd in

itself.
have 44 tables with the exact same fields except for one field, & that
is the department name. there are 44 different departments & this
database would track the same variables for all the departments for a
given date. the reason i had a had a one to one relationship is that i
need to query on a particular department or departments.. hope that
explains it.

Nov 13 '05 #5
yo*******@yahoo .com (Matt) wrote in message news:<fe******* *************** ****@posting.go ogle.com>...
pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.com>...
Is there a reason you have 44 1-to-1 relationships? That sounds odd in itself.


have 44 tables with the exact same fields except for one field, & that
is the department name. there are 44 different departments & this
database would track the same variables for all the departments for a
given date. the reason i had a had a one to one relationship is that i
need to query on a particular department or departments.. hope that
explains it.


Okay, so now I *definitely* agree with Alan. Put it all the records
in one table, index it, and filter the table. Then you can just open
a parameterized query and just pass different parameters to it.
Infinitely easier/more practical than the alternatives. Especially if
you ever need to update your structure. (Don't know about you, but
after about the third time, I'd be ready to throw in the towel!)
Having that many tables to manage just seems like a TON of unnecessary
work.
Nov 13 '05 #6
thanks all for your time. Not trying to be rude here, but i still
think my design will be the best i can think off for my situation. Ok
let me explain the situation.

why did i not have them in a single table ? coz at the end of the
shift, the person who would be entering information don't have to
create a single record for each and every department. i have a main
form with the date,shift number & an autonumber as an id, with 44
subforms on a tab control. so all he has to do is type in date & shift
number once & then add information about different departments.

Is that the only reason i had made 44 tables?? no!....another reason
why having 44 tables will be well suited is, a report needs to be
generated in a particular manner & with the way the departments are
named its impossible to sort them. not to mention, printing
performance charts would be another impossible thing to do if they
cannot be sorted.

This database will be used by people who are not really good on
computers. I can think of so many other reasons that would make it
more difficult for them while entering data if it was on a single
table.

I hope i explained it well. Please let me know what you guys think.

Note: I figured out a way to perform the calculation without adding
all the 44 tables in the query. it will be 10 tables at the max. hence
"query too complex" error is solved.

But now you guys make me worry about my design...lol
Nov 13 '05 #7
Matt,
I'll bet you a dollar I could meet your user's requirements by using one
table and a bunch of views. I'll bet another dollar that my way would be
cheaper to maintain, faster, and based on those two improvements, better
than yours. If you are doing charts I still think you should look at the
pivot table view added in Access 2002. It's a very cool feature that works
a lot like the similarly named feature in Excel.

"Matt" <yo*******@yaho o.com> wrote in message
news:fe******** *************** ***@posting.goo gle.com...
thanks all for your time. Not trying to be rude here, but i still
think my design will be the best i can think off for my situation. Ok
let me explain the situation.

why did i not have them in a single table ? coz at the end of the
shift, the person who would be entering information don't have to
create a single record for each and every department. i have a main
form with the date,shift number & an autonumber as an id, with 44
subforms on a tab control. so all he has to do is type in date & shift
number once & then add information about different departments.

Is that the only reason i had made 44 tables?? no!....another reason
why having 44 tables will be well suited is, a report needs to be
generated in a particular manner & with the way the departments are
named its impossible to sort them. not to mention, printing
performance charts would be another impossible thing to do if they
cannot be sorted.

This database will be used by people who are not really good on
computers. I can think of so many other reasons that would make it
more difficult for them while entering data if it was on a single
table.

I hope i explained it well. Please let me know what you guys think.

Note: I figured out a way to perform the calculation without adding
all the 44 tables in the query. it will be 10 tables at the max. hence
"query too complex" error is solved.

But now you guys make me worry about my design...lol

Nov 13 '05 #8
yo*******@yahoo .com (Matt) wrote in message news:<fe******* *************** ****@posting.go ogle.com>...
thanks all for your time. Not trying to be rude here, but i still
think my design will be the best i can think off for my situation. Ok
let me explain the situation.

why did i not have them in a single table ? coz at the end of the
shift, the person who would be entering information don't have to
create a single record for each and every department. i have a main
form with the date,shift number & an autonumber as an id, with 44
subforms on a tab control. so all he has to do is type in date & shift
number once & then add information about different departments.
Is your concern re-entering the date and shift number? Get the code
from accessweb to grab the value from the previous record.
Is that the only reason i had made 44 tables?? no!....another reason
why having 44 tables will be well suited is, a report needs to be
generated in a particular manner & with the way the departments are
named its impossible to sort them. not to mention, printing
performance charts would be another impossible thing to do if they
cannot be sorted.
So create a table of departments and put some kind of sort key in
there... and a grouping key if you want... The users wouldn't
necessarily have to see any of this if you created a good interface...
This database will be used by people who are not really good on
computers. I can think of so many other reasons that would make it
more difficult for them while entering data if it was on a single
table.

But now you guys make me worry about my design...lol


I would be worried about a design like that. Seems like an
*incredible* amount of work for a job that should be almost trivial,
given a well-designed database. If you're worried about
outputs/reports, then could you give a few examples that you don't
think can be solved with a query on a few tables?
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
8983
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with databases... A google search takes me to various forums where I am forced to sign up before I can read any answers. Interesting note here is that the guy in the office next
9
18590
by: Mike Bridge | last post by:
I am using MS Access 2003, and I typed in a query in SQL view which was working fine. Now when I open it, it gives me the error "Join expression not supported". Apparently, Access "fixed" it for me. Unfortunately, I can't correct it because when I click "OK", the window disappears. I don't see any way to open the query directly in SQL view, and both the Design and Data views exit as soon as I close the error dialog. Is there any way...
12
1589
by: Russ | last post by:
I tried the following: >>> x = complex(4) >>> y = x >>> y *= 2 >>> print x, y (4+0j) (8+0j) But when I tried the same thing with my own class in place of "complex" above, I found that both x and y were doubled. I'd like to
4
15768
by: Mike D | last post by:
OS: Windows XP Professional Microsoft Access 2003 I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp table (on the keys) they are replaced/updated by the new data in the temp table. the vba code i am running is below: Dim mySQL As String
1
1768
by: wayniac | last post by:
I am having a problem when I try and remove several fields from the lookup. I have many fields, and the error I am getting is "Expression is too complex", is there anyway of going around this or turning an option off so I may proceed further. From what I have researched, this error is because I have too many fields be filtered in the onload property of the form. Thank you
3
3145
by: geebanga88 | last post by:
Hi i am using oracle sql developer and am making a sub query for a question. The question states: "Find Name of patients who have been treated by Dr Brian or who have had an Extended Consultation. The following is the relational schema for the database: Patient (PatientID, FamilyName, GivenName) Doctor (ProviderNo, Name) Item (ItemNo, Description, Fee) Account (AccountNo, ProviderNo, PatientID, Date) AccountLine (AccountNo,...
1
2173
by: Rahul Babbar | last post by:
Hi, I ran the scripts in a file from Command Line Processor and it gave the error for all the constraints being added, but not the indexes being added. For a simple statement like Alter table A add constraint A_const foreign key(col1) references B(col2);
8
4450
geolemon
by: geolemon | last post by:
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now. Arg. I used to be a DBA in large DB2 and SQL Server environments, but I'm a little rusty, plus I'm very new to Access. I am trying to perform a calculation based on information in a few tables, and use the result to update another. Should be simple, here's what counts from the tables...
1
1878
cori25
by: cori25 | last post by:
Paycode': IIf(="01","Meeting",IIf(="02","Training",IIf(="04","Special Project",IIf(="05","QC",IIf(="06","MDU",IIf(="07","UG",IIf(="08","2nd Man",IIf(="09","Sr Tech Other",IIf(="10-1","Loan Out to Nassau",IIf(="10-2","Loan Out to Suffolk",IIf(="10-3","Loan Out to CTHV",IIf(="10-4","Loan Out to Bronx",IIf(="10-5","Loan Out to Brooklyn",IIf(="10-6","Loan Out to NJ North",IIf(="10-7","Loan Out to NJ...
0
9832
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11245
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10840
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10469
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5859
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6066
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4684
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4281
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3293
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.