×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to Update an existing table in a Database
2

How to Update an existing table in a Database

How to Update an existing table in a Database

(OP)
Hi All,
I am new to this group, and I must congratulate you all, great group.
I am a New/Junior DBA using Access 2000, the database that I am administering was already created/designed. There are some backlog and new data that needs to be inserted to get this database uptodate.
Is there an easy way to do this....
At the moment, I am storing these new and old info in an Excel sheet, which I will eventually import to Access. The Excel sheet reflects the Access table (same design). The Access table has Provinces and Years as fields and an Autonumber for the ID field.  

Is it possible to update/import to the (Year field) and (ID field) without sending the information to the last record.

Correct me if I am wrong, in Access whenever you insert a new record it goes to the last record in the Table.  Unlike Excel, you can insert rows in a table and it updates.

Thanks for your help on this....
Arlene

RE: How to Update an existing table in a Database

First of all, you can link Excel spreadsheets as a table to an Access database.  This is where I would start.
Right-click anywhere in the database window and select link tables.  Select excel spreadsheet for the file type and select your file.  The wizard will walk you through linking the spreadsheet.

Once this is done, you can use append and update querys to move the data from your excel table to your Access table.

I'm not completely clear on what you are trying to do, but it sounds like you might want to run an update query which allows you to update specific fields in a table.

Hopefully this will get you started!!

Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

RE: How to Update an existing table in a Database

(OP)
Thank you Mike, I will try what you suggested, but to be clear on what I am trying to achieve, here is the explanation below.

The table as I said before has various fields, I am particularly interested in the Year field and the ID field.
The Province feild has 10 different names and they are repeated everytime the year field is updated.
Presently the Year field on a certain Province/State stops at 1993, this has to be updated to 2000, with the present data. I want to update the table and continue from 1993, I do not want to insert the backlog and store it where the last record in the table stops.

Here is a synopsis of the table:
ID    Prov    Year
1    MA    1990
2    MA      1991
3    BC    1990
4    BC    1991
5    BC    1992
6    NS    1990
7    NS    1991
8    NS    1992
9    ON    1990
10    ON    1991
11    ON    1992
12    SK    1990
13    SK    1991
14    SK    1992

Thanks again






RE: How to Update an existing table in a Database


When you insert the new data, you'll only insert the Province and Year. Access will increment the ID (autonumber) column.

Access always appends on the end. But physical order is irrelevant in a relational database because you can order the records however you need for viewing and reporting. Thus you can order by Province, Year or Year, Province or even by ID number.

Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

RE: How to Update an existing table in a Database

(OP)
Thank you Terry, I thought that somehow there was a way for Access to append in the Table and not only at the end of the table.
I also thought for consistency and viewing puposes, the data looks better together than all over the place. Which you can get of course through a query, as you mentioned.
I rest my case.....

RE: How to Update an existing table in a Database

Most of the time, the end-user is not viewing the table directly.  It is either through a form or report.  

You can also quickly sort a table by putting your cursor in the field you want to sort and clicking the sort ascending or sort descending button.

Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close