Append Query Problem

J

J

I was using a query in order to get data from one table into another
table. The second table already has some of the information stored and
only needs the information from table 1 to be put in the corresponding
record. My problem is that when I try and run the query, it says that
I'll be modifying 104 rows. I click OK and then when I view the table,
the new information is in the table but it's a new 104 records as
opposed to the original amount of records with updated data like I need
to have. If anyone could help me with this problem it would be greatly
appreciated.
 
E

Ed Robichaud

You don't say what your query design is, but it sounds like an "append"
type. If so, it's working exactly as you should expect, appending (or
adding) the records to your table. An "update" query would add/replace
field values to existing records.

A bigger question though is, do you really need to store the same
information twice? As you've discovered, a query can link the info from
your two tables.

-Ed
 
J

Jeff Boyce

Append queries ... append rows. If you want to update data in existing
rows, use an ... update query.

By the way, it may not be necessary to copy data from one table to another.
If you provide an example of what kind of data you are copying, the 'group
may be able to offer an alternate approach.
 
J

J

Well, I need to have this data there a 2nd time in a separate table. I
just tried an Update query. It said that I would update 104 rows but
when I check the table there's nothing new in it. Here's what my query
looks like:

Field: DWG PartNo RevNo
Table: Status Status Status
Update To: "DWG" "No" "RevNo"

I havn't done one of these in a long time so I'm not sure what the
problem is. I would assume that the "Update To" Field is the column
name in the new table that will be updated.
 
E

Ed Robichaud

If I understand correctly, you want to replace the value of certain fields
in records of one table with those from records in another table. If so,
your query design grid should be changed to look like that below:

Field: DWG PartNo
Table: Status Status
Update To: [my2ndTable].[DWG] [my2ndTable].[No] ........etc.

-Ed
 
J

J

I tried what you suggested but then it asked me to input data for
table2.dwg, etc. I need to move 3 fields from records in table 1 over
to corresponding fields in table 2. In each of the tables however
there are more than 3 columns. All I need to do is get the information
from table 1 to fit into the correct records in table 2.
 
E

Ed Robichaud

It sounds like your tables are not related (joined) in any way. If table2
contains more than 1 record, than how do you determine which record is to
update a record in table1? Why don't you explain at length, in English,
what you're trying to accomplish?
-Ed
 
J

J

I have 2 tables. I need to transfer the data from table1 into the
corresponding colums in table 2. Table 2 has some information already
but lacks the 3 fields that need to be transferred over (DWG, Part No,
Rev No). The 2 tables are joined by "JOB" in table 1 which is linked
to "WORK ORDER" in table 2. If things seem a little confusing I
apologize. I'm doing this for someone else and can only work with what
they have told me.
 
J

Jeff Boyce

When you say "transfer over", do you mean the second table doesn't even have
the fields? If so, where do you want Access to stick the data?

I'll still suggest that, until you provide a strong rationale otherwise, it
is unusual and rare to actually need to copy data from one table to another,
if you are working in a well-normalized data base.

Perhaps if you described WHY you feel you need to transfer the data over,
folks could offer alternate ideas...
 
E

Ed Robichaud

OK, sounds like they are related. An "Update" query needs to know which
fields get which values, so in the query design grid, you designate what
target table, and "map" which fields in that target (your Table1) get
updated by the values of which fields in the source (your Table2). If the
fields don't already exist in Table1, then you'll need to create them first
(in design view, using similar field types, though field names need not be
identical). The Access help on update queries may be useful.
-Ed
 
J

J

The 2nd table has the fields but no data entered. I'm doing this for
something at work and this is what I was told to do.
 
Top