Syntax error in MS Access Query?

Question

I combined 7 columns into single column that is all address into a single column. Initially Location, City, State & Country are stored code its display value in that corresponding tables. So I joined all in this query below.

SELECT CustomerName, CustomerId, ContactPerson, 

((BFlatNo +','+ BPremises +','+ BStreet +','+ BArea) + ',' +
(select LocationName from Location where LocationId = Customer.Location) + ',' +
(select CityName from City where CityId = Customer.City) + ',' +
(select StateName from State where StateId = Customer.State) + ',' +
(select CountryName from Country where CountryId = Customer.Country) + ',' + 
(BMobileNumber) ) AS BillingAddress, 

((DFlatNo +','+ DPremises +','+ DStreet +','+ DArea) + ',' +
 (select LocationName from Location where LocationId = Customer.Location) + ',' +
 (select CityName from City where CityId = Customer.City) + ',' +
 (select StateName from State where StateId = Customer.State) + ',' +
 (select CountryName from Country where CountryId = Customer.Country) + ',' + 
(DPhone) ) AS DeliveryAddress

FROM Customer
WHERE Customer.CustomerId = 11;

While previewing Datasheet view in MS Access it asking for Customer.Location, Customer.City, Customer.State & Customer.Country then it showing result for CustomerId=11

enter image description here

I need to display Customer details when I click Datasheet View. Actually in Customer table Location its fieldname is BLocation, City is BCity, State is BState & Country is BCountry but If I give Select LocationName from Location where LocationId=Customer.BLocation it showning syntax error.

Help me how to solve this ?

Too many subqueries. Use JOINs instead. Start with the visual query designer, and work your way up to actual, hand-written queries. — Robert Harvey
– Robert Harvey, Commented Feb 1, 2014 at 5:56
I'd even consider creating a function in VBA that builds the full address when passed the 7 text elements of an address. It's not especially fast but I've found it easier to maintain and handle blanks than creating a really messy expression in the SQL query. — Matt Weller
– Matt Weller, Commented Feb 1, 2014 at 13:07

Srihari · Accepted Answer · 2014-02-14 07:37:02Z

2

thank you guys This code working fine

SELECT CustomerName, CustomerId, ContactPerson,

((BFlatNo +','+ BPremises +','+ BStreet +','+ BArea)+','+
(select LocationName from Location where LocationId=Customer.BLocation)+','+
(select CityName from City where CityId=Customer.BCity)+','+
(select StateName from State where StateId=Customer.BState)+','+
(select CountryName from Country where CountryId=Customer.BCountry)+','+ (BMobileNumber)) AS BillingAddress,

((DFlatNo +','+ DPremises +','+ DStreet +','+ DArea)+','+
(select LocationName from Location where LocationId=Customer.DLocateion)+','+
(select CityName from City where CityId=Customer.DCity)+','+
(select StateName from State where StateId=Customer.DState)+','+
(select CountryName from Country where CountryId=Customer.DCountry)+','+ (DPhone)) AS DeliveryAddress

FROM Customer;

edited Feb 14, 2014 at 7:37

answered Feb 3, 2014 at 5:56

Srihari

2,4319 gold badges56 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in MS Access Query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related