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
|
|||
|
|||
Setting unknown date range in a query
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? |
#2
|
|||
|
|||
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 Wells wrote: 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? |
#3
|
|||
|
|||
You can use a subquery.
SELECT list of fields FROM YourTable WHERE SomeDateField (SELECT CDate(Max(Tmp.SomeDateField)-7) FROM YourTable as Tmp) Wells wrote: 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? |
#4
|
|||
|
|||
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*")); "Steve Schapel" wrote: 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 Wells wrote: 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? |
#5
|
|||
|
|||
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. -- Steve Schapel, Microsoft Access MVP Wells wrote: 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*")); |
#6
|
|||
|
|||
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*")); "John Spencer (MVP)" wrote: You can use a subquery. SELECT list of fields FROM YourTable WHERE SomeDateField (SELECT CDate(Max(Tmp.SomeDateField)-7) FROM YourTable as Tmp) Wells wrote: 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Date Range from Query in Report Header | Alcide | Setting Up & Running Reports | 14 | September 15th, 2008 10:25 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Inserting Query's Date Range in a Report | Chip Rose | Setting Up & Running Reports | 2 | July 13th, 2004 05:39 PM |