SubQuery?

[Guest]

I've got this SQL that works fine:

SELECT Unit, Function, Min(Prod) AS AProd
FROM tblGLFINAL
WHERE (((Desc)="Product Code Allocation") AND ((Acct) Not Like "4*"))
GROUP BY Unit, Function

But in the selection of the MIN Prod, I would like to exclude Prods 100,
200, 300 and 400 as valid selection options. Basically, those prods would
NOT be considered when determining the MIN prod. Help?!

 

[Guest]

Try something like

SELECT Unit, Function, Min(Prod) AS AProd
FROM tblGLFINAL
WHERE Desc="Product Code Allocation" AND Acct Not Like "4*" And Prod Not In
(100,200,300,400)
GROUP BY Unit, Function

 

[Guest]

That was my first thought as well, but that syntax does not produce the NEXT
prod code in line (after prod 100 for example). It just skips right over any
records that contain 100, 200, 300, or 400 instead of displaying the next
product in line.

 

[Guest]

Never mind my previous post. Your suggestion works great. Thanks!

 

[John Spencer]

SELECT Unit, Function, Min(Prod) AS AProd
FROM tblGLFINAL
WHERE (((Desc)="Product Code Allocation") AND ((Acct) Not Like "4*"))
AND Prod Not In (100,200,300,400)
GROUP BY Unit, Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..