Max Function

[Dustin B]

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.

 

[Wolfgang Kais]

Hello Dustin.

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.

....and you also want to display the base rate for that effective date.

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.

The problem is that you are grouping by BaseRate. Which means that for
each BaseRate, another effective date (maximum of one record) will be
selected. Grouping for acean fields is not allowed in this case,
therefore I suggest the following:

SELECT qryGetBasePerContainer.[Broker Ref #],
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 (tblOcean.Origin = qryGetBasePerContainer.[Port of Export])
AND (tblOcean.Forwarder = qryGetBasePerContainer.Forwarder)
WHERE tblOcean.EffectiveDate =
(SELECT Max(EffectiveDate) FROM tblOcean As tmp WHERE
(tmp.ContainerSize = tblOcean.ContainerSize) AND (tmp.Origin =
tblOcean.Origin) AND (tmp.Forwarder = tblOcean.Forwarder);