Complex Calculated Query?

[ccflyer]

Hi everyone,

OK this is a pretty big question, but here it is....

I am not quite sure that a query is the best thing to use in this case, but I think it might be possible. Here is what I need to do: I have a table with information about oil well production. In this table the production of each oil well is recorded once a month (Barels of Oil, Water, and amount of Gas produced, and the number of days that the well produced in that month). This table ranges in dates from 1986 to 2006 and has records every month in that time span. You can see a sample of how the design of this table is in the attachment (Table: [Well Data]).

What I need to do is have a query look at this main table and create another table with some calculated fields. This new table will have only 1 record for each well. You can see a sample of the table that needs to be created in the attachment (Table: [New Table]). In this table, the first 8 columns are not calculated at all, they are just recorded from the main table. The next columns are calculated. As you can see in the attached preview, the query needs to find the first 180 days (6 months) that a well is producing and sum the oil, gas, and water in that time. It also has to do this for the first 365 days (12 months). Then it has to record the earliest date that a well has in the [Well Start Date] column and the latest date a well has [Well End Date]. And last, it has to determine how many total days the well was producing (Subtract [Well End Date] - [Well Start Date]).

I know this is a lot to comprehend, so please ask questions if you don't understand me. I really appreciate your help!!

Thanks,
Chris

 

[Bobadopolis]

Hi,

Firstly, no attachment.

What I need to do is have a query look at this main table and create another table with some calculated fields.

Secondly, you should not be storing calculated data in a table! You need to make a query that has these calculations in then bind your form or report to this.

So... in the query you want to add your fields several times, making sure you rename each to the relevant criteria. Then for each just add your criteria in the criteria cell. Unfortunately, this will need to be a subquery to get the min function on the date. For example, for the first 6 months of a well you may want a criteria something like:

Between (([Well Data].[Well Start Date]) In (SELECT min([Well Start Date]) FROM [Well Data] AS MinDate)) And DateAdd("m",6,(SELECT min([Well Start Date]) FROM [Well Data] AS MinDate))

The other fields just need to be totaled (enable 'Totals' in design view and choose 'total'); make sure you exclude non-totaled fields.

Hope this works - I haven't tried it!

Bobadopolis