Can anyone figure this one out?

  • Thread starter Thread starter Kyle
  • Start date Start date
K

Kyle

I have a database used to track training records. I need
to eliminate those individuals who are current in their
training requirements. For simplicity the table would
look like this:
ID Name #
1 Paul 1
2 John 2
3 John 3
4 George 3
5 Ringo 3
I want to eliminate all the occurrences of name if any of
# = 2 and return this:
ID Name #
1 Paul 1
4 George 3
5 Ringo 3

There must be a way to write this in SQL, but after two
days it still isn't coming to me.
 
Try:

****untested SQL String****

SELECT TM.[ID], TM.[Name], TM.[#]
FROM YourTable As TM
WHERE TM.[ID] Not In
( SELECT DISTINCT TS.[ID]
FROM YourTable As TS
WHERE TS.[#] = 2
)

********

Note: TM means Table (Main) and TS means Table (Sub)
 
Thanks Van, I'll give it a shot
Kyle
-----Original Message-----
Try:

****untested SQL String****

SELECT TM.[ID], TM.[Name], TM.[#]
FROM YourTable As TM
WHERE TM.[ID] Not In
( SELECT DISTINCT TS.[ID]
FROM YourTable As TS
WHERE TS.[#] = 2
)

********

Note: TM means Table (Main) and TS means Table (Sub)

--
HTH
Van T. Dinh
MVP (Access)



Kyle said:
I have a database used to track training records. I need
to eliminate those individuals who are current in their
training requirements. For simplicity the table would
look like this:
ID Name #
1 Paul 1
2 John 2
3 John 3
4 George 3
5 Ringo 3
I want to eliminate all the occurrences of name if any of
# = 2 and return this:
ID Name #
1 Paul 1
4 George 3
5 Ringo 3

There must be a way to write this in SQL, but after two
days it still isn't coming to me.
Click to expand...


.
Click to expand...
 
Back
Top