Query condition

[koolguy]

hi all,

im working on qureries in ms-access and need a way of getting the following:

With Reference to the table below:

Cust_ID
Order_date
001
01/01/2008
002
05/01/2008
001
05/01/2008
001
07/01/2008


can anyone help me design a query that gives me all the order_dates greater than the first order_date(i.e all order_dates except the very first one) of all cust_id’s.

The result shall be something similar to this:

Cust_ID
Order_date
001
05/01/2008
001
07/01/2008



Any help shall be much appreciated!

 

[krester]

Hi koolguy,

I would do it in 2 queries:
1. Find the first record for each customer - qryFindFirst:

SELECT Table1.id, First(Table1.date) AS FirstOfdate
FROM Table1;

2. Subtract the query above from your table (Table1):

SELECT Table1.id, Table1.date, qryFindFirst.FirstOfdate
FROM Table1 LEFT JOIN qryFindFirst ON Table1.id=qryFindFirst.id
WHERE (((qryFindFirst.FirstOfdate)<>Table1!date));
​

Hope I helped you ​

 

[koolguy]

fabulous krester!

the code u gave worked for my condition, thank u very much!
im happy to have got it working at last.....