Results 1 to 7 of 7
  1. #1
    rlmax is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    6

    Time-series database for monthly loan balances

    Hi All,



    I have been using Access for a few months now primarily as a raw data storage tool to house monthly snapshots of a loan portfolio, simply because it's been more efficient at storing the data than individual spreadsheets. I have 12 tables, each representing a snapshot of the loans and their balances at month's end. There are no established relationships between any of the tables or their records. Each row has around 25 data points, consisting of Customer info (name, address, DOB, etc), Loan characteristics (type, status, term, rate) and Loan-specific info (original date and balance, current balance, credit score @ open). My biggest issue is that I do not know to design (even conceptually) a database that will allow me to track a loan or a pool of loans overtime. At the very least I'd like to be able to create a query to see all monthly balances by loan type over the last 12 months.

    I apologize if I haven't been very descriptive, but i'll do my best to clarify if needed.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you should have 1 table , not 12.
    you can create a master table by appending all 12 into a single table. Then you can run queries over time.

  3. #3
    rlmax is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    6
    So I should have one table with over 3 million records where the overwhelming majority (90%+) is repeating information? This was my original thought, but it didn't seem very efficient. I assume I'd need to add in a column to each existing table stating what month the balance is for, right?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In this forum you are going to be told to learn and design a "normalized" database, where data is never repeated and multiple tables all joined together contain the data. You, however, seem to be trying to use Access as an advanced Excel. In this case get to know how to create queries to take the data as it is and build up subsets of data. For instance, you would create 12 queries, one for each table, that produce the results that you want. Then create a UNION query which joins them all together and displays the results, either in datasheet view (query), form view or a report. Or create a query which sums/averages/totals/etc off this one.

  5. #5
    rlmax is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    6
    You're right, that's what I have been trying to use it for, mostly because I started using Access before I even really understood what it does and what it can do. The raw data I have comes in a CSV format one month at a time, so I've been trying to organize the structure around the data set, not organize the data set to work with a structure. I am going to play around with creating a UNION query like you suggested, that seems like a really good solution.

    Is it possible to create a normalized database for monthly loan balances without having 12 separate monthly tables? Could I do this by slicing a master table into the three pieces I outlined in the original post and have those three tables have relationships with each other?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is a huge subject and I suggest you do a tutorial (or three) on table design.

    Separate out your data into transaction type data, things that happen on a continuing basis that you want to keep track of; and static data, data that doesn't change often, such as customer info (name, address, etc.) or loans (including characteristics). Each table will have a primary key field which you then store on the other tables where needed. For instance, the customer table primary key (e.g. CustomerID) will also be stored on the loan table (CustomerID) to enable you to know which customer this loan belongs to. Transactions will carry both primary keys so that in any query you can easily join all three tables together and see which loan and which customer the transaction belongs to.

    Get it?

  7. #7
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    You should check your file size - as 3M records may indicate you are approaching the 2G max - and this all may need to be moved to SQL Server Express. Otherwise you will definitely want a strong PC with a lot of RAM.

    So - we are all asking more or less - why 12 tables? and you state there is no relationship between them... If they are all the same structure - if you write a query for table 1 - - then you'll have to write that query 12 times and run them all separately. While, if they are all the same structure, consolidating them into a single master table would allow you to write just 1 query but as you note, will churn thru a lot of records. All things in engineering have trade offs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple loan database design
    By Julieve in forum Database Design
    Replies: 5
    Last Post: 08-20-2016, 02:36 AM
  2. Cannot find Watts a Loan database
    By Lou_Reed in forum Access
    Replies: 2
    Last Post: 09-24-2015, 02:39 PM
  3. Loan and Savings Database
    By misscupid in forum Access
    Replies: 1
    Last Post: 03-11-2013, 02:44 AM
  4. Make time series data
    By klh_2012 in forum Access
    Replies: 1
    Last Post: 03-13-2012, 02:19 AM
  5. loan database
    By jlyon in forum Access
    Replies: 5
    Last Post: 07-23-2010, 07:24 AM

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