Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table Limit? (2000 (SR-1))

    Is there a limit of fields in an Access table?
    I have reached a point where I am told there are too many fields and wonder if there is a way around this issue...
    There is always a way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Limit? (2000 (SR-1))

    A table or query in Access can have a maximum of 255 fields. If you're near that limit, it's time to reconsider your design.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Limit? (2000 (SR-1))

    I am near the limit and am not sure what consideration I should make in the design.

    The table is a "Master Table" for 285 retail locations in rows and all the Location, Operaional, Financial, Lease Related, and Merchandising data for each location in the columns. The primary key is on a Loc# and StoreName and all (almost) 255 fields are for each location.

    There are separate data entry forms for each of the categories so the regional and location persons can update the sections which is why I have not created a table for each section. When querying from 2 tables the "recordset is not updatable" error prevents the form from accepting entries.

    How would you attack this?
    There is always a way.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table Limit? (2000 (SR-1))

    In a different thread today, I advised against using two tables with a one-to-one relation, but if you really need that many fields, you could create two tables with a one-to-one relation on the primary key. A query based on those two tables ought to be updatable, but remember that you won't be able to include more than 255 fields in the query. You'd have to make sure to create a record in one table when the user creates a record in the other.

    Another possibility is to have one table with general info about the retail location, plus a table linked to it (one-to-many) on the primary key, with fields like ItemName, ItemDescription and ItemType, as well an ItemValue field for each data type you need, for instance ItemValueText, ItemValueNumeric, ItemValueDate, ItemValueBoolean asnd/or ItemValueCurrency. This will allow you to have an unlimited number of pseudo fields for each retail location - actually records in the linked table. You'd have to present these pseudo fields in a subform.

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

    Re: Table Limit? (2000 (SR-1))

    I would split data into two or more tables based on how frequently the data gets used. For example, Location probably gets updated pretty infrequently - maybe an occasional phone number change, but is probably used frequently. On the other hand, Merchandising data probably gets updated frequently, but there are many cases where you wouldn't be interested in that data. Splitting it into the 5 categories you mentioned would be a start, and if you put the Loc # into each of those tables, then any query will be updateable as long as there no more than two tables being used. If you get into three, it can get dicey, but in general as long as there are one-to-one relationships between the tables, even 4 or 5 table joins will still be updateable. Hope this all makes sense.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table Limit? (2000 (SR-1))

    Thanks.

    Your first idea works like a charm!
    There is always a way.

Posting Permissions

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