update Query written


B

Bikini Browser

Can anyone please help me? I need an update Query written and I don't know
how to do it.

Update Query needed...

I want to take 4 existing columns of data (names) in one table and append
them all into one existing column (Fullname column) in the same table.
However if one of the 4 "NAME" columns is NULL, then don't make 2 spaces
between missing data. Each name field in the full name field.

Here are my columns"

Salutation FirstName LastName SecondLastName
Mr. James Jones

Should come out like this.. Mr. James Smith Jones (There should never be
two Spaces between Names.

Versus Mr. James Jones Where there are two Spaces between James and Jones
 
Ad

Advertisements

B

bmarland

update [table to update]
set Fullname = replace(trim([salutation] & " " & [FirstName] & " " &
[LastName] & " " & [SecondLastName]), " ", " ")
 
J

John Spencer

Well, I wouldn't build the full name except in a query when I was going to
use it.

FullName: (Salutation + " ") & (FirstName + " ") & LastName & (" " +
SecondLastName)

This takes advantage of the way Access concatenates text with the two
diffierent operators. Null + something will return null. Null & Something
will return Something.

IF your fields contain zero-length strings instead of null, this won't work.
But you can use the IIF function to achieve similar results

FullName: Salutation & IIF(Len(Salutation & "") >0," ","") & FirstName &
IIF(Len(FirstName & "")>0," ","") & ...
 
G

Guest

You do NOT want to store another field in the table with this information.
What if someone gets married and changes her last name? In that case you'll
have to remember to change it in two fields and not just one. One rule of
database normalization is to not store the same data in more than one filed.

Instead you want to put something like below in a query, form, or report
each time that you need the full name. The trim function at the beginning
will remove any leading or trailing spaces if there isn't a Salutation or
SecondLastName. The Trim just before LastName will get rid of that space if
there isn't a last name. Notice the "))" after the SecondLast Name.

FullName: Trim(Salutation & " " & FirstName & " " & Trim(LastName) &" " &
SecondLastName))

The ampersand "&" is how you combine two or more fields. To put a space
between them you need to do something like & " " & .
 
B

Bikini Browser

Its ok, I will run the query everytime before I am going to use the data
from the new field.


Dale
 
B

Bikini Browser

That query does not work. It Pops up a window and Asks me for firstname.

Whats wrong?

Dale


update [table to update]
set Fullname = replace(trim([salutation] & " " & [FirstName] & " " &
[LastName] & " " & [SecondLastName]), " ", " ")


Can anyone please help me? I need an update Query written and I don't
know
how to do it.

Update Query needed...

I want to take 4 existing columns of data (names) in one table and append
them all into one existing column (Fullname column) in the same table.
However if one of the 4 "NAME" columns is NULL, then don't make 2 spaces
between missing data. Each name field in the full name field.

Here are my columns"

Salutation FirstName LastName SecondLastName
Mr. James Jones

Should come out like this.. Mr. James Smith Jones (There should never be
two Spaces between Names.

Versus Mr. James Jones Where there are two Spaces between James and
Jones
 
Ad

Advertisements

B

Bikini Browser

How do you make that work? I need to copy and paste adn your query does
not look complete. Can you make it so I can copy and paste it?

Dale
 
G

Guest

Please don't. Again you should put in the FullName statement below any time
that you are going to use it in a form, report, or query. It is the right way
to do things in a relational database management system. Storing derived data
in a table is almost always wrong.
 
B

Bikini Browser

Thanks. I know what is wrong now. My mistake. Thanks for the correction.

Dale
 
C

Chris2

Bikini Browser said:
That query does not work. It Pops up a window and Asks me for firstname.

Whats wrong?

Dale

Dale,

In your first post of this thread, you specified a column with a
name of FirstName.

The only way a parameter dialog box would appear is if there is no
column named FirstName, or if a typo had been introduced to that
part of the text of the query you copied and pasted.


Sincerely,

Chris O.
 
Ad

Advertisements

S

Smartin

Bikini said:
Its ok, I will run the query everytime before I am going to use the data
from the new field.


Dale

All the more reason not to store the full name in a table. Create a
query to construct the full name, and use the query wherever you need
the full name. That way you don't have to remember to do X before Y...
It's easier, really! (^:
 
Ad

Advertisements

J

John Spencer

What I posted was not a query. It was an expression that you could use in a
query to return values in a column (field).

In the query grid it would look something like

Field: FullName: (Salutation + " ") & (FirstName + " ") & LastName & (" " +
SecondLastName)

If you want to do this as an update query, then you would need
-- a field already defined to hold the data
-- something like the following.

UPDATE [YourTableName]
Set [FullNameField] = ([Salutation] + " ") & ([FirstName] + " ") &
[LastName] & (" " + [SecondLastName])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top