By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

copying a word into an access table

P: n/a
Hi,

I'm new to Access and this may be a basic question but I would
appreciate it if you could let me know how to do this:

I've created an Access table and would like to insert a column with the
field name "name" and to put the same word "unknown" down all the rows
of the column.

How could this be done both in the design mode and in sql code?

Thanks in advance.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Hi Brian,

First don't call a field "Name". Use FirstName, FName, LName, ObjectName
etc. "Name" is a reserved word in access and can cause conflicts later on.

To update the newly created field with the word "Unknown" use the following
sql statement
UPDATE YourTable SET YourTable.YourNewField = "Unknown"
WHERE (YourTable.YourNewField Is Null);

Even easier use the query grid and change the query type to an update query.

Jeff
"brian kaufmann" <br************@hotmail.com> wrote in message
news:41**********************@news.newsgroups.ws.. .
Hi,

I'm new to Access and this may be a basic question but I would
appreciate it if you could let me know how to do this:

I've created an Access table and would like to insert a column with the
field name "name" and to put the same word "unknown" down all the rows
of the column.

How could this be done both in the design mode and in sql code?

Thanks in advance.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #2

P: n/a
On 27 Oct 2004 19:07:36 GMT, brian kaufmann wrote:
Hi,

I'm new to Access and this may be a basic question but I would
appreciate it if you could let me know how to do this:

I've created an Access table and would like to insert a column with the
field name "name" and to put the same word "unknown" down all the rows
of the column.

How could this be done both in the design mode and in sql code?

Thanks in advance.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Brian,
"Name", as well as certain other words, is a reserved word in
Access/VBA/Jet and should not be used as field or object name in
Access.

See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

After changing the field name, you can follow Jeff Smith's query code
to place "unknown" in each record.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

P: n/a
Hi Jeff and others,

Thanks for the code on copying the word in a field. I tried doing the
code by adding it to the Sql code which was generated when I created a
query by design mode. But somehow it doesn't seem to be working. These
are the errors I'm coming up with:

1) characters found after end of sql statement
2) syntax error (missing operator) in query expression
'CountryCodes.CountryCodes=EconomicsectorNumberMas ter.country
UPDATE csv_importable_data_final_economicsector.Partner_I D='Z'.

Attached below is the full code I'm using (I added the last two lines
you suggested). I must admit I'm a beginner, what am I doing wrong
here? Also, how would this be done using the query grid? Thanks.

Brian

SELECT IndicatorCodes.indicatorcode, CountryCodes.CountryCode,
EconomicsectorNumberMaster.year,
SourceDataLookupEconomicsector.sourcecode,
EconomicsectorNumberMaster.value
FROM CountryCodes INNER JOIN (IndicatorCodes INNER JOIN
(SourceDataLookupEconomicsector INNER JOIN (EconomicsectorNumberMaster
INNER JOIN Q_SeriesSelection ON (EconomicsectorNumberMaster.country =
Q_SeriesSelection.country) AND (EconomicsectorNumberMaster.year =
Q_SeriesSelection.year) AND (EconomicsectorNumberMaster.sourcecode =
Q_SeriesSelection.source) AND (EconomicsectorNumberMaster.subgroup =
Q_SeriesSelection.subgroup)) ON
SourceDataLookupEconomicsector.sourcecode = Q_SeriesSelection.source) ON
(IndicatorCodes.subgroup = EconomicsectorNumberMaster.subgroup) AND
(IndicatorCodes.gender = EconomicsectorNumberMaster.gender) AND
(IndicatorCodes.measurement = EconomicsectorNumberMaster.measurement)
AND (IndicatorCodes.indicator = EconomicsectorNumberMaster.indicator))
ON CountryCodes.CountryCodes = EconomicsectorNumberMaster.country
UPDATE csv_importable_data_final_economicsector.Partner_I D='Z'
WHERE (csv_importable_data_final_economicsector.Partner_ ID is null);

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

P: n/a
Hi Jeff and Fred,

Thanks for the suggestions. I tried the code you suggested, Jeff, and
somehow I must have not put it in right because I'm coming up with some
errors.

What I did is to go into the query which creates the table where I'd
like to copy "unknown" into the field. I then went into sql code for
the query and added your two lines of code after the sql code which had
been previously generated from making the query. I removed the
semi-column that was previously at the end of all the code and put it at
the end of your code.

But still am coming up with errors, and the word "unknown" is not
copying into the field. What am I doing wrong?

Also, you mentioned it could also be done through the grid page. How
would this be done exactly? (I must admit I'm a beginner at this so not
quite sure where to look.) Thank you.

Brian
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

P: n/a
On 30 Oct 2004 05:07:30 GMT, brian kaufmann wrote:
Hi Jeff and Fred,

Thanks for the suggestions. I tried the code you suggested, Jeff, and
somehow I must have not put it in right because I'm coming up with some
errors.

What I did is to go into the query which creates the table where I'd
like to copy "unknown" into the field. I then went into sql code for
the query and added your two lines of code after the sql code which had
been previously generated from making the query. I removed the
semi-column that was previously at the end of all the code and put it at
the end of your code.

But still am coming up with errors, and the word "unknown" is not
copying into the field. What am I doing wrong?

Also, you mentioned it could also be done through the grid page. How
would this be done exactly? (I must admit I'm a beginner at this so not
quite sure where to look.) Thank you.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


