MAX DATE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

SELECT [Truck rates].CODE, [Truck rates].PAY_CONTRACT_CODE, [Truck
rates].RATE, [Truck rates].EFFECTIVE_DATE, [Truck rates].DESTINATION_CODE,
[Truck rates].PAY_ACTIVITY_CODE, [Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*") AND (([Truck
rates].DESTINATION_CODE) Is Not Null));

I have the above query, there are a bunch of different rates for the same
blocks and destinations. I want to be able to have the query give me the
max, or most recent date on the rates, right now I get all the rates no
matter what the date. Can anyone help please. Thanks
 
Arlene said:
SELECT [Truck rates].CODE, [Truck rates].PAY_CONTRACT_CODE, [Truck
rates].RATE, [Truck rates].EFFECTIVE_DATE, [Truck rates].DESTINATION_CODE,
[Truck rates].PAY_ACTIVITY_CODE, [Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*") AND (([Truck
rates].DESTINATION_CODE) Is Not Null));

I have the above query, there are a bunch of different rates for the same
blocks and destinations. I want to be able to have the query give me the
max, or most recent date on the rates, right now I get all the rates no
matter what the date. Can anyone help please. Thanks
Click to expand...

Arlene,

Based on the information available, this is my best guess (untested).

SELECT [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,MAX([Truck rates].EFFECTIVE_DATE)
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*")
AND (([Truck rates].DESTINATION_CODE) Is Not Null));
GROUP BY [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE


Sincerely,

Chris O.
 
SELECT CODE
, PAY_CONTRACT_CODE
, RATE
, EFFECTIVE_DATE
, DESTINATION_CODE
, PAY_ACTIVITY_CODE
, BLOCK_CODE
FROM [Truck rates] as T2
WHERE PAY_CONTRACT_CODE Like "TGH*"
AND DESTINATION_CODE Is Not Null
AND EffectiveDate =
(SELECT Max(EffectiveDate)
FROM [Truck Rates] as T1
WHERE T1.Block_Code = T2.Block_Code
AND T1.Destination_Code = T2.Destination_Code)

If that is too slow, then you could use a two query solution.
First query saved as "qLastEffective"
SELECT Destination_Code
, Block_Code,
, Max(Effective_Date) as LastDate
FROM [Truck Rates]
GROUP BY Destination_Code, Block_Code


Use that in a second query with the original table
SELECT CODE
, PAY_CONTRACT_CODE
, RATE
, EFFECTIVE_DATE
, DESTINATION_CODE
, PAY_ACTIVITY_CODE
, BLOCK_CODE
FROM [Truck rates] INNER JOIN qLastEffective
On [Truck Rates].Effective_Date = qLastEffective.LastDate
AND [Truck Rates].Destination_Code = qLastEffective.Destination_Code
AND [Truck Rates].Block_Code = qLastEffective.Block_Code
WHERE PAY_CONTRACT_CODE Like "TGH*"
AND DESTINATION_CODE Is Not Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chris2 said:
Arlene said:
SELECT [Truck rates].CODE, [Truck rates].PAY_CONTRACT_CODE, [Truck
rates].RATE, [Truck rates].EFFECTIVE_DATE, [Truck rates].DESTINATION_CODE,
[Truck rates].PAY_ACTIVITY_CODE, [Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*") AND (([Truck
rates].DESTINATION_CODE) Is Not Null));

I have the above query, there are a bunch of different rates for the same
blocks and destinations. I want to be able to have the query give me the
max, or most recent date on the rates, right now I get all the rates no
matter what the date. Can anyone help please. Thanks
Click to expand...

Arlene,

Based on the information available, this is my best guess (untested).

SELECT [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,MAX([Truck rates].EFFECTIVE_DATE)
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*")
AND (([Truck rates].DESTINATION_CODE) Is Not Null));
GROUP BY [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE


Sincerely,

Chris O.
Click to expand...
 
Thanks Chris but on this I get an error message " the Level clause includes a
reserved word or phrase that is misspelled or missing. I am not sure what
this means as I cant see it and have never seen this before.
Chris2 said:
Arlene said:
SELECT [Truck rates].CODE, [Truck rates].PAY_CONTRACT_CODE, [Truck
rates].RATE, [Truck rates].EFFECTIVE_DATE, [Truck rates].DESTINATION_CODE,
[Truck rates].PAY_ACTIVITY_CODE, [Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*") AND (([Truck
rates].DESTINATION_CODE) Is Not Null));

