Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Regina, Saskatchewan, Canada
    Posts
    17
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Indexes (Access 2003)

    Hi! I am new to this forum and have checked (as best as I could) the postings made on indexing but I could not find anything related to my question.

    I have an excel spreadsheet that I receive from a supplier and that I need to import into Access 2003. The spreadsheet is set up like a flat file database with the first four or five columns (individual identifier) identifying the individual and the remainder of the columns identifying transaction details (transaction identifier). There are two specific columns within the Individual indentifier that are unique (ClientID# (a 9 digit number) and Contract# (from 6 to 8 digit number)).

    Currently, I create a new column (index) in excel combining the ClientID# and the Contract# in the format #########.00000000 where # = the 9 digits in the employee# and 0 = the digits in the Contract# (with leading zeros where required. I then split the spreadsheet into two worksheets- one with the client info and one with the transaction details with the index column in both worksheets. I then import the worksheets into two separate tabled in Access 2003. In this way, only new clients (or old clients with new contracts) are added to the Client table but all transactions are added to the Transactions table.

    It seems to me that this is not the most efficient way to do this (and because I am using decimal numbers perhaps even dangerous?) Also, after a recent experience in Excel where I was using a 20 digit number as an index field and found out the limitation is 17 I am concerned about whether I am going to run into problems using this as my primary index.

    Any comments about how to do this in a better way would be greatly appreciated.

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

    Re: Indexes (Access 2003)

    You shouldn't combine the two numbers into one number. Excel stores numbers with 15 significant digits, so if you combine a 9 digit number with an 8 digit number, the result is not guaranteed to be correct. I don't think it is necessary either - Access can link tables on more than one column.

Posting Permissions

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