Example of a append query and a update query

  • Thread starter Thread starter Karen Middleton
  • Start date Start date
K

Karen Middleton

Can you please give me a sample of a append query and a update query.

Apart from append and update query what other query types do we have.

I have a scenario where I have table1 and table2.

The structure of table1 is the same as table2.

What I want to do is if for every record in table1 if it is present in
table2 I want to replace the table2 record by the table1 record. If
the record in table1 is not present I want to insert the record in
table1 into table2.


Please advice how I can do this.

Thanks
Karen
 
Karen

It is fairly unusual to have two tables with the same structure, IF you have
a well-normalized relational database. If you have a spreadsheet, that's
very common.

If you want to see examples of append and update queries, Access HELP
provides those.

Note: if you'll describe the "why"/your business need, there may be
alternate approaches you could use. For example, one reason to have
identical structures in two tables is in an attempt to "archive" records. A
much easier way to do this is to add a single field to a single table. That
field can be a Yes/No, if you only need to know ?Archived?, or a date/time
field, if you want to know "when". Putting a valid value in that field
means the record is archived.

Then, you only need to modify your queries to look for "non-Archived" rows
to see all the "active" rows.
 
Can you please give me a sample of a append query and a update query.
Click to expand...

An Update query changes the values of fields in records which already
exist.

An Append query adds new records to a table, in addition to the
records which already exist.
Apart from append and update query what other query types do we have.
Click to expand...

Many; among the other "action" queries are MakeTable queries, which
create a new table; Delete queries (which, well, *delete* records from
the table); and the various types of DDL (Data Definition Language)
queries, which can create new tables from scratch or modify the
structure of tables.
I have a scenario where I have table1 and table2.

The structure of table1 is the same as table2.
Click to expand...

As Jeff says... this causes great big red flags to go up in most
developer's minds. Storing data redundantly, the same data in two
different tables, is *very rarely* either necessary or appropriate.
What I want to do is if for every record in table1 if it is present in
table2 I want to replace the table2 record by the table1 record. If
the record in table1 is not present I want to insert the record in
table1 into table2.
Click to expand...

Since you wish to change the value of records which currently exist,
an Update query is the proper tool. You'll need to join the two tables
on their Primary Key (and if they don't have a Primary Key your tables
are *certainly* not properly constructed).
Please advice how I can do this.
Click to expand...

I'll suggest that you respond to Jeff's question: why do it at all?

John W. Vinson[MVP]
 
Back
Top