CODEWITHSUNDEEP
ms-accessdatabase-designtime-serieshistorical-db
Ron L
Ron L

Reputation: 51

Organizing monthly account extracts for personal use MS Access

I'm a bit of a newbie with databases and database design, but I'm hoping someone can point me in the right direction. I currently have 14 monthly loan extracts, each of which contain all accounts, their status, balance and customer contact info as-of month end. Not knowing what to do, I imported each of the monthly files into Access with each table acting more like a tab from an Excel workbook. Laugh away - I now know that's not how it's supposed to work.

I've done my homework and I understand how to split up part of my data into Customer and Account tables, but what do I do with the account balances? My thought is to create a Balances table, create a relationship to the Accounts table and create columns for each month. This seems logical, but is it the best way?

99% of my analysis involves trend reporting and other ad hoc tasks - tracking the total balances by product type over time given other criteria, such as credit score or age. My intended use is to create queries to select the data I need and connect to it via Get & Transform in Excel for final manipulation and report writing.

This also begs the question "how normalized should my new database be?" Each monthly extract is cumulative, so a good 75% of my data is redundant contact info already, but how normalized should I go?

Sorry for ranting,but if anyone has any experience in setting up their own historical database or could point me in a direction that will get me on track, I would appreciate it.

Upvotes: 0

Views: 62

Answers (1)

TK Bruin
TK Bruin

Reputation: 502

Best practice for transactional systems is close to what you expect: 1. Create a Customer table 2. Create an Account table 3. Create an Account Balance table 4. Create relationships from the Account to Customer, and from the Account Balance to the Account table.

You can create a column for each month, provided you have Year as part of the key of the Account Balance table. Even better would be to have the key for the Account Balance be Account ID and Date.

However, since you are performing analytics over the data, a de-normalized approach is not only acceptable -- it is preferable. So yes, you can (and perhaps should, based upon your use cases) put all the data into one big flat table and then compile your analytics.

Upvotes: 0

Related Questions