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

Question about the QEB

P: n/a
Hi folks,

Lots of questions below. Thanks in advance for your help!

1. I go into the query expression builder, I show a lot (like 20 or 30)
tables and queries, I create inner joins and left joins and so on, and build
what winds up looking like a very complicated-looking QEB layout. I have
been very deliberate and careful about structuring the tables and queries in
the upper portion of the QEB so that it's easy to see how they all join and
relate to one another. I test the results, everything's working fine, I
close the QEB. A few minutes later I decide I need to add one more field,
so I open the query again. Then I look at the upper portion, and I cringe in
horror at the tangled, unstructured mess of tables and queries layed out in
in seemingly random order. Is there any way to make the QEB "remember" the
latest layout, instead of presenting me with a garbeled mess? I suspect
there is not, because all it's storing is the SQL. Any ideas? I hate having
to spend minutes dragging and moving tables every time I open a complicated
query.

2. I have a few (like 3 or 4) well-designed tables (ie. they've got primary
keys, relationships, normalized). One of the tables has a lot (like over
100,000) records. I want to build a form that allows the user to view and
edit data after drilling down to the dozen or so he's interested in. Now, in
general, I think there are a lot of different ways to achieve this. I can
create one main form that contains subforms inside subforms, and use Child
and Master links to keep them all synchronized. I've found that this
approach works, but it really drags, it responds very slowly. Another
approach is to use subforms without Child and Master links, and instead use
a function as criterea inside each subform's recordsource, where the
function assignment is tied to a field on the master form. This is similar
to using master/child links, but seems to be quicker for some reason. The
problem with it is that I have to code lots of .Requeries in VBA to update
all the downstream subforms. Any opinions on what the fastest approach is?
My goal is for the end user to get fast-as-possible response when he's
drilling down to the data he wants.

OK, more later.
Gary

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Gary,
20 or 30 tables contributing to a result set? Dude, you've got relational
disease. It's tough to do so on a production system but you might want to
rethink the schema and figure out what your measured facts are, the
dimensions by which those facts are measured, etc. and collapse that monster
into a half-dozen or so compiled data warehouse tables so queries are more
straightforward and performance improves.
Faster response on drill-down. Stored procedures, man. Build your database
in the supplied copy of SQL Server Desktop Edition that comes with every
version of Access from 2K onward and use stored procedures. Way faster.
Have the stored procedures create temp tables with the drilled-down result
sets to bind your forms to. Your users will thank you and think you are
brilliant.

"Gary Wachs" <gw**@cox.net> wrote in message
news:AWNZb.19607$o52.7295@fed1read02...
Hi folks,

Lots of questions below. Thanks in advance for your help!

1. I go into the query expression builder, I show a lot (like 20 or 30)
tables and queries, I create inner joins and left joins and so on, and build what winds up looking like a very complicated-looking QEB layout. I have
been very deliberate and careful about structuring the tables and queries in the upper portion of the QEB so that it's easy to see how they all join and relate to one another. I test the results, everything's working fine, I
close the QEB. A few minutes later I decide I need to add one more field,
so I open the query again. Then I look at the upper portion, and I cringe in horror at the tangled, unstructured mess of tables and queries layed out in in seemingly random order. Is there any way to make the QEB "remember" the
latest layout, instead of presenting me with a garbeled mess? I suspect
there is not, because all it's storing is the SQL. Any ideas? I hate having to spend minutes dragging and moving tables every time I open a complicated query.

2. I have a few (like 3 or 4) well-designed tables (ie. they've got primary keys, relationships, normalized). One of the tables has a lot (like over
100,000) records. I want to build a form that allows the user to view and
edit data after drilling down to the dozen or so he's interested in. Now, in general, I think there are a lot of different ways to achieve this. I can
create one main form that contains subforms inside subforms, and use Child
and Master links to keep them all synchronized. I've found that this
approach works, but it really drags, it responds very slowly. Another
approach is to use subforms without Child and Master links, and instead use a function as criterea inside each subform's recordsource, where the
function assignment is tied to a field on the master form. This is similar
to using master/child links, but seems to be quicker for some reason. The
problem with it is that I have to code lots of .Requeries in VBA to update
all the downstream subforms. Any opinions on what the fastest approach is?
My goal is for the end user to get fast-as-possible response when he's
drilling down to the data he wants.

OK, more later.
Gary

Nov 12 '05 #2

P: n/a
Gary Wachs wrote:
Hi folks,

Lots of questions below. Thanks in advance for your help!

