Solved

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

Posted on 2008-10-07
6
897 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now