Efficient query using Union

[evergrean100]

I have an Access 2003 database with these example values:

id   NameOne    NameTwo    city
1    Jones      Carson     San Diego
2    Baker      Jones      Oakland
3    Nimmons    Smith      Los Angeles
4    Jones      Carson     San Francisco

I am trying to fetch the distinct names with their city/cities output:
Jones -> San Diego, Oakland, San Francisco
Carson -> San Diego, San Francisco
Baker -> Oakland
Nimmons -> Los Angeles
Smith -> Los Angeles

My attempt below seems very long and cant seem to put the city field in my query because it gives me errors saying its not part of aggregate function. I also tried UNION ALL and that didnt work. Please advise.

select distinct NameOne, max(TableOne.id) as mydata
from TableOne
group by NameOne
UNION
select distinct NameTwo, max(TableOne.id) as mydata2
from TableOne
group by NameTwo

 

[dhookom]

You "I am trying to fetch..." looks like the city names should be all returned in a single expression for each name. Is that correct or do you want one city per name per record. What do you get if you try:

SELECT NameOne as TheName, City
FROM tableOne
UNION
SELECT NameTwo, City
FROM TableOne;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[nickdel]

evergrean, if you have a set number of cities then I would recommend doing it this way:

SELECT NameOne, NameTwo,
sum(iif(city='San Diego',1,0)) as [San Diego],
sum(iif(city='Oakland',1,0)) as [Oakland],
sum(iif(city='Los Angeles',1,0)) as [Los Angeles],
sum(iif(city='San Francisco',1,0)) as [San Francisco]
FROM TableOne
Group By NameTwo,NameOne

This will then in effect "flatten" the results and show you something like this:

NameTwo San Diego Oakland ... San Francisco
Carson 1 0 1
Jones 0 1 0

And so on...

Hope this helps!

Cheers

Nick