1. I go into the query expression builder, I show a lot (like 20 or 30)
tables and queries, I create inner joins and left joins and so on, and build
what winds up looking like a very complicated-looking QEB layout. I have
been very deliberate and careful about structuring the tables and queries in
the upper portion of the QEB so that it's easy to see how they all join and
relate to one another. I test the results, everything's working fine, I
close the QEB. A few minutes later I decide I need to add one more field,
so I open the query again. Then I look at the upper portion, and I cringe in
horror at the tangled, unstructured mess of tables and queries layed out in
in seemingly random order. Is there any way to make the QEB "remember" the
latest layout, instead of presenting me with a garbeled mess? I suspect
there is not, because all it's storing is the SQL. Any ideas? I hate having
to spend minutes dragging and moving tables every time I open a complicated
query.
I don't know. 20-30 tables for a query sounds like a mess to me. I'd hate to
be the one that needs to debug it...or as you say add or delete a field. I
would see if making the window that displays the table larger, less space to the
field data. Also, adding the tables in the order of sequence may help. IOW,
start off with your master table, add subsequent child tables later.in a big
window.

I perhaps would create queries to hold datasets together and then end up with 3
or 4 tables in the QBE.instead of your mess.

But I don't know your system, Having 20 or 30 tables for a function may be
normal for your app. Maybe everything is so unique that you can't combine
anything together via subqueries and need to keep the system extremely complex
and build in job security.
2. I have a few (like 3 or 4) well-designed tables (ie. they've got primary
keys, relationships, normalized). One of the tables has a lot (like over
100,000) records. I want to build a form that allows the user to view and
edit data after drilling down to the dozen or so he's interested in. Now, in
general, I think there are a lot of different ways to achieve this. I can
create one main form that contains subforms inside subforms, and use Child
and Master links to keep them all synchronized. I've found that this
approach works, but it really drags, it responds very slowly. Another
approach is to use subforms without Child and Master links, and instead use
a function as criterea inside each subform's recordsource, where the
function assignment is tied to a field on the master form. This is similar
to using master/child links, but seems to be quicker for some reason. The
problem with it is that I have to code lots of .Requeries in VBA to update
all the downstream subforms. Any opinions on what the fastest approach is?
My goal is for the end user to get fast-as-possible response when he's
drilling down to the data he wants.
I might set up a row of combo boxes. The second and subsequent combos data are
predicated on displaying data from the previous combo. Ex:
Private Sub Combo1_AfterUpdate
Combo2.requery
Combo2_afterUpdate
endsub
Private Sub Combo2_AfterUpdate
Combo3.requery
Combo3_afterUpdate
endsub
Private Sub Combo3_AfterUpdate
Combo4.requery
Combo4_afterUpdate
endsub
Private Sub Combo4_AfterUpdate
Combo5.requery
endsub
The rowsource has the foreign key of the prior one with criteria pointing to
combo1. For Combo2 Ex:
Forms!MyForm!Combo1

I would attempt to follow the 3-click rule. If you can't get to where you want
in 3 clicks or less then redesign.



OK, more later.

Gary


Nov 12 '05 #3

P: n/a
Thanks for the responses guys, I'll look into those ideas.

I think I figured it out:

1. Query objects remember the exact layout in the QEB. SQL recordsources in
forms and reports don't, because they are only stored as text.. So, for the
big complex queries, make them standalone queries.

2. My final conclusion after spending many hours coding this front-end many
different ways is that using Public variables and Function calls inside
recordsource SQL and inside queries is the easiest, fastest and most
reliable way to drill down in a form or in a subform.

"Gary Wachs" <gw**@cox.net> wrote in message
news:AWNZb.19607$o52.7295@fed1read02...
Hi folks,

Lots of questions below. Thanks in advance for your help!

1. I go into the query expression builder, I show a lot (like 20 or 30)
tables and queries, I create inner joins and left joins and so on, and build what winds up looking like a very complicated-looking QEB layout. I have
been very deliberate and careful about structuring the tables and queries in the upper portion of the QEB so that it's easy to see how they all join and relate to one another. I test the results, everything's working fine, I
close the QEB. A few minutes later I decide I need to add one more field,
so I open the query again. Then I look at the upper portion, and I cringe in horror at the tangled, unstructured mess of tables and queries layed out in in seemingly random order. Is there any way to make the QEB "remember" the
latest layout, instead of presenting me with a garbeled mess? I suspect
there is not, because all it's storing is the SQL. Any ideas? I hate having to spend minutes dragging and moving tables every time I open a complicated query.

2. I have a few (like 3 or 4) well-designed tables (ie. they've got primary keys, relationships, normalized). One of the tables has a lot (like over
100,000) records. I want to build a form that allows the user to view and
edit data after drilling down to the dozen or so he's interested in. Now, in general, I think there are a lot of different ways to achieve this. I can
create one main form that contains subforms inside subforms, and use Child
and Master links to keep them all synchronized. I've found that this
approach works, but it really drags, it responds very slowly. Another
approach is to use subforms without Child and Master links, and instead use a function as criterea inside each subform's recordsource, where the
function assignment is tied to a field on the master form. This is similar
to using master/child links, but seems to be quicker for some reason. The
problem with it is that I have to code lots of .Requeries in VBA to update
all the downstream subforms. Any opinions on what the fastest approach is?
My goal is for the end user to get fast-as-possible response when he's
drilling down to the data he wants.

OK, more later.
Gary

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.