Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-07
6
Medium Priority
?
912 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 1

Accepted Solution

by:
lynchrichard72 earned 1200 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 800 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

Technology Partners: 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

824 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