How to Update an existing table in a Database 2

[JulietB]

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

 

[Rohdem]

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!"

 

[JulietB]

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

 

[tlbroadbent]

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

 

[JulietB]

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.....

 

[Rohdem]

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!"