Results 1 to 6 of 6
  1. #1
    Xty
    Guest

    adding a field/column from excel

    How do i insert/add a field/column imported from excel or from another table already in Access 2000 v9.0?
    historically i have always done this thru a query, but don't like the fact that all properties on my original table are negated when i do this. I want to maintain the general properties of my original table and just add one new field.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding a field/column from excel

    I'm not clear on what you mean about usually adding the field in a query, but this may help

    Open the existing table in the design view and add the field there; specify the properties you want for that field.
    Now if you need to add the data through a query, the field will have the properties that you specified in the table design.

    If this isn't what you are trying to do, post back with more detail on the table and the format/procedure of what you are trying to add.

  3. #3
    Xty
    Guest

    Re: adding a field/column from excel

    I have a table which includes all of the counties in the US (each with own
    unique numerical identifier called a FIPS number) plus demographic data for
    each county (e.g., population 1999, median age 1999, %minority, median
    household income, etc). Now that the new census is coming out i want to
    maintain the old data, but add new fields such as pop2000, age2000, etc.
    In the past i have imported a table with the new data from excel to access,
    put both of those tables into a query, added the fields i wanted to include
    (in this case ALL of the fields from the original table plus the new 2000
    data fields), joined the tables on their FIPS numbers and run a 'create
    table query'. This negates all of the properties of my original table
    however (e.g., data displayed as percents or $$). I am assuming there is an
    easier way of just adding the new fields/columns without having to go thru
    all this. I hope this clarifies my problem. thank you for taking the time.
    Xty

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: adding a field/column from excel

    Hi,
    You could add a year field to your table rather than adding a whole new set of fields. That way all you need to do is add the year to the info you're importing and append it to your existing table so you end up with multiple records per county rather than one record with an increasing number of fields.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Xty
    Guest

    Re: adding a field/column from excel

    I have a table which includes all of the counties in the US (each with own
    unique numerical identifier called a FIPS number) plus demographic data for
    each county (e.g., population 1999, median age 1999, %minority, median
    household income, etc). Now that the new census is coming out i want to
    maintain the old data, but add new fields such as pop2000, age2000, etc.
    In the past i have imported a table with the new data from excel to access,
    put both of those tables into a query, added the fields i wanted to include
    (in this case ALL of the fields from the original table plus the new 2000
    data fields), joined the tables on their FIPS numbers and run a 'create
    table query'. This negates all of the properties of my original table
    however (e.g., data displayed as percents or $$). I am assuming there is an
    easier way of just adding the new fields/columns without having to go thru
    all this. I hope this clarifies my problem. thank you for taking the time.
    Xty

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding a field/column from excel

    Okay, now it's clearer, but what you are doing is causing repetitive fields to be added per year, which results in an unnormailized table. (as Rory has suggested)

    How about creating a new table with the fields FIPS, Year, Age etc.... Let's call it YearlyStats. Append the Excel data to this new table for each FIPS and Year. Then you can link this table to the original table (1 to many) on FIPS. This will give you the data that you need and no longer require that you keep adding fields to the original table. When you need to add a new year's data, just append it to the YearlyStats table.

Posting Permissions

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