Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access, Please help me :( (Access 2002)

    Hello everyone, this my first post on the board. I am having a problem and i'm turning to you guys for your expertise. I am trying to make a Job Costing form for my work, however I have more than 255 fields. I am wondering how I can get all of my fields onto the same form. Any help would be great! If you need more information please ask and I will tell you as much as I can. This is the error that pops up for those who will find it helpful "The row you inserted in the grid exceeds the limit of 255 rows (fields) for a table or 1,000 rows (actions) for a macro"

    Thanks again.

    Leo

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

    Re: Access, Please help me :( (Access 2002)

    Access tables and queries have a "hard" limit of 255 fields. If you really need to, you could create two tables linked on a common ID field, and create two forms, one for each table. These forms could be placed as subforms on a main form, again linked by the ID field. But this is very artificial, and you're bound to have problems with this setup. You should ask yourself if it is really necessary to have more than 255 fields. Not only do you run into technical problems, but it isn't user-friendly at all. There should be a way of organizing the data differently, for example by splitting the information into a main record with multiple subrecords. If you provide information about what all these fields should contain, other Loungers may be able to give advice.

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access, Please help me :( (Access 2002)

    Thanks for your quick response. Ok let me see if I can give you a bit more detail about what i'm trying to do. We install mini-blinds, curtains, etc etc. What I did was make a bunch of tables which contained data for the different things we sell, IE track, blinds, parts, curtains, etc etc etc. So in this form what I'm trying to accomplish is to have a form where we pick the parts we need (varies by each job). In my table which contains my fields I have things like Blind Type, Blind Type2, Blind Type 3 (this one for example goes up to 20) and I do that because its my understanding that each combo drop down box (which is what those are linked too (another table that has info on the blinds)) requires its own unique field in the table to stay independant from the others. So you see that by the time I get down to the curtains, and accessories (accessories 1 2 3 etc ) I run out of 255 table relatively easy. I may be doing it the wrong way, I am open to suggesions of course.

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

    Re: Access, Please help me :( (Access 2002)

    What you describe looks like a many-to-many relationship between 'jobs' and 'parts' - one job may require a variable number of parts, and a part can be used in multiple jobs. The "standard" way to handle this is to create three tables:

    - A 'jobs' table that contains general information about a job; each record is identified by a unique JobID (an AutoNumber field is ideal for this).
    - A 'parts' table that contains general information about parts; each part is identified by a unique PartID (again, an AutoNumber field does the job).
    - An intermediate 'job_parts' table that contains info about which part(s) are used for each job. Each job-part is a separate record containing a unique combination of JobID and PartID (number fields here, not AutoNumber fields).

    By using this structure, you can list the parts for a specific job below each other, instead of next to each other. You would create a main form based on the 'jobs' table, and a subform based on a query that involves the intermediary table and the 'parts' table

    I have attached a very simple example of such a structure; instead of jobs and parts it is about persons and hobbies, but the basic idea is the same. Take a look at the design of the tables, and at the Relationships window. Also look at the design of the main form and subform, and how they are linked. I hope this demo will give you an idea how to proceed.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Oct 2003
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access, Please help me :( (Access 2002)

    Dangit. I was really hoping to not have to do that. Seems like that's the only way to get around this. Back to the drawing board!

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

    Re: Access, Please help me :( (Access 2002)

    It isn't a way of "getting around it", it's an approach that takes advantage of the relational capabilities of Access and serves you better in the long run. There's really no point is using Access as if it were a gigantic spreadsheet, and it would be awful to maintain. Relational design is only confusing at first. Once you get the hang of it, it becomes far easier than building spreadsheets. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Charlotte

Posting Permissions

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