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

    Organizing a database of monthly portfolio holdings data

    Hello,

    I currently have 36 files imported to Access (12 months x 3 tables for each month based on product category) and I am trying to figure out the best way to consolidate them. Each file contains the holdings in the loan portfolio for that category at the end of the month and the sum of the 3 files for a month equals the month total. I don't have any relationships established. I have been using Excel to run a query to the database, extracting the data I need from each Access table into 3 worksheets, summarizing with pivot tables on a fourth and saving the file by month. This has worked fine so far, but I can't imagine it's the best way to organize my data in Access. What I would like to do is consolidate the tables into a relational database, but I can't figure out how to do that while keeping the data separated by month.

    The vast majority of the information month to month is going to be repetitive, such as customer information. The only data that changes is the account balance and any new customers for that month. Zero balance accounts will still be listed but will be classified as closed. The 3 monthly files aren't mutually exclusive, as a customer can have a balance in either one, two or all three product categories.

    My primary objective is to lookup monthly balance totals given certain criteria, whether it's by loan characteristic or customer characteristic. The individual information does not matter as much. Would it be best to create relationships between the 3 monthly files each month, then create 12 monthly consolidated queries? Or is it a better idea to completely start from scratch? My only concern with doing that is the raw form data is in txt files that have to be formatted by fixed width.



    I apologize for not being more descriptive, I have limited experience with Access, so I've described my situation as best I could.

  2. #2
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Could you simply add a month field to each of the 3 tables? You could then filter by month and export the data that way.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would it be best to create relationships between the 3 monthly files each month, then create 12 monthly consolidated queries? Or is it a better idea to completely start from scratch?
    It depends on the structure of the tables. Ideally, there would only be 1 table instead of 3. Actually, 2 tables would be ideal - a table for the customers and a table for the data. As AccessPower said, there would be a field for the month (NOT named "month" - that is a reserved word) and quite possibly a field for the year if there is/will be multiple years of data.


    My only concern with doing that is the raw form data is in txt files that have to be formatted by fixed width.
    Why do/would the text files need to be formatted fixed width?


    Once the data is in 1 table, creating the reports is simple. If for some reason you wanted to keep the data in 3 tables (non-normalized), you could use a Union query to get the data into 1 record set and use that record set to create the queries for the report(s).

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

Similar Threads

  1. Replies: 5
    Last Post: 09-26-2014, 08:07 AM
  2. Replies: 6
    Last Post: 11-10-2012, 09:49 PM
  3. Need help re-organizing data
    By saccolicious in forum Access
    Replies: 2
    Last Post: 10-13-2012, 07:15 AM
  4. Replies: 17
    Last Post: 01-12-2012, 11:25 AM
  5. Help needed on Contracts Portfolio database
    By futurecoder in forum Database Design
    Replies: 4
    Last Post: 04-04-2011, 09:08 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