Query to return complete record with max cost within grouping...

G

Guest

Hello...

I am struggling with a query to do the following...

From a table tblData like this:
ID Vehicle Date Ref Cost
1 z1 06/04/2006 11 $22
2 z1 06/04/2006 24 $25
3 z1 06/04/2006 31 $13
4 z1 08/07/2006 9 $45
5 z1 08/07/2006 55 $78
6 x2 10/05/2006 57 $10
7 x2 10/05/2006 61 $33
8 x2 10/05/2006 89 $55
9 x2 10/05/2006 21 $10
10 c3 15/03/2006 15 $97
11 c3 15/03/2006 10 $22
12 c3 15/03/2006 99 $44
13 c3 01/05/2006 83 $7
14 c3 01/05/2006 72 $3
15 c3 01/05/2006 14 $8

...I want to group by Vehicle and Date, and return tblData.* for the record
with Max Cost within that grouping. Therefore, the query should return:

ID Vehicle Date Ref Cost
2 z1 06/04/2006 24 $25
5 z1 08/07/2006 55 $78
8 x2 10/05/2006 89 $55
10 c3 15/03/2006 15 $97
15 c3 01/05/2006 14 $8

Note that each record is a complete record from tblData. tblData is not in
any particular order.

I hope this is clear! Very grateful for any assistance...

Cheers,
Dave
 
G

Guest

David,

One way would be:

SELECT T.*
From tblData T
INNER JOIN (SELECT Vehicle, [Date], Max(Cost) as MaxCost
FROM tblData
Group by Vehicle, [Date]) as VDMC
ON T.Vehicle = VDMC.Vehicle
AND T.[Date] = VDMC.[Date]
AND T.Cost = VDMC.MaxCost
ORDER BY T.Vehicle, T.[Date]

BTW, Date is a reserved word in Access and should not be used as a field
name. Be more specific such as PurchaseDate or MaintenanceDate, or something
like that. There are lots of other reserved words which Access will try to
interpret at run time. If you want to avoid conflicts you should not use
these words, or wrap them in brackets [Date] whenever you use them.

HTH
Dale
 
M

Marshall Barton

Dave said:
I am struggling with a query to do the following...

From a table tblData like this:
ID Vehicle Date Ref Cost
1 z1 06/04/2006 11 $22
2 z1 06/04/2006 24 $25
3 z1 06/04/2006 31 $13
4 z1 08/07/2006 9 $45
5 z1 08/07/2006 55 $78
6 x2 10/05/2006 57 $10
7 x2 10/05/2006 61 $33
8 x2 10/05/2006 89 $55
9 x2 10/05/2006 21 $10
10 c3 15/03/2006 15 $97
11 c3 15/03/2006 10 $22
12 c3 15/03/2006 99 $44
13 c3 01/05/2006 83 $7
14 c3 01/05/2006 72 $3
15 c3 01/05/2006 14 $8

..I want to group by Vehicle and Date, and return tblData.* for the record
with Max Cost within that grouping. Therefore, the query should return:

ID Vehicle Date Ref Cost
2 z1 06/04/2006 24 $25
5 z1 08/07/2006 55 $78
8 x2 10/05/2006 89 $55
10 c3 15/03/2006 15 $97
15 c3 01/05/2006 14 $8

Note that each record is a complete record from tblData. tblData is not in
any particular order.
Click to expand...


Try this kind of thing:

SELECT tblData.*
FROM tblData
WHERE tblData.Cost =
(SELECT Max(X.Cost)
FROM tblData As X
WHERE X.Vehicle = tblData.Vehicle
And X.[Date] = tblData.[Date])
 
G

Guest

FYI, wrapping reserved words in brackets is not 100% reliable. I had a case
recently where even with the field named Date was wrapped, I was getting
incorrect results with:

If rst![Date] = Date() Then

I had to change it to

If rst![Date] = VBA.Date() Then

--
Dave Hargis, Microsoft Access MVP


Dale Fye said:
David,

