basic update query question

[TimR]

In a simple update query from DB "B" to DB "A"...I know the compared fields
(only using one field) have to be EXACTLY the same...but...do the number of
records and/or the sort order of the records being compared/updated also
have to be excactly the same number of records or sort order ? Or, can the
two data bases have a different number of records and sort order of records
since you are updating only one particular field from DB "B" in DB "A" based
on one seperate matching exact field value in both ?

Should you use the Access default ID value of 1,2,3, etc...or no index value
when running a simple update query ?

Thanks, Tim

 

[John W. Vinson]

In a simple update query from DB "B" to DB "A"...I know the compared fields
(only using one field) have to be EXACTLY the same...but...do the number of
records and/or the sort order of the records being compared/updated also
have to be excactly the same number of records or sort order ? Or, can the
two data bases have a different number of records and sort order of records
since you are updating only one particular field from DB "B" in DB "A" based
on one seperate matching exact field value in both ?

The sort order is absolutely irrelevant and is not used. An Update
Query joining two tables will require a unique Index (such as a
primary key) on the linking field in the table you're updating FROM;
the way the query works is to find all the records in the second table
which exactly match that value. You can update one or several fields;
and you can actually join the two tables on one field, two fields,
even TEN fields, just so the combination is unique in the "from" table
and they match in datatype and size between the two tables.

Should you use the Access default ID value of 1,2,3, etc...or no index value
when running a simple update query ?

Use it for... what...!? You should use whatever field or combination
of fields let you uniquely identify which record should be updated. If
you have an Autonumber field, and information as to which autonumber
value identifies the record that you want, by all means use it; if
some other field or combination of fields identify the record you
want, use them instead.

For instance, if you have a bunch of records in your table with a ZIP
code of 83660, you can use that as a criterion on your update query
and update the CITY field to "Parma".

John W. Vinson [MVP]

 

[TimR]

Thanks John...I just opened Access for the first time a week or so ago...so
this is all new to me ! Does the number of records in the two tables I am
comparing matter...I have two tables with the same fields but differing
numbers of records...and I am having lots of trouble getting my first query
to work.

Trying to compare records via the Assessors number ( identical in the two
tables) and the replace the FirstName field in DB "A" with the corrected
FirstName field in DB "B"

Also...The field I am comparing between the two tables is an Assessor's
number field and there is a combination of numbers like:

12232458
12235779
13450666
and ,
12388352A
12048337B
etc

The field was imported (along with all the rest of the data) from an Excel
file with a 'general' formatting in the cells...is this a problem for the
Access datatype setup ?

Thanks, Tim

 

[John W. Vinson]

Thanks John...I just opened Access for the first time a week or so ago...so
this is all new to me ! Does the number of records in the two tables I am
comparing matter...I have two tables with the same fields but differing
numbers of records...and I am having lots of trouble getting my first query
to work.

The number of records does matter to some extent: IF there are
multiple records for each Assessors Number in table B, then you won't
be able to update. Suppose one record had the FirstName "Jim" and a
different record, for the same Assessors number, had the first name
"Aloysius". What should that record in A be updated to?

What is the Primary Key of each table? Do they have primary keys? (If
not - you have problems!!!) Does the Assessors Number uniquely
identify a record?

Trying to compare records via the Assessors number ( identical in the two
tables) and the replace the FirstName field in DB "A" with the corrected
FirstName field in DB "B"

Also...The field I am comparing between the two tables is an Assessor's
number field and there is a combination of numbers like:

12232458
12235779
13450666
and ,
12388352A
12048337B
etc

The field was imported (along with all the rest of the data) from an Excel
file with a 'general' formatting in the cells...is this a problem for the
Access datatype setup ?

I presume that the datatype of the field must be Text. That's ok; a
text field works fine for a join.

However, the text strings "12388352A" and "12388352" are *DIFFERENT*
text strings and will not match. Are you expecting them to do so?

Since I cannot see your tables, don't know field names, don't know
your primary keys, and don't know your relationships I'm somewhat
groping in the dark - but try this:

Back up your database. Update queries are *not* reversible!
Create a Query.
Add Table A and Table B.
Drag the Assessors Number from Table A to Table B.
Change the query to an update query.
On the Update To line under TableA.FirstName type

[Table B].[FirstName]

using the actual name of table B of course. The brackets are
important, otherwise it will update everyone's first name to

"Table B.FirstName"

Run the query by clicking the ! icon.

If this isn't working, please open your query in design view, and
select View...SQL. Copy and paste the SQL text (which is the *real*
query, the grid is just a tool to create SQL) to a message here;
indicate what you expected to happen and what's actually happening.

John W. Vinson [MVP]