Unanswered: Database rookie with a complex problem - seeking bright minds for a solution.
First time on this forum for me, hopefully itís the correct place for such a question. I did a bunch of searching prior to posting, and I have bits and pieces of information together - but itís still all a little unclear to me. Keeping in mind, this is all coming from a guy whoís only recently learned what SQL is, and Iím still not sure I have my head wrapped around it yet.
Iím going to try and paint a picture of what exactly I do, and my end goal is to come up with a more optimized way of doing it. Iíve recently been assigned new responsibilities, and itís really peaked my interest in BI and Database management. I have no doubt itís done very inefficiently since Iím at least conscious of all the other tools/software/functions that exist.
I have a database in Access that is currently ~2,500,000 rows of data with 15 columns of data in each row. Static data, nothing fancy only values. This database is continually growing via copy/paste from Excel. In Excel I use a series of VLOOKUPís and other data pulls into to assign rates and other variables to each given row of data that comes to me. This happens on a daily basis, as dates change rates changes etc. So each day I will be assigning values to new data, and copy pasting it into Access after Iíve assigned all the variables necessary.
Every time this data changes, I am asked to reconcile balances. So I run an Access query with specific data I need (i.e.: >Specific Maturity Date) with a unique line of business identifier. One of the larger balances I have to reconcile makes up ~800,000 of these 2.5MM lines. So I end up with a large Access query, and export it to Excel.
Once I have the Excel up, I have to add several columns to calculate specific things that are required. So each row that was originally 15 columns now has ~20. Each of the additional columns is a formula that is referencing that rows data. Things like weighted averages and such. I then pivot (keeping in mind 800,000+) the data, and compare balances and rates with each corresponding line of business that has done the same on their side.
So essentially Iím comparing 2 pivot tables at the end of it all. The issue lies in trying to manage these large amounts of data in Excel with all the formulas and pivots, itís become cumbersome. Takes 20 minutes just to save the file.
This is all just to do a simple balance reconciliation mid month; I have to do a more comprehensive check at the end of the month which goes into even more detail.
Like I said, Iím new to this, and itís interesting to me. Iíd like to come up with a more efficient way to manage this data and optimize how I can reconcile it as often as needed without software limitations. I'm all ears.