Quick SQL help

[trk1616b]

I think this is fairly easy, but i'm far from a SQL expert.

Use these fields in my table:
-Date
-Customer_nm

I'm trying to get a list of rows with this criteria:
-The customer must have a row before 10/1/2005
-The customer must NOT have a row after 10/1/2005
-List them by alphabetically.

Thanks!

 

[PHV]

One way:
SELECT * FROM yourTable
WHERE Customer_nm In (SELECT Customer_nm FROM yourTable GROUP BY Customer_nm HAVING Max([Date])<#2005-10-01#))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Select Customer_nm, [Date]

From myTable A

Where

   EXISTS (Select * From myTable X
           Where X.Customer_nm = A.Customer_nm
             AND X.[Date] < #10/01/2005# )

   AND

   NOT EXISTS (Select * From myTable X
               Where X.Customer_nm = A.Customer_nm
                 AND X.[Date] > #10/01/2005# )

Order By Customer_nm

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]