I have an Access database being used for a company locator which consists of several hundred company names with address, city, state, zip, and phone for each. Using a .asp page a user can search by company name which returns all locations of that company (some companies have multiple locations throughout the US and some have just one). This works fine. My table structure:
tblCompanyID
CompanyID
CompanyName
(this table houses each company with a unique identifier)
tblLocation_Details
LocationID
CompanyID
CompanyName
Address
City
State
etc
The first page of results for a search by city or state should return only one CompanyID. These unique companies should link to detail pages which would include all locations within the search parameter for that CompanyID. The logic is, one company can have as many as 1700 locations and the companies with 1 location are getting buried. This is a solution to give equal weight to all companies in the first search results screen.
What I would like to do is when searching by
city or state, I'd like those companies with multiple locations in that city or state to appear only once in the returned list. It doesn't matter which of the multiples is returned. E.g.,
If for the state of CA there are:
Company A
Company B
Company C1
Company C2
Company C3
Company D
...I would like the results page to display:
Company A
Company B
Company C1
Company D
I have tried using SELECT DISTINCT CompanyName, but that only returns the company name. Is there a way that I can also return the City and State for that DISTINCT CompanyName.
Thank you in advance for any help you can offer.