Merging 2 columns into one with Access SQL

Dear Experts,

I have the following table:

Sup_ID  Sup_Cmpny_Nme  Cntct_Fnme  Cntct_Lnme  Adres_1    Adres_2  Adres_3
1001       J&J                       Jimmy          Johnson      105 W. Erie  Suite 1   Chicago  


Adres_4    Adres_5
IL              60005


How can I create a query with Access SQL to return me a report like this

Contact_Name         Sup_Cmpny_Nme     Address
Johnson, Jimmy          J&J                     105 W. Erie, Suite 1, Chicago, IL 60005


Thanks!
Mars0178Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
Select Cntct_Lnme & ", " & Cntct_Fname AS ContactName, Sup_Cmpny_Nme, Adres_1 & ", " & Adres_2 & ", " & Adres_3 & ", " & Adres_4 & " " & Adres_5 AS Address FROM tblName
jmantha709Commented:
Try:

SELECT  Cntct_Lnme  + ', ' + Cntct_Fnme  AS Contact_Name, Sup_Cmpny_Nme , Adres_1 + ', ' + Adres_2 + ', ' + Adres_3 + ', ' + Adres_4 + ' ' + Adres_5 AS Address FROM MyTable
Mars0178Author Commented:
sirbounty,

I get a syntax error message with your suggestion.

Jmantha,

when I run the query, I get a window asking me to enter a parameter value for Contact F_name, If I click on ok, I get another window asking me to enter a parameter value for Contact_Lname.

Can you fix this?

Thanks
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jmantha709Commented:
Did you write this textually (Except for MyTable) ?

SELECT  Cntct_Lnme  + ', ' + Cntct_Fnme  AS Contact_Name, Sup_Cmpny_Nme , Adres_1 + ', ' + Adres_2 + ', ' + Adres_3 + ', ' + Adres_4 + ' ' + Adres_5 AS Address FROM MyTable

There's no place that it says Contact F_name or Contact_Lname in that sql...  Unless you replaced MyTable, with another query and that query is generating the error...
jmantha709Commented:
Did you make a new query or edited one already existing ?  If it already existed, try a brand new query...
sirbountyCommented:
What's the error and where does it occur?
tblName would be the name of your table..since you didn't post one, I used tblName as a substitute...
Mars0178Author Commented:
It's not really an error message, but after I run the query, I get a pop up window asking me to ENTER A PARAMETER VALUE for Contact_Lname, if I hit ok I get another Window asking to ENTER A PARAMETER VALUE for Contact_Fname.

I have modified the syntax by replacing it with the correct field names that my table has and I did change the MyTable name to the name of the table I have.  Here are the results I get:

Contact_Name    Sup_Company_Name                     Address
            Wilson Sporting Inc. 1001 W. Johnson Dr., Suite 100, Springfield, MO 65804
                Lake Tahoe Sport      
                A.J. Sport      
        Golf Discount of Springfield      11025 Vanguard Dr., Suite 55, Sprinfield, IL 85850
      Mathews Sporting      
      Jimmy Johnson Used Sport Equipment      
      Magic Sport Equip      
      Commando Sport      
     K.C. Sporting Inc.      12120 E. 158th Street, Suite 101, Kansas City, KS 66661
      Sporting Hut      


As you can see not all of the Suppliers came back with Address or Contact Names.

System_ExpertCommented:
Hello I use Supplier as Table Name
if particular field is going to be blank then use
iif(condition,truepart,falsepart)

ok

SELECT (Cntct_Fnme+ ' ' + iif(isnull(Cntct_Lnme)=true,'',Cntct_Lnme )) as Contact_Name,
Sup_Cmpny_Nme ,
(iif(isnull(Adres_1)=true,'',Adres_1) + ' ' + iif(isnull(Adres_2)=true,'',Adres_2)+ ' ' + iif(isnull(Adres_3)=true,'',Adres_3)) as Address
FROM Supplier
Mars0178Author Commented:
System Expert,

The results shouldn't come back with empty results, The tables where the data is have all those fields filled with data, that's why I don't understand why the query returns empty fields....does that mean the syntax is incorrect?
sirbountyCommented:
In Access, this should work:
Select Cntct_Lnme & ", " & Cntct_Fname AS ContactName, Sup_Cmpny_Nme, Adres_1 & ", " & Adres_2 & ", " & Adres_3 & ", " & Adres_4 & " " & Adres_5 AS Address FROM tblName

So, please try the following sub-portions and report back the results:

Select Cntct_Lnme & ", " & Cntct_Fname FROM tblName
Select Cntct_Lnme FROM tblName
Select Count(Cntct_Lnme) FROM tblName  How many?

The field names have to be correctly duplicated.  Your initial post was field Cntct_Fnme but then you report that it's asking you for Contact_Fname.  What is that field name?

You should easily be able to concatenate any two or more fields by using
SELECT Field1 & " " & Field2 FROM table...

I don't suppose you have a way of posting the database?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
System_ExpertCommented:
e.g
adres_1='203, Hilton '
adres_2='NY'
adres_3=Null or say ''

if you just right
(adres_1 + ' ' + adres_2 + ' ' + adres_3 ) as Addredd

then blank result will come

that's why iif(isNull(),,) i used and it work properly
 check it
Bye
Mars0178Author Commented:
Ok Expert,

I used yours works but I am missing commas to separate the last name from the first name, and commas separating street address from city, state, zip code....also I tried sorting the table by Contact Name by using Order By and it didn't work for me, what's the appropriate syntax for it sort by Asc on Contact Name field.

Thanks
Mars0178Author Commented:
Sirbounty,

I don't know what the hell I did wrong, but I got your last message posted and it worked.  It came back with the results I need...I am sorry and thank you!
System_ExpertCommented:
Contact_Name contains two Field =Cntct_Fnme and
Cntct_Lnme

So Try

Order by (Cntct_Fnme  + ' ' + Cntct_Lnme )

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.