Add column with data to an existing table

[Joe M.]

I want to add a column to an existing table and make the new column data
based upon the data in another column already in the table. Such as:
Field1="ABCD"
NewField=Field1 & " "

How do I do this?

Thanks,
Joe M.

 

[George Hepworth]

You probably shouldn't.

One of the principles of sound database design is that you do not store
redundant data. You do not store values which can be calculated from other
values.

If you need this calculated value for some purpose, you can, and should, do
so in a query anytime it is required.

That said, there are situations, rare but not unheard of, where it is proper
to store data in what might appear to be redundancy. Explain what you are
trying to accomplish here, so we can offer further advice and suggestions.

George

 

[Arvin Meyer MVP]

Are you replacing Field1? If not, do not add a column, just update Field1
using an Update Query:

UPDATE YourTable SET Field1= Field1 & " "

If you are using a DDL query, you can use Alter Table to add a column:

ALTER TABLE YourTable ADD COLUMN Field2 TEXT;

 

[Joe M.]

George,

I have a table containing a column which in some cases is filled, in other
cases is null. I have tried unsuccessfully in my query to return the records
filled and null. The query receives its criteria values from a form. There
are multiple fields which the user may or may not enter selection data
depending upon the need. To make sure of results the fields which data is not
entered a wildcard (*) is entered by default. The astrick used in the query
criteria for the field containing the possible nulls only returns records
with the filled fields. So I am attempting to solve this by adding a column
which will contain the field with nulls and add " ". Changing the query to
this new column will return the correct results since now the nulls are
changed to " ".

Thanks,
Joe M.

 

[ken]

Joe:

There's no need to add an extra column; simply use:

WHERE NZ([YourColumn],"") LIKE "*"

To do this in query design view enter NZ([YourColumn],"") in the
'field' row of a blank column in the grid, uncheck the 'show' check
box and enter LIKE "*" in the 'criteria' row, substituting the real
column name of course.

Ken Sheridan
Stafford, England

 

[Joe M.]

Ken,

I followed your instructions but when entering * for all the criteria
parameters only the rows with the non-null fields are returned. Am I missing
something?

Thanks,
Joe M.

Joe:

There's no need to add an extra column; simply use:

WHERE NZ([YourColumn],"") LIKE "*"

To do this in query design view enter NZ([YourColumn],"") in the
'field' row of a blank column in the grid, uncheck the 'show' check
box and enter LIKE "*" in the 'criteria' row, substituting the real
column name of course.

Ken Sheridan
Stafford, England

George,

I have a table containing a column which in some cases is filled, in other
cases is null. I have tried unsuccessfully in my query to return the records
filled and null. The query receives its criteria values from a form. There
are multiple fields which the user may or may not enter selection data
depending upon the need. To make sure of results the fields which data is not
entered a wildcard (*) is entered by default. The astrick used in the query
criteria for the field containing the possible nulls only returns records
with the filled fields. So I am attempting to solve this by adding a column
which will contain the field with nulls and add " ". Changing the query to
this new column will return the correct results since now the nulls are
changed to " ".

Thanks,
Joe M.

 

[ken]

Joe:

I assume that you are either entering the asterisk in a control on a
form which the query references as a parameter along the lines of
Forms!YourForm!YourControl or in a system generated parameter prompt
such as [Enter a value or * for all records]. in which case the
criterion would be the parameter not a literal asterisk character:

Like Forms!YourForm!YourControl

However a better way to 'optionalize' the parameter is to test for OR
IS NULL. The parameter, whether its a control on a form or a simple
parameter generating a prompt would then be left blank (Null) rather
than defaulting to an asterisk, and the criterion for the actual
column in the query (not a computed column using the Nz function)
would be like this:

Forms!YourForm!YourControl OR Forms!YourForm!YourControl IS NULL

In SQL this would be a parenthesised expression in the WHERE clause:

([YourField] = Forms!YourForm!YourControl OR Forms!YourForm!
YourControl IS NULL)

If you enter this expression in the columns' criteria row in query
design view and then save the query, if you then open it again in
query design view you'll find that Access has moved things around.
The underlying logic will be the same, but you might find it difficult
making any further amendments to the query in design view. Its
generally better with a query with a complex WHERE clause to write
and save it in SQL.

Ken Sheridan
Stafford, England

Ken,

I followed your instructions but when entering * for all the criteria
parameters only the rows with the non-null fields are returned. Am I missing
something?

Thanks,
Joe M.

Joe:

There's no need to add an extra column; simply use:

WHERE NZ([YourColumn],"") LIKE "*"

To do this in query design view enter NZ([YourColumn],"") in the
'field' row of a blank column in the grid, uncheck the 'show' check
box and enter LIKE "*" in the 'criteria' row, substituting the real
column name of course.

Ken Sheridan
Stafford, England

 

[car]

You probably shouldn't.

One of the principles of sound database design is that you do not store
redundant data. You do not store values which can be calculated from
other values.

If you need this calculated value for some purpose, you can, and should,
do so in a query anytime it is required.

That said, there are situations, rare but not unheard of, where it is
proper to store data in what might appear to be redundancy. Explain what
you are trying to accomplish here, so we can offer further advice and
suggestions.

George

 

[car]

You probably shouldn't.

One of the principles of sound database design is that you do not store
redundant data. You do not store values which can be calculated from
other values.

If you need this calculated value for some purpose, you can, and should,
do so in a query anytime it is required.

That said, there are situations, rare but not unheard of, where it is
proper to store data in what might appear to be redundancy. Explain what
you are trying to accomplish here, so we can offer further advice and
suggestions.

George