MS Access to Excel: Processing of Original Data 1

[MattGreer]

I have oil and gas production data, the amount of which has exceeded the capabilities of my work laptop to continue using Excel to visualize and manipulate it. The data is based on certain dates of when an oil well is brought on into production and the associated production rates, month by month over time.

I'm familiar with setting up tables and queries in MS Access and ensuring they eliminate redundancy (i.e. I know about the normal forms). So I'm pretty sure I have things set up in Access correctly.

I want to look at the base data as well as sensitivities. Let's say the well produces 30% more, or 30% less, of oil, gas, or water. Or the date of when a well or group of wells is brought online changes. How will this affect the associated pipelines and central collection facility?

My question is more related to data processing speed, I suppose. It's been a while since I've linked up data from Access to Excel so I'm not even sure about the steps or how the data will show up. I guess my question is, should any calculations to manipulate the data take place in a query in Access, or should I do this in Excel?

So for example, let's say I decide to bring a set of wells on at a later date, pushing their production amounts back, oh, six months. Should I adjust those dates in Excel after the data comes over from Access, or should I do that in Access, and then bring the results of the query into Excel? And if I do that in Access, is there a way to enter the desired adjustment in Excel, have that injected in a query in Access, and then the data comes back to Excel? Would this have to be done through VBA or can I simply enter a value in Excel?


Thanks for your guidance!

Thanks!!


Matt

 

[simian336]

Done correctly, it is always better to work with a smaller dataset. Query just what you need and then bring it over to Excel.

Simi

 

[lameid]

I agree with Simi the smaller dataset the better. You might consider adding an inedex for query performance depending.

At to your question what to update, Access or Excel, you have decided that Access is the authoritative source for the data, so you should update it with changes unless you are just running hypothetical scenarios and you don't need to retain the parameters. If the latter my inclination is to minimize spurious updates a database and use Excel. On the other hand, your methodology may favor one or the other methods strongly and that would emerge as the obvious choice. I can see it both ways depending on details. This may end up being as much as a personal style choice as anything. There is often more than one good solution.

 

[MattGreer]

Thanks for replying folks, I appreciate your assistance!

At the end of the day I did a lot of this work on my home computer (faster CPU, more memory), and then recently got 64bit Excel which has sped things up quite a bit. Very nice. I had to abandon the effort to integrate Access and Excel this time, but I will be performing this same type of work over and over again. So...

There's two types of manipulations I would want to perform:
[ol 1]
[li]Change the expected production data by a certain percentage; a constant multiplier over a given time period.[/li]
[li]Change the date of the initial production, which results in a different amount of fluid being brought down a pipeline. It would show the predicted production if we brought on a well sooner or later in time.[/li]
[li]Both 1 and 2[/li]
[/ol]

The point of both of these exercises is to determine the size of pipeline needed, and then the size of any production facility that will need to handle the influx.

I think based on what you all are saying, it's better to update Access and then bring it into Excel. This makes sense. So now I'm back to database design.

What I'm not sure of is whether to create an extra field in the production data table to indicate "new maniuplated production date" and "new manipulated production rate" so that I can keep the original data intact. So I guess that's where my knowledge of database design falls down. What is the proper way to create a duplicate set of data based on an original set? Separate table? Or a couple duplicate fields in the same record?

The production table looks like this right now:
[tt]tblProductionData
Field 1(PK): ProductionID - Just an index
Field 2: WellName - this can have multiple of the same values as it describes the production from that well on a given date
Field 3: ProductionDate - date that the production is estimated
Field 4: GasRate - rate on that date
Field 5: OilRate - rate on that date
Field 6: WaterRate - rate on that date
[/tt]

So would it make sense to add fields "NewProductionDate", "NewGasRate", "NewOilRate", and "NewWaterRate"?


Thanks!!


Matt

 

[Darrylles]

Matt,

If you think back to your normalisation learning, duplicating fields is a no-no.
What happens if you decide later that you want a 3rd reading, and want to 'remember' all 3? Would you add a third set of duplicated fields? What about 4,5,6, 9999?
It's a production data RECORD for a single, specific date.
You have an intention to store another RECORD of production data on another date for the same well, so do that: simply add another record to tblProductionData for the second date results.

You can always tell the 'order' in which these occurred by the ProductionDate.

ATB,

Darrylle