Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: Importing new data into new column on table

    Hi,
    I’m running access 2003.
    I have a table were I need to add a column on a monthly basis for the months figures. I have a linked excel file which updates another table with the new month figures.

    Could I have some help in adding a new column to my data table with the new months figures from the imported excel table?

    I have managed to insert a blank column into my table with the code below:
    CurrentDb.Execute ("ALTER TABLE tbl_Actual_car_Sales ADD COLUMN 201108 number")

    I have also managed to copy data from same table to the blank cells in the same data with the code below:
    CurrentDb.Execute ("UPDATE tbl_Actual_car_Sales SET P201108 = P201107")

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're working with Access as you would work with Excel, however an access table is not an Excel sheet. If there are data of the same nature but for different periods of time, they should be stored in the same table, adding rows, not columns to the table. In the case you describe, a column would indicate the concerned period, possibly in a 'yyyymm' format.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    2
    Thanks Sinndho,

    So theres no way to make a code that will allow me to insert the data in a column from another table in the same database? other then redesigning the original table and making this into a cross tab query?

    Somthing on the lines below? but this is not copying over the data.
    CurrentDb.Execute ("UPDATE tbl_Actual_car_Sales SET P201107 = ('tbl_importedData' P201107")

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In any case you'll be obliged to issue a DDL SQL statement or use DAO or ADO for modifiying the structure of the table first. This will prevent you from using a relational database the way it is intended to.

    The best solution I can come with (except for properly redesigning the database) would consists in creating additional tables (possibly using SELECT ... INTO SQL queries), then linking the different tables with dynamic SELECT queries. But even this does not make a lot of sense.

    See: Database Design :: Normalization Basics - Techniques
    Have a nice day!

Posting Permissions

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