Maybe an entirely different approach is needed?

[dustinb via AccessMonster.com]

I have a DB that I would like to pull the correct rate from tblOcean that
corresponds to the each shipment in qryGetBasePerContainer. I am trying to
do this by putting a criteria on Effective Date and saying I want to see the
Max effective date that is less than the freight tender date. To me that
sounds like it should work but instead of pulling just one rate from Table
ocean to go with each shipment it pulls nothing because the Max of
EffectiveDate is greater than the freight tender. If you need more details
please ask. Thank you.


SELECT qryGetBasePerContainer.[Broker ref #], qryGetBasePerContainer.
[Container #], Max(tblOcean.EffectiveDate) AS [Effective Date]
FROM tblOcean LEFT JOIN qryGetBasePerContainer ON (tblOcean.Mode =
qryGetBasePerContainer.Mode) AND (tblOcean.ContainerSize =
qryGetBasePerContainer.[Container size]) AND (tblOcean.Origin =
qryGetBasePerContainer.[Port of export]) AND (tblOcean.Forwarder =
qryGetBasePerContainer.Forwarder)
GROUP BY qryGetBasePerContainer.[Broker ref #], qryGetBasePerContainer.
[Container #], qryGetBasePerContainer.[Freight tender date]
HAVING (((Max(tblOcean.EffectiveDate))<[qryGetBasePerContainer]![Freight
tender date]));

 

[Marshall Barton]

I have a DB that I would like to pull the correct rate from tblOcean that
corresponds to the each shipment in qryGetBasePerContainer. I am trying to
do this by putting a criteria on Effective Date and saying I want to see the
Max effective date that is less than the freight tender date. To me that
sounds like it should work but instead of pulling just one rate from Table
ocean to go with each shipment it pulls nothing because the Max of
EffectiveDate is greater than the freight tender. If you need more details
please ask. Thank you.


SELECT qryGetBasePerContainer.[Broker ref #], qryGetBasePerContainer.
[Container #], Max(tblOcean.EffectiveDate) AS [Effective Date]
FROM tblOcean LEFT JOIN qryGetBasePerContainer ON (tblOcean.Mode =
qryGetBasePerContainer.Mode) AND (tblOcean.ContainerSize =
qryGetBasePerContainer.[Container size]) AND (tblOcean.Origin =
qryGetBasePerContainer.[Port of export]) AND (tblOcean.Forwarder =
qryGetBasePerContainer.Forwarder)
GROUP BY qryGetBasePerContainer.[Broker ref #], qryGetBasePerContainer.
[Container #], qryGetBasePerContainer.[Freight tender date]
HAVING (((Max(tblOcean.EffectiveDate))<[qryGetBasePerContainer]![Freight
tender date]));

I's not sure what you want, but I think you want to get rid
of the HAVING clause and use:

WHERE tblOcean.EffectiveDate<qryGetBasePerContainer![Freight
tender date]

 

[dustinbrearton via AccessMonster.com]

I have a DB that I would like to pull the correct rate from tblOcean that
corresponds to the each shipment in qryGetBasePerContainer. I am trying to

[quoted text clipped - 16 lines]

HAVING (((Max(tblOcean.EffectiveDate))<[qryGetBasePerContainer]![Freight
tender date]));

I's not sure what you want, but I think you want to get rid
of the HAVING clause and use:

WHERE tblOcean.EffectiveDate<qryGetBasePerContainer![Freight
tender date]

Not explained well I guess. Each line in tblOcean represents a rate. The
rates are broken down by route effective date and a couple other criteria.
qryGetBasePerContainer is a query on a linked table called EOM. EOM has a
field called FreightTenderDate. What I want to do is get the correct rate
based on the newest EffectiveDate that is not after the FreightTenderDate.
The code above is a smaller version of a query that I have pulling in
different rates for each lane. The HAVING line is what I beleive to be key
to making this work. The SQL that you have above will give me several rates
from tblOcean for each shipment in qryGetBasePerContainer because there could
be 5 changes to the rate for that lane before the FreightTenderDate and
another 3-4 after FreightTenderDate. Thank you for your time.

 

[dustinbrearton via AccessMonster.com]

It looks like I found a way to make it work. I made more querries to feed
the final query. I was trying to add to many criteria for one query I guess.
I made one query that found all rates that matched the shipment I was looking
at. Then from that query I found the max effective date to come up with the
rates to use for each shipment. Then I can pull the rest of the rates in
another query.

[quoted text clipped - 7 lines]

WHERE tblOcean.EffectiveDate<qryGetBasePerContainer![Freight
tender date]

Not explained well I guess. Each line in tblOcean represents a rate. The
rates are broken down by route effective date and a couple other criteria.
qryGetBasePerContainer is a query on a linked table called EOM. EOM has a
field called FreightTenderDate. What I want to do is get the correct rate
based on the newest EffectiveDate that is not after the FreightTenderDate.
The code above is a smaller version of a query that I have pulling in
different rates for each lane. The HAVING line is what I beleive to be key
to making this work. The SQL that you have above will give me several rates
from tblOcean for each shipment in qryGetBasePerContainer because there could
be 5 changes to the rate for that lane before the FreightTenderDate and
another 3-4 after FreightTenderDate. Thank you for your time.

 

[Marshall Barton]

I have a DB that I would like to pull the correct rate from tblOcean that
corresponds to the each shipment in qryGetBasePerContainer. I am trying to

[quoted text clipped - 16 lines]

HAVING (((Max(tblOcean.EffectiveDate))<[qryGetBasePerContainer]![Freight
tender date]));

I's not sure what you want, but I think you want to get rid
of the HAVING clause and use:

WHERE tblOcean.EffectiveDate<qryGetBasePerContainer![Freight
tender date]

Not explained well I guess. Each line in tblOcean represents a rate. The
rates are broken down by route effective date and a couple other criteria.
qryGetBasePerContainer is a query on a linked table called EOM. EOM has a
field called FreightTenderDate. What I want to do is get the correct rate
based on the newest EffectiveDate that is not after the FreightTenderDate.
The code above is a smaller version of a query that I have pulling in
different rates for each lane. The HAVING line is what I beleive to be key
to making this work. The SQL that you have above will give me several rates
from tblOcean for each shipment in qryGetBasePerContainer because there could
be 5 changes to the rate for that lane before the FreightTenderDate and
another 3-4 after FreightTenderDate.

I think you want to use a subquery instead of group by:

SELECT Q.[Broker ref #], Q.[Container #],
(SELECT Max(tblOcean.Rate)
FROM tblOcean As R
WHERE R.ContainerSize = Q.[Container size]
AND R.Origin = Q.[Port of export]
AND R.Forwarder = Q.Forwarder
AND R.[Effective Date] <= Q.[Freight
tender date]
) AS EffectiveRate
FROM qryGetBasePerContainer As Q

 

[Marshall Barton]

It looks like I found a way to make it work. I made more querries to feed
the final query. I was trying to add to many criteria for one query I guess.
I made one query that found all rates that matched the shipment I was looking
at. Then from that query I found the max effective date to come up with the
rates to use for each shipment. Then I can pull the rest of the rates in
another query.

That sounds kind of convoluted or at least the equivalent of
using a subquery.