Max Function in a query

I have a DB of rates. The rates change. Therefore they have effective
dates. I want to pull the Max effective date that is less than the Freight
Tender date. I have included the SQL below for your reference.

SELECT qryGetBasePerContainer.[Broker Ref #], Max(tblOcean.EffectiveDate) AS
MaxOfEffectiveDate, qryGetBasePerContainer.[Freight Tender Date],
qryGetBasePerContainer.[Base/Container], tblOcean.BaseRate,
[qryGetBasePerContainer]![Base/Container]-[tblOcean]![BaseRate] AS
[Difference in Base]
FROM tblOcean INNER JOIN qryGetBasePerContainer ON (tblOcean.ContainerSize =
qryGetBasePerContainer.[Container Size]) AND (qryGetBasePerContainer.[Port of
Export] = tblOcean.Origin) AND (tblOcean.Forwarder =
qryGetBasePerContainer.Forwarder)
GROUP BY qryGetBasePerContainer.[Broker Ref #],
qryGetBasePerContainer.[Freight Tender Date],
qryGetBasePerContainer.[Base/Container], tblOcean.BaseRate,
[qryGetBasePerContainer]![Base/Container]-[tblOcean]![BaseRate]
HAVING (((Max(tblOcean.EffectiveDate))[qryGetBasePerContainer]![Freight
Tender Date]));

For some reason when I run this query I get a listling for each effective
date instead of a single max. After messing with the query I still can't
figure out why it pulls three entries for each. If you can offer help it
would be appreciated. Thank You.

here is one method; using the normal query design view grid;

1. make a query that returns all records with the effective date that is
less than the Freight Tender date. Call that Query1

2. make a new query sourced on Query 1. Use the embedded aggregate feature
(big greek E symbol) and for the date column select Max

--
NTC


"Dustin B" wrote:

I have a DB of rates. The rates change. Therefore they have effective
dates. I want to pull the Max effective date that is less than the Freight
Tender date. I have included the SQL below for your reference.

SELECT qryGetBasePerContainer.[Broker Ref #], Max(tblOcean.EffectiveDate) AS
MaxOfEffectiveDate, qryGetBasePerContainer.[Freight Tender Date],
qryGetBasePerContainer.[Base/Container], tblOcean.BaseRate,
[qryGetBasePerContainer]![Base/Container]-[tblOcean]![BaseRate] AS
[Difference in Base]
FROM tblOcean INNER JOIN qryGetBasePerContainer ON (tblOcean.ContainerSize =
qryGetBasePerContainer.[Container Size]) AND (qryGetBasePerContainer.[Port of
Export] = tblOcean.Origin) AND (tblOcean.Forwarder =
qryGetBasePerContainer.Forwarder)
GROUP BY qryGetBasePerContainer.[Broker Ref #],
qryGetBasePerContainer.[Freight Tender Date],
qryGetBasePerContainer.[Base/Container], tblOcean.BaseRate,
[qryGetBasePerContainer]![Base/Container]-[tblOcean]![BaseRate]
HAVING (((Max(tblOcean.EffectiveDate))[qryGetBasePerContainer]![Freight
Tender Date]));

For some reason when I run this query I get a listling for each effective
date instead of a single max. After messing with the query I still can't
figure out why it pulls three entries for each. If you can offer help it
would be appreciated. Thank You.

You are grouping by five columns in total here so you'll get the latest date
for each distinct set of values within that grouping, hence the multiple rows
returned. To create a single query to get the values from these columns,
restricted on the latest effective date, but without grouping on all of them
you'll need to use a subquery which returns the latest effective date for
whatever column or columns in the outer query you want as the basis for the
set of dates to be returned. Lets assume you want the latest effective date
that is less than the Freight Tender date per Broker Ref #:

SELECT
QGBPC1.[Broker Ref #],
TO1.EffectiveDate,
QGBPC1.[Freight Tender Date],
QGBPC1.[Base/Container],
TO1.BaseRate,
QGBPC1.[Base/Container]-TO1.[BaseRate]
AS [Difference in Base]
FROM tblOcean AS TO1 INNER JOIN
qryGetBasePerContainer AS QGBPC1
ON TO1.ContainerSize = QGBPC1.[Container Size]
AND QGBPC1.[Port of Export] = TO1.Origin
AND TO1.Forwarder = QGBPC1.Forwarder
WHERE TO1.EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM tblOcean AS TO2 INNER JOIN
qryGetBasePerContainer AS QGBPC2
ON TO2.ContainerSize = QGBPC2.[Container Size]
AND QGBPC2.[Port of Export] = TO2.Origin
AND TO2.Forwarder = QGBPC2.Forwarder
WHERE QGBPC2.[Broker Ref #] = QGBPC1.[Broker Ref #]
AND TO2.EffectiveDate QGBPC1.[Freight Tender Date]);

Without having access to your tables I obviously can't test this, and my
assumption that you want the latest effective date that is less than the
Freight Tender date per Broker Ref # may be wrong, but the way the above
should work is that the subquery returns the latest EffectiveDate where the
Broker Ref # is the same as the current row returned by the outer query and
the EffectiveDate is earlier than the Freight Tender Date returned by the
outer query's current row. This is what's known as a correlated subquery.
Note how the tblOcean table and qryGetBasePerContainer query are given
different aliases in the outer and subquery so as to distinguish the two
instances of each.

You might well find it will be slow as the subquery has to run separately
for every row potentially returned by the outer query so as to achieve the
correlation and restrict the outer query's results.

Ken Sheridan
Stafford, England

"Dustin B" wrote:

I have a DB of rates. The rates change. Therefore they have effective
dates. I want to pull the Max effective date that is less than the Freight
Tender date. I have included the SQL below for your reference.

SELECT qryGetBasePerContainer.[Broker Ref #], Max(tblOcean.EffectiveDate) AS
MaxOfEffectiveDate, qryGetBasePerContainer.[Freight Tender Date],
qryGetBasePerContainer.[Base/Container], tblOcean.BaseRate,
[qryGetBasePerContainer]![Base/Container]-[tblOcean]![BaseRate] AS
[Difference in Base]
FROM tblOcean INNER JOIN qryGetBasePerContainer ON (tblOcean.ContainerSize =
qryGetBasePerContainer.[Container Size]) AND (qryGetBasePerContainer.[Port of
Export] = tblOcean.Origin) AND (tblOcean.Forwarder =
qryGetBasePerContainer.Forwarder)
GROUP BY qryGetBasePerContainer.[Broker Ref #],
qryGetBasePerContainer.[Freight Tender Date],
qryGetBasePerContainer.[Base/Container], tblOcean.BaseRate,
[qryGetBasePerContainer]![Base/Container]-[tblOcean]![BaseRate]
HAVING (((Max(tblOcean.EffectiveDate))[qryGetBasePerContainer]![Freight
Tender Date]));

For some reason when I run this query I get a listling for each effective
date instead of a single max. After messing with the query I still can't
figure out why it pulls three entries for each. If you can offer help it
would be appreciated. Thank You.