If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Max Function in a query
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. |
#3
|
|||
|
|||
Max Function in a query
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. |
Thread Tools | |
Display Modes | |
|
|