ACCESS FEED: RE: [MS_AccessPros] Query Won't append new field

Rabu, 21 September 2011

RE: [MS_AccessPros] Query Won't append new field

You are attempting to run an Append query when I believe you need an update query. Make sure tblIndustry_Names.strIndustry has a unique index on it.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: wgriffin48@mchsi.com
Date: Wed, 21 Sep 2011 22:45:19 +0000
Subject: Re: [MS_AccessPros] Query Won't append new field

Hey Duane;

Yea, data normalization was not my friend when I first put this app together so now I'm doing it the hard way :-P

I have a table called tblIndustry, I am removing the text field with the name of the industry and replacing it with an ID field from a new table called tblIndustry_Names, culled from the tblIndustry using a query that pulled distinct industry names (removed about 10000 dupes)
I then inserted a new ID field into the result. Now I am trying to insert the IDIndustry_Name into a blank field I inserted into the tblIndustry . When I have the ID field in the original tblIndustry I will remove the old strIndustry text field.
The query is
INSERT INTO tblIndustry ( IDIndustry, idStation, idIndustry_Name, strIndustry, idCommodity, strSR, strEra, idCarType )
SELECT tblIndustry.IDIndustry, tblIndustry.idStation, tblIndustry_Names.idIndustry_Name, tblIndustry.strIndustry, tblIndustry.idCommodity, tblIndustry.strSR, tblIndustry.strEra, tblIndustry.idCarType
FROM tblIndustry INNER JOIN tblIndustry_Names ON tblIndustry.strIndustry = tblIndustry_Names.strIndustry
WHERE (((tblIndustry.strIndustry)=[tblIndustry_Names]![strIndustry]));

This works great in view mode (it can build the monster table in memory but it won't write the changes)
I'm confused.

Walter

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> Walter,
> It's very difficult for us to see your SQL or your table or constraints or fields or data types or relationships or anything else since we are not sitting at your computer.
>
> It is good to hear that your attempting to normalize your data. This is typically a very good investment.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: wgriffin48@...
> Date: Wed, 21 Sep 2011 22:21:36 +0000
> Subject: [MS_AccessPros] Query Won't append new field
>
>
>
>
>
>
> Hi folks, Been a while.
> I have run into a problem restructuring a table, adding a new field to help normalize data, substituting a text field with an ID field from a new table (containing the text field and an ID field). I created a blank number field in the table to be modified for the ID field that will replace the text field (to be removed later). I ran an append query and it works fine in "view" mode but when I run it I get an error message telling me it found "0" issues but it won't write any changes to the table? (There are 27422 records in this table). Is there a way to make this work?
>
> Walter Griffin
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>