Originally Posted by
June7
Queries are also limited to 255 fields so never will be able to show all these fields together as one dataset.
I'll be doing a lot of aggregation calculations. For example, I want to get the mean and median of certain subsets of the data for all of the fields.
Originally Posted by
June7
One table will have to serve as the 'master' or 'parent' and the others as 'dependent'. Link on unique identifier (primary key) of the 'parent' which is saved as foreign key in 'dependent' tables. Which of your fields can serve as unique identifier? I presume this value is already saved in the 6 tables.
That is the direction I am headed currently. Thank you for confirming. I have tables tbl0 through tbl5. tbl0 has a unique identifier (Ticker) that I made the PK for that table with code like this:
Code:
Sub CreatPrimaryKey()Dim db As Database
Set db = CurrentDb
db.Execute "CREATE INDEX TickerID ON tbl0 (Ticker) WITH PRIMARY;"
db.Close
End Sub
I then added an auto number to each of the other tables to serve as their prospective PK (since they do not have a unique identifier). I did it with code like this:
Code:
Sub AddAutoNumberColumn()Dim db As Database
Set db = CurrentDb
db.Execute "ALTER TABLE tbl1 " _
& "ADD COLUMN SomeID AUTOINCREMENT"
db.Close
End Sub
I then try to add a foreign key with the following code and it doesn't work:
Code:
Sub CreateForeignKey()Dim db As Database
Set db = CurrentDb
db.Execute "ALTER TABLE tbl1 " _
& "ADD CONSTRAINT fk_tbl1_tbl0 " _
& "FOREIGN KEY (Ticker) REFERENCES tbl0 (Ticker);"
db.Close
End Sub
The error I get is "invalid field definition "Ticker" in definition of index or relationship"
I have also tried TickerID among other things...
Originally Posted by
June7
1350 fields is a lot of data for a single entity - why so many? Are there multiple similar name fields that hold essentially the same data?
It is numeric company/ticker specific data like:
Total assets
Total liabilities
Current assets
....and on and on and on.
In reality, there are about 15 (out of 1350) fields that have a one to many relationship like GICS Sector name and stuff like that, but I know what to do with those so it isn't/wasn't important to my initial question. I will break those out into their own tables.
No there aren't any repeats in the data other than those 15 cases.
Can you help me with creating the foreign key above?
UPDATE: Just saw another post regarding my question...
Yes, I saw that the question was a dup. Didn't know how to resolve that. I changed the title and it created a new thread. oops....
We are using access as a (free with Windows) jumping off point. We hope to move to using postgreSQL (which is also free) as our back-end to MS Access once I can prove the utility of a database.
Ultimately, I plan to do aggregation on each of the fields for various subsets of the data. Getting mean, median, standard deviation, etc. and store them in a Time Series database (another hurdle) allowing the user to chart them over time. I also plan to build alerts based on the changes in the aggregate values I calculate each day. Its a pretty big undertaking given our experience with this, but part of the effort is professional development for me.