Solved

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

Posted on 2008-10-07
6
902 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:Rowby Goren
6 Comments
 
LVL 6

Expert Comment

by:carlsiy
ID: 22665074
for the combination of columns in sql

select column1 +' ' + column2 +' '+ column3
from
tablename
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 22665092
Hi carlsiy,

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

Rowby
0
 
LVL 6

Expert Comment

by:carlsiy
ID: 22665485
theres an SQL view in access... under the queries option
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 1

Accepted Solution

by:
lynchrichard72 earned 300 total points
ID: 22666148
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 22692282
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
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 31504068
Thanks, and yes, boag2000 (we can blame it on the Queen of Outer Space)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question