I have a simple query which I am using to retrieve the first record found for each post code from a customer database. It doesn’t matter which of say 5 records for a postcode is returned but it must return one and only one.
I have tried using the first() function but I am either using it incorrectly or it is the wrong way to do what I need.
SQL used is as follows:
SELECT tblCustomer.[Post Code], [First Name]+" "+[Surname] AS FullName, tblCustomer.Street, tblCustomer.Town, tblContract.CustomerID, tblCustomer.County, tblCustomer.Latitude, tblCustomer.Longitude, "England" AS Country
FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID
WHERE (((tblContract.CustomerID)>1) AND ((tblCustomer.Latitude)>52.00514 And (tblCustomer.Latitude)<52.51912) AND ((tblCustomer.Longitude)>-0.37281 And (tblCustomer.Longitude)<1.01))
ORDER BY [First Name]+" "+[Surname];
Any help would be appreciated. Many thanks.
I have tried using the first() function but I am either using it incorrectly or it is the wrong way to do what I need.
SQL used is as follows:
SELECT tblCustomer.[Post Code], [First Name]+" "+[Surname] AS FullName, tblCustomer.Street, tblCustomer.Town, tblContract.CustomerID, tblCustomer.County, tblCustomer.Latitude, tblCustomer.Longitude, "England" AS Country
FROM tblCustomer INNER JOIN tblContract ON tblCustomer.CustomerID = tblContract.CustomerID
WHERE (((tblContract.CustomerID)>1) AND ((tblCustomer.Latitude)>52.00514 And (tblCustomer.Latitude)<52.51912) AND ((tblCustomer.Longitude)>-0.37281 And (tblCustomer.Longitude)<1.01))
ORDER BY [First Name]+" "+[Surname];
Any help would be appreciated. Many thanks.