How do add data from one table to another

[Guest]

Hello,

I have a table named "Spec_Cond" with two fields; Parcel_ID and Cond_Code.
Then I have another table named "Cond_Code" with the SAME two fields.

The Cond_Code fields from FIRST table are ALL empty. As you might guess,
the Cond_Code fields from the SECOND table are all full, and therefore, I'd
like to populate the Cond_Code fields from the it to the FIRST table.

The Parcel_ID fields from BOTH the tables are all matched with the same
numbers, so they will function as the common key fields.

How do I do this?

Thanks in advance for your replies.

Phil.

 

[Guest]

NOTE: I erroneously posted the previous post w/o proofing it first. Sorry.
Below is the CORRECT post.
-----------------------------------------------------------------

I have a table named "Spec_Cond" with two fields; Parcel_ID and Cond_Code.
I have another table named "Cond_Code" with the SAME two fields.

The Cond_Code fields from FIRST table are ALL empty. As you might guess,
the Cond_Code fields from the SECOND table are all full, and therefore, I'd
like to populate the Cond_Code fields from the SECOND table to the FIRST
table.

BTW, the Parcel_ID fields from BOTH the tables are all matched with the same
data, so they will function as the common key fields, if this is relevant.

How do I do this?

Thanks in advance for your replies.

Phil.

 

[Guest]

Try an UPDATE QUERY. Bring both tables into your query. Join at the common
field (SKU ID, etc.) with ALL records from “Spec_Cond†and only those records
from "Cond_Code" where the joined fields are equal. The only field needed is
the "Cond_Code", see below.

Field: Cond_Code (field to be updated)
Table: Cond_Code (source table)
Update To: [Spec_Code]![Cond_Code] (table updated)



You could also do a MAKE TABLE from all of the available fields from
"Spec_Cond", except "Cond_Code" field that you pick up from "Cond_Code" table.

 

[Guest]

Hi,

I tried to follow what you said, and it doesn't seem right. When I
constructed the Update Query, and tried to run it, it said that it was going
to update 3576 rows, but the Spec_Cond table is ONLY 1526 rows.

Here is the SQL for the query:

UPDATE Cond_Code RIGHT JOIN Spec_Cond ON Cond_Code.Parcel_ID =
Spec_Cond.Parcel_ID SET Spec_Cond.Cond_Code = [Spec_Cond]![Cond_Code];

What am I doing wrong?

Try an UPDATE QUERY. Bring both tables into your query. Join at the common
field (SKU ID, etc.) with ALL records from “Spec_Cond†and only those records
from "Cond_Code" where the joined fields are equal. The only field needed is
the "Cond_Code", see below.

Field: Cond_Code (field to be updated)
Table: Cond_Code (source table)
Update To: [Spec_Code]![Cond_Code] (table updated)



You could also do a MAKE TABLE from all of the available fields from
"Spec_Cond", except "Cond_Code" field that you pick up from "Cond_Code" table.

Hello,

I have a table named "Spec_Cond" with two fields; Parcel_ID and Cond_Code.
Then I have another table named "Cond_Code" with the SAME two fields.

The Cond_Code fields from FIRST table are ALL empty. As you might guess,
the Cond_Code fields from the SECOND table are all full, and therefore, I'd
like to populate the Cond_Code fields from the it to the FIRST table.

The Parcel_ID fields from BOTH the tables are all matched with the same
numbers, so they will function as the common key fields.

How do I do this?

Thanks in advance for your replies.

Phil.

 

[John Spencer]

I would change the RIGHT JOIN into an INNER JOIN.

Access does a bit of non-standard stuff with outer joins and will attempt to
APPEND records to the Target Table where there is no match in the target
table. It updates the records where there is a match (on the join).

If you have a Primary key on the Target table that is not an autonumber
field, then you would probably get an error message when you did the update
that xx records were not updated due to ...

Hi,

I tried to follow what you said, and it doesn't seem right. When I
constructed the Update Query, and tried to run it, it said that it was
going
to update 3576 rows, but the Spec_Cond table is ONLY 1526 rows.

Here is the SQL for the query:

UPDATE Cond_Code RIGHT JOIN Spec_Cond ON Cond_Code.Parcel_ID =
Spec_Cond.Parcel_ID SET Spec_Cond.Cond_Code = [Spec_Cond]![Cond_Code];

What am I doing wrong?

Try an UPDATE QUERY. Bring both tables into your query. Join at the
common
field (SKU ID, etc.) with ALL records from "Spec_Cond" and only those
records
from "Cond_Code" where the joined fields are equal. The only field
needed is
the "Cond_Code", see below.

Field: Cond_Code (field to be updated)
Table: Cond_Code (source table)
Update To: [Spec_Code]![Cond_Code] (table updated)



You could also do a MAKE TABLE from all of the available fields from
"Spec_Cond", except "Cond_Code" field that you pick up from "Cond_Code"
table.

Hello,

I have a table named "Spec_Cond" with two fields; Parcel_ID and
Cond_Code.
Then I have another table named "Cond_Code" with the SAME two fields.

The Cond_Code fields from FIRST table are ALL empty. As you might
guess,
the Cond_Code fields from the SECOND table are all full, and therefore,
I'd
like to populate the Cond_Code fields from the it to the FIRST table.

The Parcel_ID fields from BOTH the tables are all matched with the same
numbers, so they will function as the common key fields.

How do I do this?

Thanks in advance for your replies.

Phil.

 

[Guest]

1st: My apologies, I had told you incorrectly. The following is the correct
way to do an UPDATE TABLE.

Field: Cond_Code (field to be updated)
Table: Spec_Code (table updated)
Update To: [Cond_Code]![Cond_Code] (source table)

2nd: I sounds like you are doing an APPEND query or you may have blank rows
in your in tables. You can do the following as a 2nd column (leaving Update
To: blank).

Field: Parcel_ID
Table: Cond_Code
Update To: (leave blank, this is a query field)
Criteria: Is Not Null (this will query only non-blank records)

Hi,

I tried to follow what you said, and it doesn't seem right. When I
constructed the Update Query, and tried to run it, it said that it was going
to update 3576 rows, but the Spec_Cond table is ONLY 1526 rows.

Here is the SQL for the query:

UPDATE Cond_Code RIGHT JOIN Spec_Cond ON Cond_Code.Parcel_ID =
Spec_Cond.Parcel_ID SET Spec_Cond.Cond_Code = [Spec_Cond]![Cond_Code];

What am I doing wrong?

Try an UPDATE QUERY. Bring both tables into your query. Join at the common
field (SKU ID, etc.) with ALL records from “Spec_Cond†and only those records
from "Cond_Code" where the joined fields are equal. The only field needed is
the "Cond_Code", see below.

Field: Cond_Code (field to be updated)
Table: Cond_Code (source table)
Update To: [Spec_Code]![Cond_Code] (table updated)



You could also do a MAKE TABLE from all of the available fields from
"Spec_Cond", except "Cond_Code" field that you pick up from "Cond_Code" table.

Hello,

I have a table named "Spec_Cond" with two fields; Parcel_ID and Cond_Code.
Then I have another table named "Cond_Code" with the SAME two fields.

The Cond_Code fields from FIRST table are ALL empty. As you might guess,
the Cond_Code fields from the SECOND table are all full, and therefore, I'd
like to populate the Cond_Code fields from the it to the FIRST table.

The Parcel_ID fields from BOTH the tables are all matched with the same
numbers, so they will function as the common key fields.

How do I do this?

Thanks in advance for your replies.

Phil.