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.