Sorry, that topic isn't really specific but I'm not exactly sure how to phrase it.

Basically I've been given this rather large database that someone else designed horribly, and I'm looking at one specific table. The two columns in particular I need to look at are called "archive" and "filename".

The archive column consists of either "Printed Copy" or "Electronic Copy". Each filename in this table should have two records associated with it, the "Printed Copy" record and the "Electronic Copy" record.

Is there a way to go through this entire table and basically check to make sure each unqiue filename has both a "Printed Copy" and an "Electronic Copy"... and if they don't output them?

Thanks.

Edit: To get a little more specific:

Filename Archive
-------- -------
aaaa "Printed Copy"
aaaa "Electronic Copy"
bbbb "Electronic Copy"
cccc "Printed Copy"

I would want 'bbbb' and 'cccc' rows to be outputted.

Hi there,

I think this will do the trick:

SELECT FileName, COUNT(FileName) AS NumRecords
FROM FileNamesTable
GROUP BY FileName
HAVING (COUNT(FileName) = 1)

This ouputs something like this:

FileName NumRecords
BBBB 1
CCC 1

which I think is what you're after.

The SELECT for NumRecords isn't strictly necessary, so you can drop it if you want.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Pretty Tied Up by Guns N' Roses (Track 7 from the album: Use Your Illusion 2) What's This?

Hi Imar,

That works somewhat, but I need to have the archive column in there as well, and if I just add that into the select line, it gives me an error.