An opinion on the best way to modify data

[Jameo]

Hi all. I will try and set the scene to help you understand what I am trying to acheive.

I currently have an access database that has data for a variety of gas storage sites.

Ultimately I output a table to excel, with the following headers

             |         SiteName        |         SiteName        |
_________________________________________________________________
|Gas Day|Hour|Flow Rate|Deliverability||Flow Rate|Deliverability|

Basically the table displays the current flow rate, against the thoretical maximum flow rate. When the data is then exported to excel, it is then possible to follow a simple formula to take the current flow rate, from the maximum deliverability to give the theoretical maximum deliverability (max flow rate-current flow rate)

My current situation is that I now need to include planned outages into the data. i.e. at certain times in the year the maximum deliverability will change to a lower value.

In order to accomplish this I will need to add a calculation to change the deliverability value in the table, the easiest way would be to multiply the deliverability value by a number between 0-1. i.e. if there is no outage, the number is multiplied by 1, if the outage restricts flow rate by 50%, then i would multiply it by 0.5 etc. etc.

The curve ball in this situation is that the value needs to be changed on an hour per hour basis.

My question to you guys is, what do you think the best way of acheiving this would be? An append query determined by a form, to modify the access data? Or some VBA code to change the data once it gets into excel?

Any help would be much appreicated. If I have not supplied enough information, or have not been clear enough, please let me know and I will try to clear things up.

Many thanks

 

[Norie]

How will you be getting the outage details/data?

If it's just a single figure that needs to be used in a calculation that could easily be added to the query that is output to Excel.

Assuming it is a query, if it isn't then it might be worth using a query to do this calculation and export that to Excel.

I don't think you need/want to use an append query, you don't seem to be adding any new records anyway.

If you wanted to keep historical data about the outage then you could have another very simple table for that.

The only fields I think that would need would be for date/time of outage and the actual outage.

When it comes to calculating the deliverability you can take the appropriate (latest?) outage value from that table and use that.

 

[Jameo]

Thanks for your help Norie. I have decided on a very simple solution, I have created a form where the user selects a date range and station, and selects the new flow rate during the outage. An update query then updates the actual flow rate with the newly selected values.

Obviously this is not ideal as there is no historical records kept and no way to easilly reverse the update if mistakes are made. However, as there will ultimately be a small user base for this db, hopefully this will be sufficient.

I am now struggling with a way to create sub columns from either a cross tab, or report that can then be exported into excel. (http://www.mrexcel.com/forum/showthread.php?t=546612)

Hopefully if this getrs sold it will be the end of my troubles

Thanks again mate