Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    7

    Unanswered: Table Design Help

    I have a worker injury form here at work that has more than 255 trackable fields. Is there a way to expand the table design to allow more than 255 fields?

    The form has three numbers that identify it.
    1) Company Report Number
    2) Other company report number
    3) Incident Number

    One or all three of the above numbers could be listed on the form. So I've been using autonumber as a primary key.

    Since I couldn't put it all in one table, I broke the form into sections A thru I and created a table for each section.

    Now, the big question I have is, how do a I design a form to enter into all of the tables at the same time, without having to retype one or all three of those number into nine different tables (A thru I) and keep the tables all relational to be able to produce reports?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What about having a different table for storing answers:

    tblReport
    ----
    report_id
    report_date
    principal_name
    etc

    tblForm
    -----------
    form_id
    report_id
    etc

    tblQuestion
    ----------
    question_id
    short_question
    long_question

    tblFormQuestion
    ----------
    form_id
    question_id
    answer
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by 1966Lincoln
    .....Now, the big question I have is, how do a I design a form to enter into all of the tables at the same time, without having to retype one or all three of those number into nine different tables (A thru I) and keep the tables all relational to be able to produce reports?
    have a look at using sub tables as part of your form design

    I cannot believe that you genuinely have 255 columns for each and every accident report. Mind you considering the app is dealing with Health & Safety I suppose it could be a genuine requirement!

    Is the data design normalised?

    Is there an element of redundancy in your table design (eg columns 10..125 apply to incidents of type 1, 125..140 type 2 etc.....?

    just a word of caution Im pretty certain there is an inbuilt limit Access on query & report design. Queries are limited to 256 columns so Id expect that similar limit to be carried through on tables, reports & forms.

    if you need to have more than 255 columns in a table, and the data is already normalised then you could add another table with a one to one RI link allowing another 255 columns (+1 for the linkID)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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