Posted by Jason Lepack on 12/13/06 13:47

I now understand access subqueries. They're different from Oracle.

This one query does what you want.
SELECT T1.VehicleID, tbl_purchases.PurchaseID, T1.MinDate,
tbl_purchases.Comment
FROM [SELECT tbl_purchases.VehicleID, Min(tbl_purchases.PurchaseDate)
AS MinDate
FROM tbl_purchases
GROUP BY tbl_purchases.VehicleID]. AS T1 INNER JOIN tbl_purchases ON
(T1.MinDate = tbl_purchases.PurchaseDate) AND (T1.VehicleID =
tbl_purchases.VehicleID);

Cheers,
Jason Lepack

Jason Lepack wrote:
> I used two queries:
>
> Query1:
> SELECT tbl_purchases.VehicleID, Min(tbl_purchases.PurchaseDate) AS
> MinOfPurchaseDate
> FROM tbl_purchases
> GROUP BY tbl_purchases.VehicleID;
>
> Query2:
> SELECT Query1.VehicleID, tbl_purchases.PurchaseID,
> Query1.MinOfPurchaseDate, tbl_purchases.Comment
> FROM Query1 INNER JOIN tbl_purchases ON (Query1.VehicleID =
> tbl_purchases.VehicleID) AND (Query1.MinOfPurchaseDate =
> tbl_purchases.PurchaseDate)
> GROUP BY Query1.VehicleID, tbl_purchases.PurchaseID,
> Query1.MinOfPurchaseDate, tbl_purchases.Comment;
>
> Cheers,
> Jason Lepack
> roberthornsby@hotmail.com wrote:
> > Hi,
> > Please can you help me with this query which I am struggling with?
> > Here is a simplified version of the table I am trying to work with
> >
> > VehicleId, PurchaseId, PurchaseDate, Comment
> >
> > 1, 1, 03/03/2006, 'customer has a big nose'
> > 1, 79, 04/04/2006, 'it's raining'
> > 1, 8, 05/05/2006, 'man, i keep selling this vehicle'
> > 2, 412, 02/02/2006, 'I break for lunch in 10 minutes'
> > 2, 5, 03/03/2006, 'I wonder what's on TV tonight'
> > 3, 2, 05/05/2006, 'I am the angel of death, destroyer of worlds'
> >
> > I need to select only the rows for the first time the vehicle is sold
> > (specifically I need the comment). I need to return
> >
> > 1, 1, 03/03/2006, 'customer has a big nose'
> > 2, 412, 02/02/2006, 'I break for lunch in 10 minutes'
> > 3, 2, 05/05/2006, 'I am the angel of death, destroyer of worlds'
> >
> > Purchase Id cannot be guaranteed to be in ascending date order.
> >
> > Can anyone help please?
> > Thanks
> > Rob

[Back to original message]