Fields in a table (Max Amount allowable)

[Patentinv]

What is the maximum amount of fields/columns a table can hold in access 2003?

And what do you do if you have a form and its record source is a table that has run out of fields/columns?

If I remember right in access 97 it was 256 or 257 fields/columns was the max allowed. And it seemed to me it was quite common to run out of fields.

I'm currently waiting for my access 2003 upgrade disk to arrive and I'm
anticipating issues that might occur.

Thanks--Any help is much appreciated.

 

[MichaelRed]

actual limit (even w/ Ms. A. ver '03) remains at 255. But it includes every treference to every "field" - including the use as an index, as part of a calculated field, as an argument to a procedure, as a sort field and AFAIK even it it us used as an aggregate expression. (I'm sure there are others, but the mind slows with both bogglness and age).




MichaelRed

 

[dhookom]

Any table with more than about 25 fields raises a red flag about normalization.

Would you consider sharing why you think you need to push the 255 field limit?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Patentinv]

I've personally never designed a database. I'm actually a cne in novell networking and have worked around db designers.

I actually had one of them design me a db for my business in access 97 quite a few yars ago. I kind of watched and learned as much as possible. I'm trying to re-make it in access 2003 by my self.

I have a primary_Master form that is filled with combo and text boxes and its record source is called the Prim_master table and it is almost full w/hardly any fields left and I want to add more combo boxes and text boxes to this form once I re-make the db.

Doesn't each form have only 1 table as its record source. And if not how do you give it more than 1 record source.

The way I see it, I may have to create more than 1 form there by separating the program into multiple form. So I can
get more fields.

If you have a combo or text box on a form doesn't the field
that will be in ts control source have to be in the forms record sources table?

Thanks-- Any suggestions will be greatly appreciated.

 

[dhookom]

You haven't stated why you think you need so many fields.

What kind of information are you storing that requires so many fields?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Patentinv]

I'm just looking at the access 97 db I have in front of me that hasll these fields.

The db is used to calculate bids for construction company's
It includes all materials associated with firguring a price.
Also Customer info.
Quite a large amount of combo and text boxes.

 

[dhookom]

I would set up the tables so that each "material" created a record rather than a field in a table. Consider looking at the Northwind with orders and order details.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Patentinv]

O.K. lets sat i do make a table called materials and list each material in it which will then create a record as you stated.
Linking them by primary and foreign keys.

Now lets say there's a combo box on a form that doesn't have the record source of the materials table. It has a record source of the primary bid table. And I need to use a field in the materials table to give this combos box its control source. How do I accomplish this?

 

[dhookom]

Look at Northwind to see how products are selected in the order details form/subform. If this doesn't meet your needs, come back and tell us exactly why it doesn't.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

Many of "us" will agree with Duane -- when some one comments on the limits of 255 fields for a table, the first thought is if the database is "normalized".

The most common type of database that runs into the 255 field limit is a questionaire / survey / check list, and a database dealing with dates / days. Example, payroll for Sunday to Saturday.

As you "watched and learned", and by reviewing the Northwind datbase, you may wonder why certain things are done a specific way. I also wonder if the original Access97 database designed for you was designed properly.

Some references to consider reading...
Fundamentals of Relational Database Design by Paul Litwin
Download document from Jeramy's site
Read on-line (HTML) at Rudy's site

Micro$oft's answer...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Paul Litwin article is referenced a lot -- easy to read, makes a lot of sense and addresses the technical stuff.

Richard