Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Evansville, Indiana, USA
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Maximum number of fields? (2000)

    What is the maximum number of fields one table can have? I am trying to set up a table that needs 350 fields.
    Maybe someone will have a better way of doing this, here is what I am trying to do:
    I have a form with a grid on it. It looks exactly like an excel spreadsheet. Each cell is a textbox. The user can input data into any one of the text boxes anytime, but not all are required. The data they enter needs to be saved to a table, and then retrieved when they go to re-input data. Is there a better way of doing this other than setting each text box to a field in the table?
    Thanks,
    BMZ

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Maximum number of fields? (2000)

    Your limit is 255 fields in both Access and SQL Server. In fact we have a rule of thumb that says if a table approaches 100 fields, we presume the database is not properly normalized and start redesigning tables. FYI, the maximums in Access can be found in Help by searching for "specifications" - good luck.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Maximum number of fields? (2000)

    If you want something that looks like an Excel spreadsheet, you would be better of using Excel. Any flat table such as you describe is a waste of relational database capability, so there really isn't much point in doing it in Access, even if it doesn't exceed the field limits.
    Charlotte

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Maximum number of fields? (2000)

    Charlotte is correct - but note that Excel also only allows 255 columns, so you would have to use two rows to hod 350 fields.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Maximum number of fields? (2000)

    My expectation is that you really need to understand relational database design. Excel is not a relational database, it is what we call a "flat-file"; essentially because it only 2 dimensional (rows and columns).

    Access, like other relational databases, creates a multidimensional view of the data. However, this depends on you understanding the concepts of database normalization. For example, you might have several "phone" fields for each customer in your excel spreadsheet; home phone, work phone, fax, cell phone,etc. In a relational database, you would have NO phone fields in your customer record. Instead, you would create a separate phones table that might have 3 fields in it: customerNo, PhoneNo, and PhoneType. There could then be an unlimited number of phone records per customer.

    On your Access form, you handle this by using subforms.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •