0

Am trying to access 2 tables values to single query. For Eg Customer name, id, Address to a single query. Customer address - State & Country in another table Am trying to access all address to a single column in the query. In Customer table state and country code only stored. Now I can able to access Customer name, Id, Address(Single column) all in query but cont able to access that State & Country.

SELECT CustomerName, CustomerId,  (BFlatNo +','+ BPremises +','+ BStreet +','+ BArea +','+ BLocation +','+ BCity +','+ BState +','+ BCountry)  as Address FROM Customer WHERE CustomerId=11;

this code working successful & got result this enter image description here Am getting 24 and 1 are id's of State & Country.

so Again I modify this code to get exact address in a single column state delhi instead off 24 and country india instead of 1 

SELECT CustomerName, CustomerId,  (BFlatNo +','+ BPremises +','+ BStreet +','+ BArea +','+ BLocation +','+ BCity +','+ BState +','+ (select CountryName from Country where CountryIdId=Customer.Country) AS Country)  AS Address FROM Customer;

this code showing syntax error ! How to solve this ?

Improve this question

4 Answers 4

Reset to default
2

If you want to SELECT from multiple tables then include the tables in the FROM clause or use a JOIN.

SELECT CustomerName,
       CustomerId,
       (BFlatNo & ',' & BPremises & ',' & BStreet & ',' & BArea & ',' & BLocation & ',' & BCity & ',' & BState & ',' & CountryName) AS Address
FROM   Customer
       INNER JOIN
       Country
       ON Country.CountryId = Customer.Country;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Why no join? The concatenator in MS Access is & not +, you will run into various problems with nulls if you use the wrong concatenator.
Inner join and where are mostly analogous. I'm unsure of whether the MS Access query planner will create similar execution plans, but in general they should have nearly the same performance. Nice tip about the & concatenator.
Joins are nearly always better with Access.
1

I'm not sure about MS Access syntax vs. SQL Server, but you can give this a shot:

SELECT 
CustomerName, 
CustomerId,  
(
(BFlatNo +','+ BPremises +','+ BStreet +','+ BArea +','+ BLocation +','+ BCity +','+ BState)
+','+
(select top 1 CountryName from Country where CountryIdId=Customer.Country)
)
AS Address 

FROM Customer;

Basically you don't need to say "as Country" as you're doing in the subquery, and you should return the top 1 result because if there are more results this will cause a problem.

Improve this answer

7 Comments

Hi, I Used this code working successful but I need to filter single customer so I tired where condition but getting error on this code after Address
WHERE NewInvoice_1.CustomerId=Customer.CustomerId FROM Customer;
FROM Customer WHERE NewInvoice_1.CustomerId=Customer.CustomerId;
where clause after from clause, not sure what newinvoice_1 is though
haa small mistake cont find in stress. Thanks.
|
1

Your Query to solve your problem

SELECT CustomerName, CustomerId, (BFlatNo +','+ BPremises +','+ BStreet +','+ BArea +','+ BLocation +','+ BCity +','+ BState +','+ (select CountryName from Country where CountryIdId=Customer.Country) ) AS Address FROM Customer;

Improve this answer

Comments

1

You should do like this

SELECT A.CustomerName,
   A.CustomerId,
   (A.BFlatNo + ',' + A.BPremises + ',' + A.BStreet + ',' + A.BArea + ',' + A.BLocation + ',' +    A.BCity + ',' + A.BState + ',' + B.CountryName) AS Address
 FROM   Customer A, Country B 
 WHERE  B.CountryId = A.Country;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.