Yeah, you're right. Sorry. I was trying not to overcomplicate the subject.
What I'm given is monthly projected production data. I have to use daily production numbers for equipment design and rating, so I take the month total and average it over days. (I'm working on getting something more realistic but that's what I have for now). Whenever a new well is brought online, it adds to the total flow going down a pipeline so I use the database to figure out, on a given day, what the pipeline needs to handle. In the end, I'll change the production dates to match when the well will be brought online and total up the resulting flows. Hope that makes sense.
So I need to focus on the peak flows.
Each WellPad will contain multiple Wells. I need the total flow from each WellPad. So while I have individual production numbers from each Well, I need to add them together to get a total flow from the WellPad. (The reasons being based on geography as the wells will feed into one place on the pipeline.)
Relationships
Table Descriptions
Sample Production Data
Query Results
The query used to get the total production per well pad is this:
SQL:
SELECT tblWellPads.WellPadName, tblProduction.ProductionDate, Sum(tblProduction.RiskedOil) AS SumOfRiskedOil, Sum(tblProduction.RiskedGas) AS SumOfRiskedGas, Sum(tblProduction.RiskedWater) AS SumOfRiskedWater
FROM (tblWellPads INNER JOIN tblWells ON tblWellPads.PadID = tblWells.[WellPadID]) INNER JOIN tblProduction ON tblWells.WellID = tblProduction.WellID
GROUP BY tblWellPads.WellPadName, tblProduction.ProductionDate
ORDER BY tblProduction.ProductionDate;
This query basically takes all the Wells coming from a Well Pad and gives me the total flow on that particular ProductionDate. The totals will change, obviously, as we manipulate the date that we bring a well online.
Right now I'm just focusing on getting the oil totals out as whatever I do there I'll apply to the Water and Gas.
End Result: So the end result is getting the peak/max flow from each well pad and determining (outside of Access) how that will affect the hydraulics of the pipeline. The database is used to adjust the ProductionDate, which in turn affects when the max flow from each WellPad is observed. I'm doing this in Access because my computer is choking on the large spreadsheets I'm having to deal with, and it seems to make a lot more sense to use Access for this anyway. I was able to do this with PivotTables pretty easily. If there was a way to translate a PivotTable result into an Access query that would solve all my problems!
Thanks for sticking with me through this!
Thanks!!
Matt