Update Query Help!

[steve12173]

I had inherited a database, which I need to add the info of one field, to
another database. The one field in the old database that I need to add to
the new database matched by the Prospect ID.

Old Table - tblProspects
Field - Notes

New Table - tblProspectsComplete
Field - Notes

I need the query to add all the data in the Notes field from the
tblProspects to the Notes field in the tblProspectComplete where the Prospect
ID is the same.

 

[John W. Vinson]

I had inherited a database, which I need to add the info of one field, to
another database. The one field in the old database that I need to add to
the new database matched by the Prospect ID.

Old Table - tblProspects
Field - Notes

New Table - tblProspectsComplete
Field - Notes

I need the query to add all the data in the Notes field from the
tblProspects to the Notes field in the tblProspectComplete where the Prospect
ID is the same.

If the ID is the Primary Key of each table (or at least has a unique Index)
then simply create a query joining tblProspects to tblProspectsComplete by ID;
change the query to an Update query; and put

[tblProspects].[Notes]

on the Update To line under tblProspectsComplete.Notes. Run the query by
clicking the ! icon.

 

[steve12173]

It says that it is updating the records but, it isn't changed when I go back
and look at the records.

Here is my SQL in the query:
UPDATE tblProspectComplete INNER JOIN tblProspect ON tblProspectComplete.SSN
= tblProspect.SSN SET tblProspect.Notes = tblProspectComplete.Notes;

I had inherited a database, which I need to add the info of one field, to
another database. The one field in the old database that I need to add to
the new database matched by the Prospect ID.

Old Table - tblProspects
Field - Notes

New Table - tblProspectsComplete
Field - Notes

I need the query to add all the data in the Notes field from the
tblProspects to the Notes field in the tblProspectComplete where the Prospect
ID is the same.

If the ID is the Primary Key of each table (or at least has a unique Index)
then simply create a query joining tblProspects to tblProspectsComplete by ID;
change the query to an Update query; and put

[tblProspects].[Notes]

on the Update To line under tblProspectsComplete.Notes. Run the query by
clicking the ! icon.

 

[John W. Vinson]

It says that it is updating the records but, it isn't changed when I go back
and look at the records.

Here is my SQL in the query:
UPDATE tblProspectComplete INNER JOIN tblProspect ON tblProspectComplete.SSN
= tblProspect.SSN SET tblProspect.Notes = tblProspectComplete.Notes;

Are you actually *RUNNING* the query, either by clicking the ! icon or by
saving the query, doubleclicking it in the query window, and accepting the
warning "this query will update xyz records"?

Or are you just looking at the query datasheet from the query design window?
The latter will display the records before updating.

Also... you do indeed want to update the records in tblProspect, not those in
tblProspectComplete?

 

[steve12173]

I am wanting to update the records in the tblProspectComplete with the Notes
field from tblProspect. I did click the ! icon to run the query and I tried
doudle clicking the query. It gives me the warning that the records will be
updated but, they are not.

 

[John W. Vinson]

I am wanting to update the records in the tblProspectComplete with the Notes
field from tblProspect. I did click the ! icon to run the query and I tried
doudle clicking the query. It gives me the warning that the records will be
updated but, they are not.

Very odd. The SQL looks impeccable.

If you change the query from an Update query to a Select query, what do you
see? You may want to select both instances of the Notes field to see what they
look like in the two tables.

 

[steve12173]

I had heard from someone that you can not use an update query on a field with
a memo data type in the table. Have you ever heard this?

 

[John W. Vinson]

I had heard from someone that you can not use an update query on a field with
a memo data type in the table. Have you ever heard this?

I know for a fact that it is untrue. You can update any field (including the
memo field) in a query except for an Autonumber field.