Wide Tables

[ann]

You've been having some good discussion on wide tables.
I'm in a project now where I need a 900-field table, I
think, but I hope I'm missing something. I've never had a
design problem like this.

The database must generate a special form with 900 unique
(unrelated) variables in pre-defined places on a form.
When I build a form - I can only specify one table as the
record source, right? (It has to be a table, not a query,
because there is also user input to the table.)

Is there a way to make a set of linked tables be a form
record source? I've tried some things, and I seem to
recall there's a best way to do this. Thanks for any
help.

 

[Douglas J. Steele]

You can use queries as recordsources: as long as the joins are done properly
so that the queries are updatable, your users will be able to update the
data.

However, even queries are limited to 255 fields in Access. As well, there's
a limit of 754 controls and sections you can add over the lifetime of the
form or report, so you won't be able to have a form with 900 fields on it.

I suspect you may need to use subforms, rather than trying to have a single
form.

Make sure your data model is correct: don't base your tables on your forms.

 

[Paul Overway]

The recordsource for the form can be a query. Nevertheless, there is a 256
column limit for tables AND queries. So, you're out of luck there. You
could possibly break it up into separate tables with less than 256 columns
and a 1-1 relationship between the tables. However, there is also a limit
to the number of controls you can place on a form...and I think you'll hit
it unless you use subforms.

Still, you probably missed something...900 fields in a table is very
unusual. Is the data normalized?

 

[Paul Overway]

Correction...255 field limit per table or query

 

[John Vinson]

You've been having some good discussion on wide tables.
I'm in a project now where I need a 900-field table, I
think, but I hope I'm missing something. I've never had a
design problem like this.

If you're deriving your table structure from the appearance of a form
- or, as I suspect, from a Report - you're pretty much guaranteed to
get a badly non-normal design.

The database must generate a special form with 900 unique
(unrelated) variables in pre-defined places on a form.

Is this a *paper* form? or an Access Form, an onscreen tool to view
and edit data? I'd rebel violently if I were presented with 900
controls to edit on a single computer screen!!!

When I build a form - I can only specify one table as the
record source, right?
Wrong.

(It has to be a table, not a query,
because there is also user input to the table.)

That's assuming that a Query cannot be updated; that's an incorrect
assumption. Queries CAN be updated.

Is there a way to make a set of linked tables be a form
record source? I've tried some things, and I seem to
recall there's a best way to do this. Thanks for any
help.

I'd like to get the problem clarified somewhat. You can use multiple
Subforms on a form; you can use multiple Tab Pages on a form; you can
fill out a *paper* Form using a Report based on a query joining
multiple tables.

You're almost always better off designing your table structures *based
on the logic of the data*; laying them out on paper or on the screen
should come LAST, not first, and it will be much easier with a
properly structured set of tables!

 

[TC]

You've been having some good discussion on wide tables.
I'm in a project now where I need a 900-field table, I
think, but I hope I'm missing something. I've never had a
design problem like this.

The database must generate a special form with 900 unique
(unrelated) variables in pre-defined places on a form.
When I build a form - I can only specify one table as the
record source, right? (It has to be a table, not a query,
because there is also user input to the table.)

Is there a way to make a set of linked tables be a form
record source? I've tried some things, and I seem to
recall there's a best way to do this. Thanks for any
help.

 

[TC]

You need to read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC