How to combine 2-3 columns in Access 2003 and how to add a column

Hi

I have an access file that includes names and addresses.

THe addresses are in separate columns ie   111          North              Orange      Street

I need to combine them into one column.

Also how to I add a column in Access.  (I am very ignorant in Access).

Finally if a date is in a "wrong format' how can it be converted to this format YYYYMMDD.

Thanks

P.S.  WHere in Access 2003 are the templates stored, and what is the extension?



Rowby
LVL 9
Rowby GorenAsked:
Who is Participating?
 
lynchrichard72Commented:
Hi Rowby

one way is to create edit the table that contains the addresses and add a new field for the combined data eg "address1" as a text filed.

Then create an update query to update the address1 based on data from the other fields seperated with a space"


In access under queries select create query by using wizard.
Select the table all the fields with the parts that need to be combined as well as the target field that will store the combined data.
on the last wizard prompt select modify the query design and finish
from the top menu, select Query | Update query
For the filed that will store the combined string  enter in the 'update to' something like the following where A B C D are the source fields seperated by a space " "
[Table1]![A]+" "+[Table1]![B]+" "+[Table1]![C]+" "+[Table1]![D]

run the query by clicking on the red ! button at the top on the menu bar.  it will then report how many fields were updated and store the results in the same table.

please see attached jpg of screen capture for example

for date value format you can refer to this site.
http://office.microsoft.com/en-us/access/HP010990151033.aspx

Also many windows applications date format will be the same as windows system date settings in control panel | regional and language options.  Check the windows system has the same format you short and long date format you require.

regards,
Richard


update-query.JPG
0
 
carlsiyCommented:
for the combination of columns in sql

select column1 +' ' + column2 +' '+ column3
from
tablename
0
 
Rowby GorenAuthor Commented:
Hi carlsiy,

I only have access to work in, cannot use sql.

Rowby
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
carlsiyCommented:
theres an SQL view in access... under the queries option
0
 
Jeffrey CoachmanMIS LiasonCommented:
rowby,

For the record you really have three separate questions here.
You are only allowed one question per post, see here:
http://www.experts-exchange.com/help.jsp#hi23
(However, I wil forgive you if you, if you were under the influence of the Queen of Outer Space.)
;-)

1. In Access the symbol to "Concatenate is:  &
So you would use something like this:
    [Table1]![A] & " " & [Table1]![B] & " " & [Table1]![C] & " " & [Table1]![D]

2. If by "Add" a column, you mean you want to get a "Sum" of the entire column, use something like this:
SELECT Sum(YourField) AS SumYourField
FROM YourTable;

3. If a date is in the wrong format, you can change it to  YYYYMMDD by using something like this:
NewFormatDate = Format(YourDate, "YYYYMMDD")

I hope this clears up all three questions
;-)

Jeff
0
 
Rowby GorenAuthor Commented:
Thanks, and yes, boag2000 (we can blame it on the Queen of Outer Space)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.