Why isn't this updatable?

[Stephen Glynn]

I'm trying to convert a flat CSV database into a normalised Access
database.

I've imported it into Access and want to replace the field 'Categories'
with a field 'CategoriesID'. I've run a query on E-listing (the table
I imported and which contains about 7000 records) and discovered I've
got 97 category types. I then created a tblCategories with an autoID
CategoryID field and a text CategoryName field. Then I turned my select
query into an append query and tipped all 97 records into tblCategories.

Next I created a number field in E-listing called CategoryID.

What I now want to do is join E-listing and tblAllProducts on the
CATEGORY = CategoryName fields, which works, and then update the empty
E-listing.CategoryID to tblCategories.CategoryID.

This I can't do because the query isn't updatable.

Why isn't it updatable and how can I make it updatable?

The SQL is

SELECT Categories.[Category ID], Categories.[Category Name],
[E-listing].CategoryID
FROM Categories LEFT JOIN [E-listing] ON Categories.[Category Name] =
[E-listing].CATEGORY;

Steve

 

[John Vinson]

I'm trying to convert a flat CSV database into a normalised Access
database.

I've imported it into Access and want to replace the field 'Categories'
with a field 'CategoriesID'. I've run a query on E-listing (the table
I imported and which contains about 7000 records) and discovered I've
got 97 category types. I then created a tblCategories with an autoID
CategoryID field and a text CategoryName field. Then I turned my select
query into an append query and tipped all 97 records into tblCategories.

Next I created a number field in E-listing called CategoryID.

What I now want to do is join E-listing and tblAllProducts on the
CATEGORY = CategoryName fields, which works, and then update the empty
E-listing.CategoryID to tblCategories.CategoryID.

This I can't do because the query isn't updatable.

Why isn't it updatable and how can I make it updatable?

The SQL is

SELECT Categories.[Category ID], Categories.[Category Name],
[E-listing].CategoryID
FROM Categories LEFT JOIN [E-listing] ON Categories.[Category Name] =
[E-listing].CATEGORY;

Steve

First off, make it an Inner Join since you only want to update
existing categories; secondly, make sure that there is a unique Index
on [Categories].[Category Name].

You can then change the query to an Update query and update
[E_Listing].[CategoryID] to [Categories].[CategoryID], and after
that's working, delete the (redundant) Category field.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Stephen Glynn]

I'm trying to convert a flat CSV database into a normalised Access
database.

I've imported it into Access and want to replace the field 'Categories'
with a field 'CategoriesID'. I've run a query on E-listing (the table
I imported and which contains about 7000 records) and discovered I've
got 97 category types. I then created a tblCategories with an autoID
CategoryID field and a text CategoryName field. Then I turned my select
query into an append query and tipped all 97 records into tblCategories.

Next I created a number field in E-listing called CategoryID.

What I now want to do is join E-listing and tblAllProducts on the
CATEGORY = CategoryName fields, which works, and then update the empty
E-listing.CategoryID to tblCategories.CategoryID.

This I can't do because the query isn't updatable.

Why isn't it updatable and how can I make it updatable?

The SQL is

SELECT Categories.[Category ID], Categories.[Category Name],
[E-listing].CategoryID

FROM Categories LEFT JOIN [E-listing] ON Categories.[Category Name] =

[E-listing].CATEGORY;

Steve

First off, make it an Inner Join since you only want to update
existing categories; secondly, make sure that there is a unique Index
on .

You can then change the query to an Update query and update
[E_Listing].[CategoryID] to [Categories].[CategoryID], and after
that's working, delete the (redundant) Category field.

John W. Vinson[MVP]
(no longer chatting for now)

Thanks (and also thanks to Barron Henderson).

I hadn't realised about the importance of having a unique index on
[Categories].[Category Name]. I can see it's a valuable safety
precaution but I didn't realise it was mandatory and Help doesn't seem
to mention it in the section on updatable queries.

Anywhere I can read up on the subject? I always get confused about
what'll update and what won't.

Steve

 

[Ken Snell [MVP]]

See these MS Knowledge Base articles for more info about updatable queries:

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


--

Ken Snell
<MS ACCESS MVP>

I'm trying to convert a flat CSV database into a normalised Access
database.

I've imported it into Access and want to replace the field 'Categories'
with a field 'CategoriesID'. I've run a query on E-listing (the table
I imported and which contains about 7000 records) and discovered I've
got 97 category types. I then created a tblCategories with an autoID
CategoryID field and a text CategoryName field. Then I turned my select
query into an append query and tipped all 97 records into tblCategories.

Next I created a number field in E-listing called CategoryID.

What I now want to do is join E-listing and tblAllProducts on the
CATEGORY = CategoryName fields, which works, and then update the empty
E-listing.CategoryID to tblCategories.CategoryID.

This I can't do because the query isn't updatable.

Why isn't it updatable and how can I make it updatable?

The SQL is

SELECT Categories.[Category ID], Categories.[Category Name],
[E-listing].CategoryID

FROM Categories LEFT JOIN [E-listing] ON Categories.[Category Name] =

[E-listing].CATEGORY;

Steve

First off, make it an Inner Join since you only want to update
existing categories; secondly, make sure that there is a unique Index
on .

You can then change the query to an Update query and update
[E_Listing].[CategoryID] to [Categories].[CategoryID], and after
that's working, delete the (redundant) Category field.

John W. Vinson[MVP]
(no longer chatting for now)

Thanks (and also thanks to Barron Henderson).

I hadn't realised about the importance of having a unique index on
[Categories].[Category Name]. I can see it's a valuable safety
precaution but I didn't realise it was mandatory and Help doesn't seem
to mention it in the section on updatable queries.

Anywhere I can read up on the subject? I always get confused about
what'll update and what won't.

Steve