Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19

    Unanswered: Chnages in Access 2003

    I have many problems in Access2000 with the amount of fields a table can hold. Has this been increased in 2003?

    I also have to put large amounts of characers in one field. I use a memo field at present but this can even struggle with the amount of characters. Has this been increased in 2003?

    Thanks

    James

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    Normalization

    If you have a table with that many columns you need to think about normalizing or splitting your table up.

    Secondly, if you are approacing the maximum number of characters in a Memo box you can always store the information as an OLE object, this will allow you maximum size up to 1GB. However a memo holds 64k worth of data, that is A LOT of information.

    I am thinking your DB may have problems greather than the number of fields and space for data that wont be resolved with MS Access 2k3

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    James,

    On the face of it, I have to agree w/mjweyland.

    In the situation you are describing, the burned of proof is yours to show that this kind of database design is justified.

    What are you doing ?

  4. #4
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    I have no problem with the design of the database I can split the tables no problem. The trouble I am having is when I have to send the data to clients who have requirements for lots of codes in one field. I.e a string of text in a field such as F009#F987#F654#F432 and so on?

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    F009#F987#F654#F432

    Are these actually separate codes with a '#' sign as a delimiter ?

  6. #6
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    Yes they are.

    These are all in one field. The file itselft is just comma delimeted.

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You need to familiarize yourself with the basic concepts of relational database design.

    When you have a field that can have multiple values, those values should be stored in a separate table and associated to the main table with a common piece of data (a unique index).

    EXACTLY, what are you trying to do. I want to try and help you but we are not getting a full picture here.

  8. #8
    Join Date
    Jan 2004
    Location
    Northamptonshire
    Posts
    19
    The design of the database has nothing to do with this. The values are stored within a table seperate or not it seems irrelevant to me.

    An example.

    A field in the table called Question1 has a value of -1 when the tick box on the form has been ticked. Say we have 30 questions. Each box is ticked. So I have 30 fields of -1's. I then have to change the -1's to relevant codes for my client. In question1 the -1 changes to F098, The -1 in question2 changes to F786 and so on.

    I then have to put all these values in one field seperated by #. I do this via an update query.

    So I then achieve F098#F786#F786 and so on. My problem is that I dont only have 30 questions some times it is hundreds and the memo field is not big enough.

    I hope this helps

    Cheers for the feedback.

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Why do you have to put them in one field ?

    Is it because you will be transmitting these codes in some file ?

    If so, there are other ways to accomplish this than recreating original data into a transformed copy and saving both.

  10. #10
    Join Date
    Dec 2003
    Posts
    268

    Questions

    I hve developed something similar to that here in my office and YES those should be split up to be kept in a different table.

    tblQuestion (QID, Text)
    tblQuestionOptions(OptionID, QID)
    tblResponses(RespID, OptionID, Response )


    With a simple table design like this you can eliminate the number of columns and the size of the memo fields. You could have a survey with 1million responses taken by 100 people and still not reach critical mass on an AccessDB. You need to think about how your data is organized and get an understanding of 3NF.

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Yeah, I'm still puzzled here.

    A memo field can handle up to 65,535 characters. And if used in a certain way, a memo field can handle a Gigabyte, or more, of data.

    Unless you haven't told us something, each of your fields translates to 5 characters (4 characters plus a pound-sign delimiter).

    So that means that you can handle 65,535 / 5 = 13,107 fields.

    Now, your example was for 30 fields, but you said that you will have hundreds and that the memo field was not large enough.

    WHY ! ! !

Posting Permissions

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