Results 1 to 5 of 5
  1. #1
    Rebecca is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    2

    Automatically Reduce Field Size


    I am using Windows 7 and Microsoft Access 2007.

    I recently imported a lot of data from Microsoft Excel 2007 into Access. The field size allotted for each column was 255. Some of the columns contain only 15 characters, but none of the others contain 255.

    Is there any way to reduce the size of the fields safely, so no data is lost? I am new to Access and I don't know how to program or do anything complicated now. Please explain the procedure in easy English. Thank you very much.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Very simple go open the table in the design view click on the field that you want to alter the size. at the bottom of the screen you will find the field properties. In the Field size property type the size e.g. 15. When you will try to save the change you will get a confirmation prompt. Clicking yes will reduce the size of the field.

    a few words of caution:

    1. it is a good practice not to adjust the size of a field that is involved in a relationship because this may lead to a relationship failure.
    2. Suppose you change the size of the field from 255 to 20 any data that might be having say 25 or 22 letters will be reduced to 20 letters. I made the mistake long time back and believe me it caused a lot of confusion.

    so if you are absolutely sure go a head. I will suggest you check your data first just to be sure make a select query and then add a calculative field e.g. you want to change the size of the name field. then make a select query out your table add a feild CountAlpha:Len([Name]) set the condition of that field as >15 so basically you will be able to find out any data that is there in that feild having more then 15 letters.

    if this solves your problem please mark the thread as solved.

  3. #3
    Rebecca is offline Novice
    Windows 7 Access 2007
    Join Date
    Jan 2010
    Posts
    2
    Not quite solved yet. I do NOT know the length of the data in the various table columns (the number 15 was just an estimate; it could be anywhere from 5 to 150 or more up to 255, though unlikely).

    So, what is a quick way to find out which words are the longest in various columns? Obviously I will need to know this first.

    Please keep the explanation in SIMPLE AND CLEAR English. Thanks.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Just so you know: Access does *not* reserve the unused space in a field so there is no penalty for declaring a field too large.

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows XP Access 2003
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Rural Guy is right I think your exercise is absolute unecessary.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automatically choose which report?
    By Lockrin in forum Reports
    Replies: 2
    Last Post: 12-28-2009, 02:41 PM
  2. Compact/Reapir doe not reduce database size
    By nosrepmodnar in forum Access
    Replies: 1
    Last Post: 10-23-2009, 02:38 AM
  3. Automatically get Excel data
    By TimG in forum Import/Export Data
    Replies: 2
    Last Post: 08-24-2009, 06:38 AM
  4. Replies: 3
    Last Post: 04-12-2009, 05:11 PM
  5. Have database open automatically
    By Norman Masson in forum Access
    Replies: 6
    Last Post: 01-08-2008, 07:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums