Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max # of Fields in a Query (2003/SP1)

    I am wondering if there is a max number of fields that can be in a query. I have tried unsuccessfully to create a Form that incorporates 5 different tables, with over 300 fields total. I am wanting to use a Tab'd format to show each table's data associated to an individual. All tables are linked by the individuals name and I have forced referential integrity. I queried the Access help function and was brought to the following:

    Attribute Maximums
    Number of enforced relationships 32 per table minus the number of indexes that are on the table for fields or combinations of fields that are not involved in relationships
    Number of tables in a query 32
    Number of fields in a recordset 255
    Recordset size 1 gigabyte
    Sort limit 255 characters in one or more fields
    Number of levels of nested queries 50
    Number of characters in a cell in the query design grid 1,024
    Number of characters for a parameter in a parameter query 255
    Number of ANDs in a WHERE or HAVING clause 99
    Number of characters in an SQL statement approximately 64,000

    The problem that I am having is that when I create a query that incorporates all subject tables, create the Form, assign a Tab'd page to the Form and then populate the form with the fields, I switch from edit mode to view mode and nothing appears on the form. I switch back to edit mode and everything is back again. Does any of the above "maximums" mean anything in regards to this issue? NOTE: I did do a test form with only using a single table and it worked fine, so I know that there isn't any issue with Access itself. Any information on this would be great.

    Thanks,

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

    Re: Max # of Fields in a Query (2003/SP1)

    You can have at most 255 fields in a query, and a single record can be at most 2,000 bytes in size (except for memo and OLE fields)

  3. #3
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max # of Fields in a Query (2003/SP1)

    Thanks for the quick reply!

    That is what I figured, but wanted to make sure. Is there anyway to use more than one query on a Form or do I have to break things up across several Forms?

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

    Re: Max # of Fields in a Query (2003/SP1)

    One form can have only a single record source with at most 255 fields.
    You can use subforms. Each subform can have up to 255 fields in its record source.

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max # of Fields in a Query (2003/SP1)

    I have read about them, but never had the need to play with them. Looks like a good time to learn. Thank You!

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

    Re: Max # of Fields in a Query (2003/SP1)

    You'd have to use code to set the name in the linked tables. I would use a different setup, however, with a Personnel table containing only the personnel info, and an AutoNumber primary key, and a Training table with a separate record for each person - training event combination. This table would contain a number field that links to the primary key of the Personnel table, plus a field identifying the training event, plus - if necessary - a few fields containing info specific to the person - training event combination.
    This way, the number of training events per person is unlimited, but you need few fields.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max # of Fields in a Query (2003/SP1)

    Image resized by HansV - please don't post pictures over 640x480 pixels in size.

    Top,
    I'll post a screenshot of my unit's database. The way I have found that works best is the tabbed approach, with each tab containing a subform related to one (or two small) table(s). It let's you scroll through information quickly and easily. Also - if possible, try to use SSN as a primary key, you will find quickly that there are too many people with the same last name. My screenshot shows toggle buttons, however tabs will achieve the same result, (more effectively, I may add).
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  8. #8
    Lounger
    Join Date
    Jun 2003
    Location
    Pleasant Hill, Missouri, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max # of Fields in a Query (2003/SP1)

    OK...Sill having problems in designing this database and making each table link to each other inside a form. I'm sure this is probably a simple solution, but from all my reading I haven't been able to make it work.

    I need to be able to link well over 255 training events to each of my personnel. Because of the high number of fields I have created two test tables (Personnel & Ancillary) and am using Form/Sub-Form format for an input form. Personnel is primary and Ancillary is the Sub-Form. In each table I have a field called "Name" to try and link the two together using 1 to 1 referential integrity; however, when I pull up the form and input a name in the Personnel:Name field is is not automatically added to the Ancillary:Name field. How can I force this to happen so that I can maintain record integrity accross both these tables or is there a better way? Personnel:Name is the primary key in the primary table; Ancillary table does not have a primary key. Do I have to actually type the name in each table so that they can be linked? I was trying to only have to type in the name once on the main form and then "cascade" the addition to the other tables. I have attached a screen shot of the form that I am working with.

    Any help would be greatly appreciated.

Posting Permissions

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