[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

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
0
Rowby Goren
Asked:
Rowby Goren
2 Solutions
 
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
 
carlsiyCommented:
theres an SQL view in access... under the queries option
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now