Update Existing Table with New Information

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all;

I have a table in my database that has several fields which need to be updated periodically with new information. I have successfully imported the new information into Access and created a new table from it. How do I move that information into the main table? I tried an append query, but I got a primary key violation. What does that mean?

Any help would be appreciated, as I'm far from proficient with this program.
Thanks!
 
Your tables both have a primary key field?

If you open the table in design view, this field has a little key icon to
the left. If is often an AutoNumber. The idea of the key field is that it
must be unique, e.g. you cannot have two records in the table that both have
23 in the primary key.

If you try to append to the table, Access is probably trying to append the
value from the autonumber field as well. Becuase you already have a record
with the same value in the primary key, it cannot append the record.

To solve the problem, open your append query in design view. Remove the
primary key field from the grid. That will allow Access to append the other
fields, and it will automatically assign another unique number for its
primary key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Casa said:
I have a table in my database that has several fields which need to be
Click to expand...
updated periodically with new information. I have successfully imported the
new information into Access and created a new table from it. How do I move
that information into the main table? I tried an append query, but I got a
primary key violation. What does that mean?
 
Hi all;

I have a table in my database that has several fields which need to be updated periodically with new information. I have successfully imported the new information into Access and created a new table from it. How do I move that information into the main table? I tried an append query, but I got a primary key violation. What does that mean?

Any help would be appreciated, as I'm far from proficient with this program.
Thanks!
Click to expand...

An Append query appends new records; it sounds like you may need an
Update query instead. Do you have some field (or combination of
fields) which identify which record in the main table should be
updated with a particular record in the imported table? If so, create
a Query joining the two tables, change it to an Update query, and
update Fieldname to

[Secondtablename].[Fieldname]

Run the query by clicking the ! icon.

Of course... back up your database first, just in case!
 
Yes, I'd be updating existing records. I will try to figure out how to join two tables, and then do the update. Are joins hard to do?

John Vinson said:
Hi all;

I have a table in my database that has several fields which need to be updated periodically with new information. I have successfully imported the new information into Access and created a new table from it. How do I move that information into the main table? I tried an append query, but I got a primary key violation. What does that mean?

Any help would be appreciated, as I'm far from proficient with this program.
Thanks!
Click to expand...

An Append query appends new records; it sounds like you may need an
Update query instead. Do you have some field (or combination of
fields) which identify which record in the main table should be
updated with a particular record in the imported table? If so, create
a Query joining the two tables, change it to an Update query, and
update Fieldname to

[Secondtablename].[Fieldname]

Run the query by clicking the ! icon.

Of course... back up your database first, just in case!
Click to expand...
 
Yes, I'd be updating existing records. I will try to figure out how to join two tables, and then do the update. Are joins hard to do?
Click to expand...

Not if your tables are correctly structured. You need a "Primary Key"
field in each table, a field which uniquely identifies the record
(actually you can have a multifield primary key); and a "foreign key"
matching the primary key in datatype in the second table. Just add
both tables to the Query grid and - if Access doesn't do so
automatically - drag the primary key of the "master" table to the
foreign key field of the "child".

If you have two tables with a one to one relationship the foreign key
field will also be the primary key - just drag the primary key to the
primary key.

If you don't have a primary key in either table... fix your table
structure. It's essential.
 
Thanks for your help. Yes, I've assigned primary keys to both tables. Does it matter if the primary keys in both tables reflect the same set of numbers? For example, I'm using "sample numbers" as my primary key in the two tables. Some of the sample numbers are represented in both tables and some of the sample numbers are unique to the table I want to use to update my main table. I want to use the second table (the one with the new data) to update the main table -- some new sample numbers need to be added, as well as all of the new data for some existing records in the main table.
I got an append query going, but it only appended the sample numbers and data of the unique sample numbers that weren't in the main table previously. While that's useful, I really need to get the rest of the data in.
 
Thanks for your help. Yes, I've assigned primary keys to both tables. Does it matter if the primary keys in both tables reflect the same set of numbers? For example, I'm using "sample numbers" as my primary key in the two tables. Some of the sample numbers are represented in both tables and some of the sample numbers are unique to the table I want to use to update my main table. I want to use the second table (the one with the new data) to update the main table -- some new sample numbers need to be added, as well as all of the new data for some existing records in the main table.
I got an append query going, but it only appended the sample numbers and data of the unique sample numbers that weren't in the main table previously. While that's useful, I really need to get the rest of the data in.
Click to expand...

The sample numbers are fine.

Just create an Update query joining the main table to the update table
by sample number (a one to one join in this case); update the desired
fields in Master to

[SecondTable].[Fieldname]

with the brackets and (of course) your own table and fieldnames. This
will update only the already-existing records; the Append query will
update the rest.
 
Back
Top