"Query is too complex" error

[Matt]

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.

[Pieter Linden]

Is there a reason you have 44 1-to-1 relationships? That sounds odd in itself.

[Matt]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. 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.

[Alan Webb]

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*******@yahoo.com> wrote in message
news:fe**************************@posting.google.c om...

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.

[Alan Webb]

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*******@yahoo.com> wrote in message
news:fe**************************@posting.google.c om...

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. 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.

[Pieter Linden]

yo*******@yahoo.com (Matt) wrote in message news:<fe**************************@posting.google. com>...

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. 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.

[Matt]

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

[Alan Webb]

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*******@yahoo.com> wrote in message
news:fe**************************@posting.google.c om...

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

[Pieter Linden]

yo*******@yahoo.com (Matt) wrote in message news:<fe**************************@posting.google. 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?