Help with syntax...please!

I have a table with duplicate filenames and directories. I need a way to select the filesets (all rows of one filename) that overlap in a certain directory. In the example data below I would need to select the 3 sets of files (file2, file3 and file4) because those sets include the "overlap" directory.

SELECT [table].[File], [table].Path
FROM [table]
GROUP BY [table].[File], [K drive overlap].Path
HAVING ((([table].Path) Like "K:\Dir-overlap*"));

This only returns the part of the set...I want to group by the file and select the sets that contain at least one overlap field, but also retain the results from that set that aren't in the overlap directory. Any ideas?


File....................Path

File1..................K:\Dir1
File1..................K:\Dir1
File1..................K:\Dir3

File2..................K:\Dir1
File2..................K:\Dir-overlap
File2..................K:\Dir3

File3..................K:\Dir1
File3..................K:\Dir-overlap
File3..................K:\Dir1

File4..................K:\Dir3
File4..................K:\Dir-overlap
File4..................K:\Dir3

File5..................K:\Dir1
File5..................K:\Dir3
File5..................K:\Dir3

Huh? I smell a FULL JOIN in there somewhere, but I'm still not sure even what you want.

-PatP

All the records are in one table, so from the example data above, I'd like to return these results with my query....

(I realized that the formatting on the above post went wacky, so I fixed it here by adding some "........." to separate the columns)

File............Path

File2..........K:\Dir1
File2..........K:\Dir-overlap
File2..........K:\Dir3

File3..........K:\Dir1
File3..........K:\Dir-overlap
File3..........K:\Dir1

File4..........K:\Dir3
File4..........K:\Dir-overlap
File4..........K:\Dir3


I need the complete set of records for each filename - but I only want the results from that set if it contains the the overlap directory...

Does that clear it up?

I think I understand what you want. Try using:to see if that does what you want.

-PatP

Yeah! That worked great!

I genuflect before your SQL greatness...

Thanks for the help and the quick response!