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

Adding a field to an existing table using a query

P: n/a
GL
Hi,

Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have a specific need
to only add a new field to a table if possible. Here's a simplified
example of what I'm trying to do:

I get a file with the following two fields:
First Name
Last Name

I want to add a 3rd field onto that table to hold the 1st 5 characters
of the Last Name:
First Name
Last Name
1st 5 characters of Last Name

Can this be done without using a make table query and without manually
adding a field to the table & then using an update query?

Thanks,
G
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
G, If there a reason you are trying to store this value. Why not simply design a query based on
this table. Add a column to your query and type the following on the Field Row:

NewLastName: Left([LastName],5)
Hope this helps!
--
Reggie

----------
"GL" <81*****@comcast.net> wrote in message news:4b**************************@posting.google.c om...
Hi,

Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have a specific need
to only add a new field to a table if possible. Here's a simplified
example of what I'm trying to do:

I get a file with the following two fields:
First Name
Last Name

I want to add a 3rd field onto that table to hold the 1st 5 characters
of the Last Name:
First Name
Last Name
1st 5 characters of Last Name

Can this be done without using a make table query and without manually
adding a field to the table & then using an update query?

Thanks,
G

Nov 13 '05 #2

P: n/a
81*****@comcast.net (GL) wrote in message news:<4b**************************@posting.google. com>...
Hi,

Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have a specific need
to only add a new field to a table if possible. Here's a simplified
example of what I'm trying to do:

I get a file with the following two fields:
First Name
Last Name

I want to add a 3rd field onto that table to hold the 1st 5 characters
of the Last Name:
First Name
Last Name
1st 5 characters of Last Name

Can this be done without using a make table query and without manually
adding a field to the table & then using an update query?

Thanks,
G


No, you cannot modify a table with a normal query. You would need to
use code to add a column/field to your table. But if all you want is
the first five characters in the field, what is wrong with using
something like LEFT$(LastName,5) and then querying that?
Nov 13 '05 #3

P: n/a
If the new field should always be the first 5 characters of the surname, you
should not create this field in the table. Instead, do it in a query, by
entering this into a fresh column of the Field row in query design:
FirstFive: Left([Last Name], 5)

If you need to be able to sometimes assign a value other than the first 5
characters, use a Data Definition Language (DDL) query statement to add a
field to your table, and then an Update query to populate the field.

This code executes the DDL query:
Dim strSql As String
Dim db As DAO.Database

Set db = CurrentDb()
strSql = "ALTER TABLE MyTable ADD COLUMN MyNewTextField TEXT (5);"
db.Execute strSql, dbFailOnError
Set db = Nothing

For help with the Update query:
1. Create a new query into the modified table.
2. Change it to an Update query (Update on Query menu).
3. In the Update row under MyNewTextField, enter:
Left([Last Name], 5)
4. Run the query, or switch to SQL View (View menu) and copy what you see if
you want to execute it in code.

Unfortunately, the DDL query statement does not set AllowZeroLength to No in
recent versions, as it did in earlier versions. You will need to use DAO
code to modify this property, so you may prefer to use CreateField() instead
of the DDL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"GL" <81*****@comcast.net> wrote in message
news:4b**************************@posting.google.c om...
Hi,

Is there a way to add a field to an existing table using a query of
some sort (without needing to manually add a field to the table). I
know how to do it with a make table query, but I have a specific need
to only add a new field to a table if possible. Here's a simplified
example of what I'm trying to do:

I get a file with the following two fields:
First Name
Last Name

I want to add a 3rd field onto that table to hold the 1st 5 characters
of the Last Name:
First Name
Last Name
1st 5 characters of Last Name

Can this be done without using a make table query and without manually
adding a field to the table & then using an update query?

Thanks,
G

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.