One way would be:

SELECT T.*
From tblData T
INNER JOIN (SELECT Vehicle, [Date], Max(Cost) as MaxCost
FROM tblData
Group by Vehicle, [Date]) as VDMC
ON T.Vehicle = VDMC.Vehicle
AND T.[Date] = VDMC.[Date]
AND T.Cost = VDMC.MaxCost
ORDER BY T.Vehicle, T.[Date]

BTW, Date is a reserved word in Access and should not be used as a field
name. Be more specific such as PurchaseDate or MaintenanceDate, or something
like that. There are lots of other reserved words which Access will try to
interpret at run time. If you want to avoid conflicts you should not use
these words, or wrap them in brackets [Date] whenever you use them.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Dave Ramage said:
Hello...

I am struggling with a query to do the following...

From a table tblData like this:
ID Vehicle Date Ref Cost
1 z1 06/04/2006 11 $22
2 z1 06/04/2006 24 $25
3 z1 06/04/2006 31 $13
4 z1 08/07/2006 9 $45
5 z1 08/07/2006 55 $78
6 x2 10/05/2006 57 $10
7 x2 10/05/2006 61 $33
8 x2 10/05/2006 89 $55
9 x2 10/05/2006 21 $10
10 c3 15/03/2006 15 $97
11 c3 15/03/2006 10 $22
12 c3 15/03/2006 99 $44
13 c3 01/05/2006 83 $7
14 c3 01/05/2006 72 $3
15 c3 01/05/2006 14 $8

..I want to group by Vehicle and Date, and return tblData.* for the record
with Max Cost within that grouping. Therefore, the query should return:

ID Vehicle Date Ref Cost
2 z1 06/04/2006 24 $25
5 z1 08/07/2006 55 $78
8 x2 10/05/2006 89 $55
10 c3 15/03/2006 15 $97
15 c3 01/05/2006 14 $8

Note that each record is a complete record from tblData. tblData is not in
any particular order.

I hope this is clear! Very grateful for any assistance...

Cheers,
Dave
Click to expand...
Click to expand...
 
J

John W. Vinson

..I want to group by Vehicle and Date, and return tblData.* for the record
with Max Cost within that grouping. Therefore, the query should return:

ID Vehicle Date Ref Cost
2 z1 06/04/2006 24 $25
5 z1 08/07/2006 55 $78
8 x2 10/05/2006 89 $55
10 c3 15/03/2006 15 $97
15 c3 01/05/2006 14 $8

Note that each record is a complete record from tblData. tblData is not in
any particular order.
Click to expand...

Use a Subquery:

SELECT ID, Vehicle, [Date], Ref, Cost
FROM tblData
WHERE Cost =
(SELECT Max([Cost]) FROM tblData AS TD
WHERE TD.Vehicle = tblData.Vehicle);

If there are two records tied for the maximum cost you'll see both.

John W. Vinson [MVP]
 
G

Guest

Dale,

Thanks for this- works perfectly, and surprisingly quickly! Just one
question- why substitute T as a table instead of using tblData as is. Does
this make it quicker?

Thanks,
Dave

Dale Fye said:
David,

One way would be:

SELECT T.*
From tblData T
INNER JOIN (SELECT Vehicle, [Date], Max(Cost) as MaxCost
FROM tblData
Group by Vehicle, [Date]) as VDMC
ON T.Vehicle = VDMC.Vehicle
AND T.[Date] = VDMC.[Date]
AND T.Cost = VDMC.MaxCost
ORDER BY T.Vehicle, T.[Date]

BTW, Date is a reserved word in Access and should not be used as a field
name. Be more specific such as PurchaseDate or MaintenanceDate, or something
like that. There are lots of other reserved words which Access will try to
interpret at run time. If you want to avoid conflicts you should not use
these words, or wrap them in brackets [Date] whenever you use them.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Dave Ramage said:
Hello...

I am struggling with a query to do the following...

