Predictive modeling / Excel / Access

[torok]

Ok, I've trying to do something complicated and could use some advice.

Right now, I have a whole bunch of tables and queries in an Access database. I'm using the data to do predictive modeling, and their are a series of "scenarios", so that every table's data is duplicated and appended to the table with every scenario, only the scenario field values are changed.
So when I create a new scenario, the database doubles in size and I'm allowed to go in and modify all data pertaining to that scenario. It works great. So far so good?

Only problem is: I want to modify the data in Excel. With formulas. If one of my variabls changes, say, 2% per year for 20 years, I don't want to calculate each number by hand - I want to punch in a formula, fill down to update the data for that scenario, and recalculate the model.

Can this be done?

I can get the data *into* Excel, but after modifying it can I update the database tables with it, without having to copy and paste stuff?

Is there a better way?

Much appreciated for anyone who manages to read this far!

 

[Cosmos75]

You may want to research UPDATE QUERIES.
From Access Help File
"Makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables."

Take a look at this link to see if it might help you out.
Dynamic Calculated Field in Query Needed
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=36953

 

[torok]

Thanks for the link (and the response!)....

That's an interesting approach, and one I'll definitely take a closer look at. The volume of data I have to manipulate *might* make that solution impossible, not sure yet. (You wouldn't believe how much data I've got for each scneario - several dozen variables per year over a span of 20 years). If someone wants to, say, increase one variable by 5% per year for the first 5 years, then 10% per year for the next 10, then 15% for the last 5..... well, update queries might not work. Alternatives would be formulas in Excel, or calculate every variable by hand

Or perhaps I'm not understanding the full capability of update queries. If anyone has anything to add, I'm all ears!

 

[The_Doc_Man]

While you certainly could do the update you are describing, there are other approaches.

This one that came to me is bizarre enough that it might work for you. OK, listen carefully... You don't care what is in the tables. (Trust me before you get riled up.) You really care about what you see when you do whatever modeling you are doing. I.e. you worry about the input to your model, not the original table data.

Suppose that you have some sort of sequence number or generated number that can be a unique key. Your basic (reality) table has all of your data organized by this key. Remember, it has to be unique for this to work.

Rather than generate duplicates of this entire table with updated values, just generate a much shorter table with only the fields that change. The more fields you have in the reality table that DON'T change, the better the advantage you get with using this method.

Once you have generated the short table with the updated fields, write a two-table JOIN query that joins the invariant fields of the reality table with the varying fields of the scenario table using that unique key or sequence number as the basis of the JOIN. Then whatever you do, do it to the SELECT query instead of to a copy of the reality table.

Since you didn't describe your data set, I don't know if this will offer enough of an advantage. But if it does, give it a rip.

 

[barbarossaii]

Hi Torok,

as I understand you have some data, which is used as basis for your calculations, but changes frequently.

If I don't oversimplify ur problem this 'easy' solution could work:

use Excel to store (and manipulate) the basic data and l i n k a table to this xls file. Your 'old' versions of your basic data would be saved under another xls file name (for refference....); if you need to change back just replace the file that is linked.

To do some 'fine-tuning' of ur basic data, I would use something similiar to The_Doc_Man's proposal (but in xls):

The xls file should have folowing Colums:
Orginal Data.....Times....Added....Actual data

And all the "Actual Data" fields (in xls syntax !) are calculated: "Original Data" x "Times" + "Addded" (do this once and use xls to fill the lower cells w/ the formula).

Base ur calculations in Access on the "actual data" column and u should be able to manipulate ur original data in xls in every way; a plus would be that u can use xls to fill several cells by copy & paste or increments,...(5% hike in yr 1-10, later 10 % in yr 15-23,...). another is that u can protect the whole spreadsheet except the "times" & "Added" columns to prevent entering mistakes.

For starting fill the "times" column in xls w/ "1" and the add w/ "0". Later u can change these two colums to do the finetuning of ur data.


HTH,

Barbarossa II

 

[torok]

Thanks very much to everyone who replied! ALl solutions have merit, and I'm going to have to do a little research and testing to figure out the best way for this particular case. The final approach suggested is one similar to one that I'd been thinking about, only better. Since this will be a critical app, I'll have to make sure it all works, but thank you all again for taking the time to think about this!