Query help please

[roberthornsby]

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

[Jason Lepack]

I used two queries:

Query1:
SELECT tbl_purchases.V ehicleID, Min(tbl_purchas es.PurchaseDate ) AS
MinOfPurchaseDa te
FROM tbl_purchases
GROUP BY tbl_purchases.V ehicleID;

Query2:
SELECT Query1.VehicleI D, tbl_purchases.P urchaseID,
Query1.MinOfPur chaseDate, tbl_purchases.C omment
FROM Query1 INNER JOIN tbl_purchases ON (Query1.Vehicle ID =
tbl_purchases.V ehicleID) AND (Query1.MinOfPu rchaseDate =
tbl_purchases.P urchaseDate)
GROUP BY Query1.VehicleI D, tbl_purchases.P urchaseID,
Query1.MinOfPur chaseDate, tbl_purchases.C omment;

Cheers,
Jason Lepack
ro***********@h otmail.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

[Jason Lepack]

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

This one query does what you want.
SELECT T1.VehicleID, tbl_purchases.P urchaseID, T1.MinDate,
tbl_purchases.C omment
FROM [SELECT tbl_purchases.V ehicleID, Min(tbl_purchas es.PurchaseDate )
AS MinDate
FROM tbl_purchases
GROUP BY tbl_purchases.V ehicleID]. AS T1 INNER JOIN tbl_purchases ON
(T1.MinDate = tbl_purchases.P urchaseDate) AND (T1.VehicleID =
tbl_purchases.V ehicleID);

Cheers,
Jason Lepack

Jason Lepack wrote:

I used two queries:

Query1:
SELECT tbl_purchases.V ehicleID, Min(tbl_purchas es.PurchaseDate ) AS
MinOfPurchaseDa te
FROM tbl_purchases
GROUP BY tbl_purchases.V ehicleID;

Query2:
SELECT Query1.VehicleI D, tbl_purchases.P urchaseID,
Query1.MinOfPur chaseDate, tbl_purchases.C omment
FROM Query1 INNER JOIN tbl_purchases ON (Query1.Vehicle ID =
tbl_purchases.V ehicleID) AND (Query1.MinOfPu rchaseDate =
tbl_purchases.P urchaseDate)
GROUP BY Query1.VehicleI D, tbl_purchases.P urchaseID,
Query1.MinOfPur chaseDate, tbl_purchases.C omment;

Cheers,
Jason Lepack
ro***********@h otmail.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

[Roy Harvey]

A few different approaches.

SELECT *
FROM Purchases as A
WHERE PurchaseDate =
(select min(PurchaseDat e) from Purchases as B
where A.VehicleID = B.VehicleID)

SELECT *
FROM Purchases as A
WHERE NOT EXISTS
(select * from Purchases as B
where A.VehicleID = B.VehicleID
and A.PurchaseDate B.PurchaseDate)

SELECT *
FROM Purchases as A
WHERE PurchaseID =
(select TOP 1 PurchaseID from Purchases as B
where A.VehicleID = B.VehicleID
order by PurchaseDate)

Roy Harvey
Beacon Falls, CT

On 13 Dec 2006 05:09:34 -0800, ro***********@h otmail.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
(specificall y 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