Setting unknown date range in a query

[Guest]

I am attempting to build a query that will use sql aggregate functions to sum
oil production data by Lease name for the last 7 days of data in the
database. I need to be able to determine the most recent date in the
database and use that date in my criteria field in the query. Unfortunately,
Access will not let me use the Max function in the Where clause of the query.
Anyone know how I can determine what the most recent date is and then use it
in my query?

 

[Steve Schapel]

Wells,

You could use an expression based on the DMax() function in the query
criteria. But I would prefer to make a separate query to return the
most recent date, and then include this query in your main query. So,
the first query might look something like this...
SELECT Max([YourDateField]) As LatestDate FROM YourTable
.... and assuming this query is named DateCrit, the main query than looks
something like...
SELECT [Lease Name], Sum([Production])
FROM YourTable, DateCrit
WHERE YourDateField Between LatestDate-7 And LatestDate
GROUP BY [Lease Name]

 

[John Spencer (MVP)]

You can use a subquery.

SELECT <list of fields>
FROM YourTable
WHERE SomeDateField >
(SELECT CDate(Max(Tmp.SomeDateField)-7)
FROM YourTable as Tmp)

 

[Guest]

Steve,
I had tried doing a separate query that finds the most recent date and then
bringing that query into the query that sums the production data. But, Access
tells me it can't evaluate the query because there are ambiguous outer joins.
Here's the SQL:

SELECT [Base Wells].[LEASE NAME], Sum([All Daily Production Data].TOTBBL) AS
SumOfTOTBBL
FROM qryFindMaxDateinDatabase, [Base Wells] LEFT JOIN [All Daily Production
Data] ON [Base Wells].[WELL#] = [All Daily Production Data].[WELL#]
WHERE ((([All Daily Production Data].DATE) Between [MaxofDate] And
DateAdd("d",-6,[MaxofDate])))
GROUP BY [Base Wells].[LEASE NAME]
HAVING ((([Base Wells].[LEASE NAME]) Not Like "*Storage*"));

Wells,

You could use an expression based on the DMax() function in the query
criteria. But I would prefer to make a separate query to return the
most recent date, and then include this query in your main query. So,
the first query might look something like this...
SELECT Max([YourDateField]) As LatestDate FROM YourTable
.... and assuming this query is named DateCrit, the main query than looks
something like...
SELECT [Lease Name], Sum([Production])
FROM YourTable, DateCrit
WHERE YourDateField Between LatestDate-7 And LatestDate
GROUP BY [Lease Name]

--
Steve Schapel, Microsoft Access MVP

I am attempting to build a query that will use sql aggregate functions to sum
oil production data by Lease name for the last 7 days of data in the
database. I need to be able to determine the most recent date in the
database and use that date in my criteria field in the query. Unfortunately,
Access will not let me use the Max function in the Where clause of the query.
Anyone know how I can determine what the most recent date is and then use it
in my query?

 

[Steve Schapel]

Wells,

There are a number of approaches to handling this. Here's one, which is
probably how I would do it myself... Make yet another query, let's call
it BaseWellsStep as an additional step in the process, to cater to the
Left Join. As an example...
SELECT [Base Wells].[LEASE NAME], Nz([All Daily Production
Data].TOTBBL,0) As TotalBBL, [All Daily Production Data].[DATE]
FROM [Base Wells] LEFT JOIN [All Daily Production Data] ON [Base
Wells].[WELL#] = [All Daily Production Data].[WELL#]
WHERE (([Base Wells].[LEASE NAME]) Not Like "*Storage*")

And then...
SELECT [BaseWellsStep].[LEASE NAME], Sum([BaseWellsStep].TotalBBL) AS
SumOfTOTBBL
FROM qryFindMaxDateinDatabase, BaseWellsStep
WHERE (([BaseWellsStep].[DATE]) > [MaxofDate]-6))

John made a good point in his reply, in that the Between...And...
construct I used is unnecessarily clumsy, since the date will never be
later than the MaxOfDate

I didn't test this, but it looks like it should work Let us know.

 

[Guest]

John,

The subquery idea seems to work well. I don't know if it is the most elegant
way to handle the problem, but it works! Thanks also to Steve for his
suggestions. It was great to get responses so quickly from both of you.
Here's the SQL in case someone else out there needs to do something similar;

SELECT [Base Wells].[LEASE NAME], Sum([All Daily Production Data].TOTBBL) AS
[Current Week (bbls)]
FROM [Base Wells] LEFT JOIN [All Daily Production Data] ON [Base
Wells].[WELL#] = [All Daily Production Data].[WELL#]
WHERE ((([All Daily Production Data].DATE) Between (SELECT Max([All Daily
Production Data].DATE) AS MaxOfDATE FROM [All Daily Production Data]) And
DateAdd("d",-6,(SELECT Max([All Daily Production Data].DATE) AS MaxOfDATE
FROM [All Daily Production Data]))) AND (([Base Wells].CLASS)="T"))
GROUP BY [Base Wells].[LEASE NAME]
HAVING ((([Base Wells].[LEASE NAME]) Not Like "*Storage*"));