What you have done is incorrect.
An Update query is separate from a Select query and they cannot be
combined.
After you have manually added your new field (Text Datatype) to the
table in table design view, create a NEW query. You can use Jeff's SQL
to create and run the Update.

Paste this into the query SQL window.

UPDATE YourTable SET YourTable.YourNewField = "Unknown";

.... changing the table and field names to the actual names used.
Since you are adding the word "unknown" to each record, there is no
need to use a Where clause. The above is the entire SQL.

I'm almost afraid to ask why you need to do this.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #6

P: n/a
What both Fred and myself have omitted is that you have to execute (run)
this query. Just viewing an update query in Datasheet view will not update
the table. Goto Query>Run from the menu when in SQL view or Design view or
click the red explanation mark on the toolbar and click yes to the messages
that pop up.

Jeff
"fredg" <fg******@example.invalid> wrote in message
news:gz***************************@40tude.net...
On 30 Oct 2004 05:07:30 GMT, brian kaufmann wrote:
Hi Jeff and Fred,

Thanks for the suggestions. I tried the code you suggested, Jeff, and
somehow I must have not put it in right because I'm coming up with some
errors.

What I did is to go into the query which creates the table where I'd
like to copy "unknown" into the field. I then went into sql code for
the query and added your two lines of code after the sql code which had
been previously generated from making the query. I removed the
semi-column that was previously at the end of all the code and put it at
the end of your code.

But still am coming up with errors, and the word "unknown" is not
copying into the field. What am I doing wrong?

Also, you mentioned it could also be done through the grid page. How
would this be done exactly? (I must admit I'm a beginner at this so not
quite sure where to look.) Thank you.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


What you have done is incorrect.
An Update query is separate from a Select query and they cannot be
combined.
After you have manually added your new field (Text Datatype) to the
table in table design view, create a NEW query. You can use Jeff's SQL
to create and run the Update.

Paste this into the query SQL window.

UPDATE YourTable SET YourTable.YourNewField = "Unknown";

... changing the table and field names to the actual names used.
Since you are adding the word "unknown" to each record, there is no
need to use a Where clause. The above is the entire SQL.

I'm almost afraid to ask why you need to do this.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 13 '05 #7

P: n/a
Hi Jeff and Fred,

Thanks for that, it worked. Something related, I now have the basic
structure for the main query I'll be running. The only thing is I have
to run it on about 20 sets of data, and the datatable names are all
alittle different. Is there a way to be able to keep the structure of
the query (relationships stay the same), but to just change the table
names each of the 20 times.

For example, each run of the 20 times involves 3 tables for which the
core names are:
1) maintable
2) selectionseries
3) lookuptable

For each of the runs, the table names are slightly different as there is
a descriptor attached such as:
maintable_labourforce
selectionseries_labourforce
lookuptable_labourforce

The part after the _ differs from labourforce to employment, etc.

If I have the structure and query set for one of these, how can I run
the same query over again 20 times without having to recreate the query
each time and using the correct table names?

Thanks.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

P: n/a
brian kaufmann <br************@hotmail.com> wrote in message news:<41**********************@news.newsgroups.ws> ...
Hi Jeff and Fred,

Thanks for that, it worked. Something related, I now have the basic
structure for the main query I'll be running. The only thing is I have
to run it on about 20 sets of data, and the datatable names are all
alittle different. Is there a way to be able to keep the structure of
the query (relationships stay the same), but to just change the table
names each of the 20 times.

For example, each run of the 20 times involves 3 tables for which the
core names are:
1) maintable
2) selectionseries
3) lookuptable

For each of the runs, the table names are slightly different as there is
a descriptor attached such as:
maintable_labourforce
selectionseries_labourforce
lookuptable_labourforce

The part after the _ differs from labourforce to employment, etc.

If I have the structure and query set for one of these, how can I run
the same query over again 20 times without having to recreate the query
each time and using the correct table names?

Thanks.

Brian


Uh oh. The Tell-tale heart! I mean tell-tale sign of incorrect
design. Lots of tables with similar/the same structures. *normally*,
you would have the query based on one table and change the parameters
are runtime, but since this isn't set up quite right, things aren't
going to be that easy.

What are you doing with the resultant datasets? Export them? Send them
to Excel?
Base reports on them?

You might want to import all the data into a single table with a flag
so that you know where the data came from, and then you can just run a
normal parameterized query on the table. If your indices are set up
right, it should be pretty quick and easy. You'd just {specify your
parameters, open the query, process the data} and repeat that as many
times as necessary... especially easy if the arguments are stored in a
table. You could open a recordset based on the table, loop through
it, and process away.

But the way you have it, you'd have to do something like create a
dummy query with a dummy tablename in it. then grab the qry.SQL, run
a REPLACE on it, save the querydef, and execute it. Then do something
with the results. Then go to the next one. Off the top of my head,
I'd create a form with a multi-select listbox of all the non-system
tables in the database, and loop through them and process inside the
loop.

Complicated? Kinda, but it would work with your design. Personally,
if it weren't a huge hassle, I'd rethink the design before doing
anything else. It could be that you're heading for worse problems and
this is just the beginning.

Sorry to be such a wet blanket, but if you need this database to work
easily, I think you're going about it the wrong way.
Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.