CODEWITHSUNDEEP
sqlwinformsms-access
Srihari
Srihari

Reputation: 2429

Syntax error in MS Access Query?

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 ?

Upvotes: 2

Views: 105

Answers (1)

Srihari
Srihari

Reputation: 2429

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;

Upvotes: 2

Related Questions