Q. Is 19 fields too much for a query ?

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

Jim Jones

Hi,

In Access 2K, I'm trying something new.

So I made a query to include 19 fields, which comes from 4 different
tables. Keep in mind, I plan to make a form out of this.

Is this too many fields? If I wanted to break it down, does it make
sense to try to make this form by breaking the one big query into 2 or
more queries ? I hope I don't have to.

(this is not going to be too critical, and is certainly not going to
be the main screen on the porject, or anything. Just looking at
options is all).

* * * * *
On ANOTHER query problem, I have a command button to pop up a form,
based on a DUPLICATES query. It comes up fine, and gives me the
counts in form view, but I can't make it come up in datasheet vew as
default, which is really what I want, and which I believe I've set
everything to force a datasheet view, in my design.
* * * * *

Please respond for either one or both of the above.

Thanks,
Jim
 
Hi,

In Access 2K, I'm trying something new.

So I made a query to include 19 fields, which comes from 4 different
tables. Keep in mind, I plan to make a form out of this.
Click to expand...

There shouldn't be any problem making the query or displaying the
returned data in a form. Make sure that all the relationships are
correct between the tables.
Is this too many fields? If I wanted to break it down, does it make
sense to try to make this form by breaking the one big query into 2 or
more queries ? I hope I don't have to.

(this is not going to be too critical, and is certainly not going to
be the main screen on the porject, or anything. Just looking at
options is all).

* * * * *
On ANOTHER query problem, I have a command button to pop up a form,
based on a DUPLICATES query. It comes up fine, and gives me the
counts in form view, but I can't make it come up in datasheet vew as
default, which is really what I want, and which I believe I've set
everything to force a datasheet view, in my design.
* * * * *
Click to expand...

Regardless of the form's Default View setting, if the form is being
opened via a command button on another form, you must explicitly open
it in form datasheet view:
DoCmd.OpenForm "FormName", acFormDS
 
Hi,

In Access 2K, I'm trying something new.

So I made a query to include 19 fields, which comes from 4 different
tables. Keep in mind, I plan to make a form out of this.

Is this too many fields? If I wanted to break it down, does it make
sense to try to make this form by breaking the one big query into 2 or
more queries ? I hope I don't have to.
Click to expand...

You're limited to 255 fields in a Query, so no, I'd say it's not
anywhere near too many.

However, it may well be too many *tables*. A four-table query is not
likely to be updateable! If you're just using this form to display
data, it may be OK (though you may see some duplicates); if you plan
to update the tables you should consider having a Form with Subforms.
(this is not going to be too critical, and is certainly not going to
be the main screen on the porject, or anything. Just looking at
options is all).

* * * * *
On ANOTHER query problem, I have a command button to pop up a form,
based on a DUPLICATES query. It comes up fine, and gives me the
counts in form view, but I can't make it come up in datasheet vew as
default, which is really what I want, and which I believe I've set
everything to force a datasheet view, in my design.
* * * * *
Click to expand...

There's an optional parameter in the OpenForm method to specify the
display mode:

DoCmd.OpenForm strDocument, acFormDS

will open it in Datasheet view.
 
Jim,

To add to Fred's comments, be aware that, depending on various factors
of the structure of the data and the relationships between the tables
(not the number of fields), your query may or may not be "updateable".
So, if the purpose is to display the query's data on a form, this will
be fine, as Fred said. If your intention is to enter/edit data on the
form, this may not be possible.
 
You're limited to 255 fields in a Query, so no, I'd say it's not
anywhere near too many.

However, it may well be too many *tables*. A four-table query is not
likely to be updateable!
Click to expand...

You all are right (unfortunately).
It won't let me update, though it is in a format that I think the user
will like much better than my custom forms.
If figures: I try one thing, then I have trouble with the one thing.
I try another and it seems ideal until I try something, and I'm so
close . . . .
If you're just using this form to display
data, it may be OK (though you may see some duplicates); if you plan
to update the tables you should consider having a Form with Subforms.
Click to expand...

John, it is ideal for display. But IT IS in form / subform view.

See, I tried to take the thing I'd been working on :

Mainform , subform and subform within subform, and tried

Main form and 2 subforms on the same level this time around.

Well, I invoked the query wizard again, and it seems the best I 'll be
able to get is where the wizard makes the main form and the button
will pop up the other form, with the embedded subform for the 3 or 4th
table.

Unless you know how to make the recordset updateable given my previous
scenario here.

*incidentally, the original way which is still in tact, which is
mainform/subform and subform within subform, works, and is updateable,
but I'm not too crazy about the design for my user.

Thanks,
Jim
 
Jim Jones said:
On Fri, 12 Mar 2004 11:05:45 -0700, John Vinson
If you're just using this form to display
data, it may be OK (though you may see some duplicates); if you plan
to update the tables you should consider having a Form with Subforms.
Click to expand...

John, it is ideal for display. But IT IS in form / subform view.

See, I tried to take the thing I'd been working on :
Mainform , subform and subform within subform, and tried
Main form and 2 subforms on the same level this time around. [snip]

*incidentally, the original way which is still in tact, which is
mainform/subform and subform within subform, works, and is updateable,
but I'm not too crazy about the design for my user.
Click to expand...

Changing from...

Form - Sub - Sub (within the sub)
to
Form - Sub - Sub (both on the main)

....requires you to do some extra work to keep the subs properly synchronized on
the form, but it should not require you to change the tables/queries that the
forms are bound to at all. Given that, you should be able to use the same
table/query structure that *was* updateable.
 
Jim Jones said:
On Fri, 12 Mar 2004 11:05:45 -0700, John Vinson
If you're just using this form to display
data, it may be OK (though you may see some duplicates); if you plan
to update the tables you should consider having a Form with Subforms.
Click to expand...

John, it is ideal for display. But IT IS in form / subform view.

See, I tried to take the thing I'd been working on :
Mainform , subform and subform within subform, and tried
Main form and 2 subforms on the same level this time around. [snip]

*incidentally, the original way which is still in tact, which is
mainform/subform and subform within subform, works, and is updateable,
but I'm not too crazy about the design for my user.
Click to expand...

Changing from...

Form - Sub - Sub (within the sub)
to
Form - Sub - Sub (both on the main)

...requires you to do some extra work to keep the subs properly synchronized on
the form, but it should not require you to change the tables/queries that the
forms are bound to at all. Given that, you should be able to use the same
table/query structure that *was* updateable.
Click to expand...

Hi,

I just want to point out, that I seem to run into that "4' table
maxiimum, regarding the query.

But, anyway, you indicated that I should change from Main, Subform,
sub within sub to Main form and 2 subforms ont he same plane.

And that's what I want to do as it is much easier to look at.

But, it's my second subform which won't update.

Why? because in between tables 2 and 3, there is a table whose purpose
is simply to hold a key, as I need that key to be a many side, on the
4th table (ServiceDetails), which is actually present as the second
subform on the form.

That 3rd table (service), which has the key "serviceticket" is not
anywhere on the form., but is in the query.

My question is : Is there REALLY a limit to which Access will work
with, regarding the "how many tables" scenario ?

Thanks,
Jim
 
Jim,

As mentioned earlier, the number of tables is not the issue. You can
have a query based on one table which is not updateable, and you can
have a query based on many tables which is updateable. The main factors
are to do with the types of relationships between the tables, and
whether there are aggregate functions involved. There is a very good
explanation in Access Help about this... type in "updatable" for
keyword, and then find the topic "When can I update data from a query".
 
Back
Top