Records immediately before and after dates that are within groups 2

[Mike922]

I need to come up with a query in Access 2k that kind of mirrors this scenario --- Using Northwind as an example. First I'd like to see all of the orders which exceed a specified amount, say $4000. Out of that group of "big" customers, I want to know the average amount they spent on the order they placed immediately before and immediately following that "large" order. I am new to Access & SQL (just starting to learn now) but I do have some VB experience. I can see how this type of query might be possible with Visual Basic using arrays etc. but I'm unable to figure out a solution yet using sql. The first part is easy, finding and displaying the "OrderID's" which meet the $4000 criteria. However dates returned from each of the "qualifying" orders must somehow form the basis of criteria for subseqent searches within groupings by CustomerID. So for example in Northwind, we know OrderID 10953 was placed by "Around the Horn" for $4050 on the 16-Mar-1998, therefore it meets criteria. "Around the Horn" also placed orders immediately before and after 16-Mar-1998 (10-Apr-1998 and 03-Mar-1998). It is these two orders that I'd like to know the average amount spent so I can compare it with the amount spent on the $4050 order. Any suggestions on how to do this or which books might be best to learn from are greatly appreciated. Thanks. Mike

 

[LittleSmudge]

In VB open a recordset ( rstLO ) on the Orders table WHERE OrderValue> $4000


Then open a second recordset ( rstBA ) on the full table ORDER BY OrderDate

WHILE Not rstLO.EOF
    rstBA.MoveFirst
    rstBA.Find "OrderId = " & rstLO!OrderId
    If rstBA.EOF Then
        ' Oops Error state
    Else
        rstBA.MovePrevious
        If rstBA.BOF Then
            ' big order was their first order !
        Else
            ' rstBA Now looking at your "BEFORE" Record
            ' Insert code to extract data
        End If
        rstBA.MoveNext
        rstBA.MoveNext
        If rstBA.EOF Then
            ' big order was their last order !
        Else
            ' rstBA Now looking at your "AFTER" Record
            ' Insert code to extract data
        End If
    End If
    rstLO.MoveNext
Wend

'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[PHV]

Something like this ?
SELECT A.CustomerID, Avg(A.OrderAmount)
FROM tblOrder A INNER JOIN
(SELECT CustomerID, OrderDate FROM tblOrder WHERE OrderAmount>4000) B
ON A.CustomerID=B.CustomerID
WHERE (A.OrderDate=(SELECT Max(C.OrderDate) FROM tblOrder C WHERE C.CustomerID=B.CustomerID AND C.OrderDate<B.OrderDate)
OR A.OrderDate=(SELECT Min(D.OrderDate) FROM tblOrder D WHERE D.CustomerID=B.CustomerID AND D.OrderDate>D.OrderDate))
GROUP BY A.CustomerID;

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

 

[Mike922]

Thanks to both of you for your help. Based in part upon your responses, I may have figured out a solution on my own using multiple queries created with the QBE grid. I'm in the process of testing it out today. I've printed out your sample code for guidance. Much appreciated.