Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56

    Indexing strategy and code with there is a one to one relationship

    Hello. I have data that was originally 1350 fields wide and 2010 records long. In other words, too wide for MS Access which caps out at 255 fields per table. This is truly one to one relationship data (really it is).



    My strategy was to break it up into 250 fields per table and have 6 tables with one to one relationships. These tables would be logical in their division, but no one to many relationships can be created. (And yes it is one to one data.)

    My plan was to create an indexing system that tied them all together on a one to one basis.

    My questions:

    1) Is there a better way?
    2) What indexing strategy is best in this case?
    3) If possible can you provide vba code to implement the indexing system you recommend? (I have to automate the creation of these tables and how I index them each day. I just need help with the indexing strategy and the vba I could use to implement that strategy.)

    PS - please don't beat me up about what a relation database is all about and how what I proposed doesn't use the "power" of a relational database. (I already know.) This IS the data that I have (one-to-one), so what is the best course of action?

    thanks!

  2. #2
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56

    Indexing strategy and code when there is a one to one relationship

    Hello. I have data that was originally 1350 fields wide and 2010 records long. In other words, too wide for MS Access which caps out at 255 fields per table. This is truly one to one relationship data (really it is).

    My strategy was to break it up into 250 fields per table and have 6 tables with one to one relationships. These tables would be logical in their division, but no one to many relationships can be created. (And yes it is one to one data.)

    My plan was to create an indexing system that tied them all together on a one to one basis.

    My questions:

    1) Is there a better way?
    2) What indexing strategy is best in this case?
    3) If possible can you provide vba code to implement the indexing system you recommend? (I have to automate the creation of these tables and how I index them each day. I just need help with the indexing strategy and the vba I could use to implement that strategy.)

    PS - please don't beat me up about what a relation database is all about and how what I proposed doesn't use the "power" of a relational database. (I already know.) This IS the data that I have (one-to-one), so what is the best course of action?

    thanks!

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Queries are also limited to 255 fields so never will be able to show all these fields together as one dataset.

    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.

    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Tell us about the data - what does it represent? Where did it come from (subject matter and format)?
    What do you plan to do with it (some details)?
    Why did/are you choose Access to store it?

    You may already know about relational database and design, but where did you find this table with 1350 fields?

    I'm not going to preach relational database to you(seems you are expecting that), but please tell us/rationalize your decision for Access. I'd just like to hear the logic underlying your design decision.

    To me the indexing strategy has a lot to do with the requirements --usage.
    Ad hoc reporting, querying, user search and interaction......, updates... stability...

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,515
    Duplicate threads merged since both had a reply. Please don't post the same question twice.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56
    Quote Originally Posted by June7 View Post
    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.

    Quote Originally Posted by June7 View Post
    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...

    Quote Originally Posted by June7 View Post
    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.
    Last edited by mountainclimber; 07-07-2015 at 11:20 AM. Reason: respond to other post

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    I don't actually mean 'repeat' data, I mean same type as in: Product1, Product2, Product3, etc. which would be a 1-to-many relationship in properly structured database.

    The dependent tables don't really need a primary key unless they have related 'dependent' tables.

    How did you import the data into 6 tables? The key value should have been included and then would not have to modify tables to add it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56
    Quote Originally Posted by June7 View Post
    I don't actually mean 'repeat' data, I mean same type as in: Product1, Product2, Product3, etc. which would be a 1-to-many relationship in properly structured database.
    Yes, I understand. As I mentioned, GICS Sector is an example of what you are talking about.

    Quote Originally Posted by June7 View Post
    The dependent tables don't really need a primary key unless they have related 'dependent' tables.
    I plan to have a few dependent tables, but discussing them wasn't really important to the question. Thanks for the heads up.

    Quote Originally Posted by June7 View Post
    How did you import the data into 6 tables? The key value should have been included and then would not have to modify tables to add it.
    The code I use to import them is below. In short I use DoCmd.TransferText to pull the csv data in. It is part of a class where a private class property array of strings p_asDataFileNamePath_DivCol contains the csv file names:

    Code:
    Public Function ImportDividedData()'imports csv files of divided model data
    'cDM.CloseDatedModel False 'false b/c it was already saved during clean
    On Error GoTo Err_Execute
    Dim i As Integer
    For i = LBound(p_asDataFileNamePath_DivCol) To UBound(p_asDataFileNamePath_DivCol)
    Debug.Print p_asDataFileNamePath_DivCol(i)
        DoCmd.TransferText TransferType:=acImportDelim, _
            TableName:="tbl" & i, _
            FileName:=p_asDataFileNamePath_DivCol(i), _
            HasFieldNames:=True
    Next i
    
    
    GoTo cleanup
    Err_Execute:
    
    
    MsgBox Err.Number & " (" & Err.Description & ")", vbInformation, Application.Name
    On Error GoTo 0
    Resume Next
    
    cleanup:
    
    End Function
    How do I get a foreign key in there?

    Notes: I have an unknown number of tables/csv files since the 1350 field number I have is not static. I have an schema.ini file in the same directory as the csv files that I import so the data types are controlled.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Need to include the Ticker identifier in the import to each table. Don't think will be possible with TransferText unless you can get the value included in the array.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56
    Okay, how do I add it after the fact?

    Why doesn't this work to add the foreign key after the fact?
    Code:
    Sub CreateForeignKey()Dim db As DatabaseSet db = CurrentDb
    
    db.Execute "ALTER TABLE tbl1 " _
            & "ADD CONSTRAINT fk_tbl1_tbl0 " _
            & "FOREIGN KEY (Ticker) REFERENCES tbl0 (Ticker);"
     
        db.Close 
    End Sub
    Last edited by mountainclimber; 07-07-2015 at 12:46 PM. Reason: typo

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Did you mean 'Why doesn't ..."?

    How will the query know which Ticker goes with which record?

    I have never seen REFERENCES key word. But then I never have need for ALTER TABLE. Some SQL is not available to Access.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    What happened? Error message??

    Here is a M$oft example
    Code:
    Sub AlterTableX4()
    
        Dim dbs As Database
    
        ' Modify this line to include the path to Northwind
        ' on your computer.
        Set dbs = OpenDatabase("Northwind.mdb")
    
        ' Add a foreign key to the Orders table.
        dbs.Execute "ALTER TABLE Orders " _
            & "ADD CONSTRAINT OrdersRelationship " _
            & "FOREIGN KEY (EmployeeID) " _
            & "REFERENCES Employees (EmployeeID);"
    
        dbs.Close
    
    End Sub

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    What do you mean by 'csv files of divided model data'? Are these the 6 table datasets? Include the Ticker value in each of those CSV files when the 'division' is accomplished.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56
    Quote Originally Posted by orange View Post
    What happened? Error message??
    From post #6:
    The error I get is "invalid field definition "Ticker" in definition of index or relationship"


    Thanks.

    June7 - I corrected the typo. I took it from MS:

    This example adds a foreign key to the Orders table. The foreign key is based on the EmployeeID field and refers to the EmployeeID field of the Employees table. In this example, you do not have to list the EmployeeID field after the Employees table in the REFERENCES clause because EmployeeID is the primary key of the Employees table.

    VBA

    Sub AlterTableX4()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Add a foreign key to the Orders table.
    dbs.Execute "ALTER TABLE Orders " _
    & "ADD CONSTRAINT OrdersRelationship " _
    & "FOREIGN KEY (EmployeeID) " _
    & "REFERENCES Employees (EmployeeID);"

    dbs.Close


    End Sub
    The above is from this link:
    https://msdn.microsoft.com/en-us/library/office/ff196148.aspx


  15. #15
    mountainclimber is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    56
    Quote Originally Posted by June7 View Post
    What do you mean by 'csv files of divided model data'? Are these the 6 table datasets? Include the Ticker value in each of those CSV files when the 'division' is accomplished.
    Okay, I can include the ticker in each csv. Not a problem, but don't I still have to create some formal relationship (PK/FK)?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-22-2014, 11:09 AM
  2. Indexing (no dupes) dates where employee code = XXXX
    By snipe in forum Database Design
    Replies: 4
    Last Post: 12-12-2013, 03:19 PM
  3. Indexing question
    By Helystra in forum Database Design
    Replies: 8
    Last Post: 11-22-2013, 04:24 PM
  4. Replies: 1
    Last Post: 09-03-2013, 07:30 AM
  5. Looking for a strategy
    By 10 Gauge in forum Access
    Replies: 1
    Last Post: 08-29-2011, 09:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums