MS Access: Subselect, order by and such.

Hi all,

I have this small problem that is bugging my to quitsville...

I have two tables (simplyfied here):

Company:
ID
Description

CompanyName:
CompanyID (foreign to ID above, many-to-one)
Name
FromYear

The Company table is self-explanatory.
The CompanyName table holds the different names a particular company have had since it started out.

In order to generate a list of company names for a given year, I do something like this (again simplified here):

SELECT co.ID, co.Description,
(SELECT top 1 cn.Name from CompanyName cn where cn.CompanyID=co.ID) as Name
FROM Company co
WHERE cn.FromYear >= [YEAR]
ORDER BY co.ID

[YEAR] is a parameter I supply.

My problem is that I would ideally like to have this list sorted by the company names, but alas one cannot sort by a subquery.
I have tried with inner and outer joins, group by, but I keep hitting a wall.

Please bear in mind that this is the simplified version, and this ties in with other tables, so re-structuring the tables is not an option.

If anyone has any ideas how to get around this, I would be sleeping a lot better! I am fairly routined at SQL and I cannot believe I cannot solve this minor thing. I guess it is just one of those things...

All the best...

Michael, thanks for your reply, but you missed a point:

The CompanyName table is many-to-one and and I only want one (thus the top 1) name from that, ie. the name that is relevant to the year (FromYear).

A simple join would give me loads of dublicate company IDs and all the names a company has had over time.

Jyde


Edited by Jyde - 04 February 2005 at 6:49am

Problem with DISTINCT is that it works on all the fields in the select statement, thus if I select only DISTINCT ID, it would work, though if I did SELECT DISTINCT ID, Name, it would give me distinct combinations, ie. all of them.

But you did give me a good idea though. I think I have tried, but will have a look.

If anyone has any other ideas, please keep them coming.

Thanks.