I have the above query, there are a bunch of different rates for the same
blocks and destinations. I want to be able to have the query give me the
max, or most recent date on the rates, right now I get all the rates no
matter what the date. Can anyone help please. Thanks
Click to expand...

Arlene,

Based on the information available, this is my best guess (untested).

SELECT [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,MAX([Truck rates].EFFECTIVE_DATE)
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*")
AND (([Truck rates].DESTINATION_CODE) Is Not Null));
GROUP BY [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE


Sincerely,

Chris O.
Click to expand...
 
Thank you John the two pronged query worked the best.

John Spencer said:
SELECT CODE
, PAY_CONTRACT_CODE
, RATE
, EFFECTIVE_DATE
, DESTINATION_CODE
, PAY_ACTIVITY_CODE
, BLOCK_CODE
FROM [Truck rates] as T2
WHERE PAY_CONTRACT_CODE Like "TGH*"
AND DESTINATION_CODE Is Not Null
AND EffectiveDate =
(SELECT Max(EffectiveDate)
FROM [Truck Rates] as T1
WHERE T1.Block_Code = T2.Block_Code
AND T1.Destination_Code = T2.Destination_Code)

If that is too slow, then you could use a two query solution.
First query saved as "qLastEffective"
SELECT Destination_Code
, Block_Code,
, Max(Effective_Date) as LastDate
FROM [Truck Rates]
GROUP BY Destination_Code, Block_Code


Use that in a second query with the original table
SELECT CODE
, PAY_CONTRACT_CODE
, RATE
, EFFECTIVE_DATE
, DESTINATION_CODE
, PAY_ACTIVITY_CODE
, BLOCK_CODE
FROM [Truck rates] INNER JOIN qLastEffective
On [Truck Rates].Effective_Date = qLastEffective.LastDate
AND [Truck Rates].Destination_Code = qLastEffective.Destination_Code
AND [Truck Rates].Block_Code = qLastEffective.Block_Code
WHERE PAY_CONTRACT_CODE Like "TGH*"
AND DESTINATION_CODE Is Not Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Chris2 said:
Arlene said:
SELECT [Truck rates].CODE, [Truck rates].PAY_CONTRACT_CODE, [Truck
rates].RATE, [Truck rates].EFFECTIVE_DATE, [Truck rates].DESTINATION_CODE,
[Truck rates].PAY_ACTIVITY_CODE, [Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*") AND (([Truck
rates].DESTINATION_CODE) Is Not Null));

I have the above query, there are a bunch of different rates for the same
blocks and destinations. I want to be able to have the query give me the
max, or most recent date on the rates, right now I get all the rates no
matter what the date. Can anyone help please. Thanks
Click to expand...

Arlene,

Based on the information available, this is my best guess (untested).

SELECT [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,MAX([Truck rates].EFFECTIVE_DATE)
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*")
AND (([Truck rates].DESTINATION_CODE) Is Not Null));
GROUP BY [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
Arlene said:
Thanks Chris but on this I get an error message " the Level clause includes a
reserved word or phrase that is misspelled or missing. I am not sure what
this means as I cant see it and have never seen this before.
Chris2 said:
SELECT [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,MAX([Truck rates].EFFECTIVE_DATE)
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE
FROM [Truck rates]
WHERE ((([Truck rates].PAY_CONTRACT_CODE) Like "TGH*")
AND (([Truck rates].DESTINATION_CODE) Is Not Null));
Click to expand...
Click to expand...

Arlene,

I forgot to move the semi-colon to the new end of the query below.

GROUP BY [Truck rates].CODE
,[Truck rates].PAY_CONTRACT_CODE
,[Truck rates].RATE
,[Truck rates].DESTINATION_CODE
,[Truck rates].PAY_ACTIVITY_CODE
,[Truck rates].BLOCK_CODE
Click to expand...
Click to expand...


Sincerely,

Chris O.
 

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

DE-Concatenate but maintain relationship and record structure 1
Max date 3
Summing top 3 results 2
Very difficult query (?) 9
Query with three tables 3
Finding the date of last pay rise 8
Parent / Child Query Assistance 7
Access crashes on a subquery 7

Back
Top