From a table tblData like this:
ID Vehicle Date Ref Cost
1 z1 06/04/2006 11 $22
2 z1 06/04/2006 24 $25
3 z1 06/04/2006 31 $13
4 z1 08/07/2006 9 $45
5 z1 08/07/2006 55 $78
6 x2 10/05/2006 57 $10
7 x2 10/05/2006 61 $33
8 x2 10/05/2006 89 $55
9 x2 10/05/2006 21 $10
10 c3 15/03/2006 15 $97
11 c3 15/03/2006 10 $22
12 c3 15/03/2006 99 $44
13 c3 01/05/2006 83 $7
14 c3 01/05/2006 72 $3
15 c3 01/05/2006 14 $8

..I want to group by Vehicle and Date, and return tblData.* for the record
with Max Cost within that grouping. Therefore, the query should return:

ID Vehicle Date Ref Cost
2 z1 06/04/2006 24 $25
5 z1 08/07/2006 55 $78
8 x2 10/05/2006 89 $55
10 c3 15/03/2006 15 $97
15 c3 01/05/2006 14 $8

Note that each record is a complete record from tblData. tblData is not in
any particular order.

I hope this is clear! Very grateful for any assistance...

Cheers,
Dave
Click to expand...
Click to expand...
 
D

Dale Fye

Doesn't make the query any quicker, but makes typeing the query a lot
quicker ;-)

Dale

Dave Ramage said:
Dale,

Thanks for this- works perfectly, and surprisingly quickly! Just one
question- why substitute T as a table instead of using tblData as is. Does
this make it quicker?

Thanks,
Dave

Dale Fye said:
David,

One way would be:

SELECT T.*
From tblData T
INNER JOIN (SELECT Vehicle, [Date], Max(Cost) as MaxCost
FROM tblData
Group by Vehicle, [Date]) as VDMC
ON T.Vehicle = VDMC.Vehicle
AND T.[Date] = VDMC.[Date]
AND T.Cost = VDMC.MaxCost
ORDER BY T.Vehicle, T.[Date]

BTW, Date is a reserved word in Access and should not be used as a field
name. Be more specific such as PurchaseDate or MaintenanceDate, or
something
like that. There are lots of other reserved words which Access will try
to
interpret at run time. If you want to avoid conflicts you should not use
these words, or wrap them in brackets [Date] whenever you use them.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


Dave Ramage said:
Hello...

I am struggling with a query to do the following...

From a table tblData like this:
ID Vehicle Date Ref Cost
1 z1 06/04/2006 11 $22
2 z1 06/04/2006 24 $25
3 z1 06/04/2006 31 $13
4 z1 08/07/2006 9 $45
5 z1 08/07/2006 55 $78
6 x2 10/05/2006 57 $10
7 x2 10/05/2006 61 $33
8 x2 10/05/2006 89 $55
9 x2 10/05/2006 21 $10
10 c3 15/03/2006 15 $97
11 c3 15/03/2006 10 $22
12 c3 15/03/2006 99 $44
13 c3 01/05/2006 83 $7
14 c3 01/05/2006 72 $3
15 c3 01/05/2006 14 $8

..I want to group by Vehicle and Date, and return tblData.* for the
record
with Max Cost within that grouping. Therefore, the query should return:

ID Vehicle Date Ref Cost
2 z1 06/04/2006 24 $25
5 z1 08/07/2006 55 $78
8 x2 10/05/2006 89 $55
10 c3 15/03/2006 15 $97
15 c3 01/05/2006 14 $8

Note that each record is a complete record from tblData. tblData is not
in
any particular order.

I hope this is clear! Very grateful for any assistance...

Cheers,
Dave
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Advanced problem using auto-number and datediff to find readmissio 1
most recent rows 5
query to reference a previous record 2
Summing payment records in a query but restricting it to variable dates in another table 4
Transpose rows to columns in Access. 3
Too negative XIRR? 3
Obtain Month & Year from a Text formatted Date field using Left Fu 2
I need to help with Excel (beginner) -- year row and month column 2

Top