[Access] Select fields without grouping by them

[tsigo]

Let's say I have a table with these fields:

ID, LAST_NAME, FIRST_NAME, PROGRAM_CODE, PGM_YEAR

And there's at least one entry per person for each year they served. I'm trying to list people who have more than one PGM_YEAR row listed, and I need to list all of those fields in the resulting query.

So here's what I tried:

SELECT * FROM (
SELECT LAST_NAME, FIRST_NAME, Count(PGM_YEAR) AS YearsServed
FROM [Table]
GROUP BY LAST_NAME, FIRST_NAME, PGM_YEAR)
WHERE YearsServed > 1;

I also need to select PROGRAM_CODE but definitely don't want to group by that. Is there a way around that?

[kajakske]

If you do not want to group by it, then there can be only 1 for every
LAST_NAME, FIRST_NAME, PGM_YEAR
That can be a problem. 1 solution is to do MAX(PROGRAM_CODE) and you won't have to group by it. (or MIN() ).

BTW, you can ditch the subquery

SELECT LAST_NAME, FIRST_NAME, max(PROGRAM_CODE), Count(PGM_YEAR) AS YearsServed
FROM [Table]
GROUP BY LAST_NAME, FIRST_NAME, PGM_YEAR
HAVING Count(PGM_YEAR) > 1

[r937]

you don't want to include PGM_YEAR in the GROUP BY because then the count will be 1 for each year

i think!!

perhaps, tsigo, you could give a few sample rows of data, and show the result rows that you want