SQL/Access nightmare

[vengence]

I'm using Access (well I'm coding SQL in access) to parse some log files. I'm mechanical engineer and not a programmer so I'm going a little bit crazy. I've managed to figure out how to do everything I need up till the last step.

I'm going to give a sample input table and desired output table.


Input
File Server
1.pdf A
1.pdf B
2.pdf A
2.pdf A
2.pdf A
3.pdf A
3.pdf B
3.pdf A
4.pdf B
4.pdf B

Output
File
1.pdf
3.pdf


What I need is to flag each file that has both the A and B server values.

I'm guessing I need something like Select Distinct File Where (???)
I've looked through a bunch of FAQs which is how I managed to get the logs down to this point, but I can't find one that tells me how to select a distinct value from file when column B has string 1 AND string 2 (A and B in the above example) .

Thanks in advance!

 

[jarekb84]

Not sure if this is what you meant, but try it out.

SELECT
op.Report
FROM
(
SELECT
RIGHT(fs.fileName, 1) AS Val,
LEFT(fs.fileName, 5) AS Report
FROM
tblFileServer fs
GROUP BY
RIGHT(fs.fileName, 1),
LEFT(fs.fileName, 5)
)
op
GROUP BY
op.Report
HAVING
COUNT(op.Report) > 1

For question like I've found these sites a great help

www.utteraccess.com/
www.tek-tips.com/
www.techonthenet.com/

 

[vengence]

Thanks, I'll give that a shot when I get to work monday.

 

[vengence]

Ok, so I ended up doing something to fake a intersect command.

JOIN INNER etc.

Had a few other problems, like my database hit 2 Gigs. (Seems 375 Megs of log files turn into a rather large database). Used the clean and repair tool in Access which made it small enough to generate the super log, then I exported the super log and deconstructed it and got the interesect. I see why Database Gurus hate Access. I can't believe it wasn't freeing up the space in the file when I deleted a table.

Anyways, got it all fixed, thanks for the help jarekb84!