use a from with calculated fields, to populate a table

[araise]

I have imported an Excel spreadsheet int o Access 2013. So far, so good. It is AT THIS POINT a flat file. This may change in the future.



I. Stock index futures database -- Primary fields: Date, Open, High, Low, Close
II. 200+ fields per record, of which only 11 are hand entered (including the 5 just mentioned) on a daily basis, so, obviously the date is of utmost importance.
( I do have an auto record ID set up in the Access DB, but almost all queries will be based upon the date field)
A. The remaining 190 or so fields are calculated from these 11

III. From an earlier post I made, it was recommended NOT to have calculated fields in the database, as by definition, it's purpose is a repository of data, not a spreadsheet. I concur wholeheartedly.

IV. I need those fields to be populated on a daily basis, though, in order to run queries

V. My question, then is should I use an Access form with 190+ calculated fields ( If it's a one time thing, I have no problem doing it) in order to populate the table every day.
And if you have a quick idea of how I can do this or direct me to any vids, I'd be very appreciating.
Thx in advance

[John_G]

Are the calculations to create the data for those fields complex, requiring VBA, or can they be done using the MS Access built-in functions? (It may not matter).

What I would do is create an append query to calculate all those fields and append the records to your table (or an update query, if the records are already there).
Question: How long is that calculated data needed? Is it used in queries that span a date range, for example, or is it only needed for the current date?

But you are still keeping all that calculated data in a table. Why not create a select query which takes the 11 fields from the futures table, and calculates the other 190 or so. Then you could use that query as the source for your other queries.

There are pros and cons to this approach. How many total records do you have in your database? If it is a large number, then the time needed to do all those calculations each time might create noticeable delays in response time. (There are probably ways of dealing with that problem, though)

The advantage I can see is that if you need to make changes to what you care calculating (for example a new calculated value), then all you need to do is change the select query, without having to worry about modifying the table.

[araise]

Many thanks!
I. The calculations are not as complex to require VBA. Mostly IF IF(AND) etc type calcs in Excell
Once calculated for the day, they do not change for that record. These "new" records will be used in future calculations as part of a time line calculation
i.e in Excel, now, each day, after the close, I calculate the (HIGH+LOW+CLOSE)/3 , which gives me the "Pivot Point" ( one of the calculated fields) for the next day.
From that "Pivot Point" calculation and the same calc for the two days prior, I calculate another field which is the "3 day avg."pivot". (Pivot Point today+Pivot point yesterday+ pivot point 2 days ago)/3
From that calculation I have another field which is the result of the 3 day avg pivot + 50% of the Range: (HIGH - LOW)
And, of course the "Range" is another calculated field.

I know it sounds nuts, but once those 190 values are calculated for that day, they do not change, and are now indeed permanent data points for that specific day. These 190 calculated data points do not tell me much by themselves. It's through additional and more sophisticated queries (that Access allows for), does the data start to paint a picture for me.

In Access I would like , then to make queries as to, say: Over the last 600 days of data, how many times did the HIGH ( high price of the day) exceed the 3 day avg.pivot on, say a Monday or maybe a Friday, etc.

The append query makes sense from the standpoint I would set it up once and have it make the 190 calculations and append the record for the day. I would use that query daily.

From there, I could make the more serious queries using those data points now in the table like the example.

If this sounds reasonable to the Access aces out there, I'll check in to that append query option
let me know if this makes sense.
Thanks in advance!

[ssanfu]

PMFJI,

The calculations are not as complex to require VBA. Mostly IF IF(AND) etc type calcs in Excell

Forget that you have used Excel. HOW the calculations will be done in Access is very different than the calculations in Excel (and possibly more difficult).

I calculate another field which is the "3 day avg."pivot". (Pivot Point today+Pivot point yesterday+ pivot point 2 days ago)/3

This is what I meant about more difficult. In a query, you cannot easily say "what is the is the 'pivot point 2 days ago'?" because a query works with the current record data. I think you will be writing many UDFs to get the calculations you want.

That is where the UDF comes in. Instead of looking 1 row up and 2 rows up (as in Excel) to get the pivot points to calculate: "(Pivot Point today+Pivot point yesterday+ pivot point 2 days ago)/3", you write a VBA function to get the value from 1 day ago and a function to get the value from 2 days ago.
You would use something like: (PivotPointToday + GetPP1(Date()-1) + GetPP1(Date()-2))/3

The function "GetPP1" would be a VBA function that would retrieve yesterday's Pivot Point. "GetPP1" would be a VBA function that would retrieve the Pivot Point from two days ago.

Or you could write the function with an additional argument for the number of days ago. (something like "GetPP(Date(),1)" and "GetPP(Date(),2)")
And you would have to take into account weekend dates that have no records.

I would start by creating a SELECT query with all of the calculations, and once all calculations are correct, change the query to an UPDATE query.



An alternative would be to do all of the calculations using VBA. I would probably use the VBA approach because of error handling/verification ability and being able to to single step through the code to check the calcs and results.


I sure hope you have a good naming convention!!

[araise]

ssanfu,
Thx for the feedback. i figured I may well have to write a function for each of the calcs. Time consuming, but once it's done, it's done. using the select query, then once all are verified, change to append query appears to be a very sound approach.
I'll get on it! Thanks again