One Very Large (123 sheets) Into a Tenant/Rent Database

Ok, so when I took over managing the storage buildings I made "pretty" spreadsheets in excel to log the payment information for the tenants. In other words I went for what it would look like printed out versus user friendly. What I ended up with was a VERY large amount of workbooks, I used some coding to get all the information from the separate workbooks into one large workbook. Now (6 months later) I would really like to be able to have receipts and late reports generated from these "pretty" spreadsheets. So I've begun converting the rows and columns I was using into tables so that I can sort it in some manner. But how should I go about getting excel or access to give me a list of everyone who has payments due and how overdue they are? I really don't want to start all over :/ I can only post on here the example version of the spreadsheet because the real version has sensitive information on it. But I have one of these for every single (one hundred and twenty three) customer. Plus we have another storage area that is processed through a different account and is separate and I also have a file for all of those customers.


Example.xlsx

That example is literally what the real ones look like. We just kept track of dates paid which month they paid for how they paid and how much they paid. i'm not sure what you mean by blank fields? the rows that are empty were for future payments. Like i said it was originally designed for print not database so hindsight is kicking me in the behind. there is a small program that i really like called RentTrackerPro that is free and has a .mdb database. But I have Access 2013 and it refuses to open it. I would just use the RentTracker program but it's so ancient looking that I can't stand it and I would like to edit some of it to include the tenants emergency contact details and if they have any other AuthorizedPerson(s) allowed access to the unit that persons information as well. Here is the .mdb of RentTracker.

RentTracker.zip

One other thing I should note is I cannot use the Unit# that the tenant rents as a primary key for the tenant because tenants tend to upsize and downsize storage units so the unit # can change. and as tenants move out new ones move in to replace them. Basically I believe I need tables for tenants, emergency contacts, authorizedpersons, units, buildings, payments, and i'm not even sure what else.

Yes that is what i figured since I couldn't open it to even inspect it and it has it's own interface it just looks like something out of windows 96

Ok I've got the tables modified. I know there are errors I am quite terrible with Access.

MyRTstorage.zip

Use the autonumber feature in Access and create a one up number for each tenant. The primary key need not have any significant value other than being unique.

Sounds like you have a good handle on the tables. You might want to look at this link to see if there is a model that you can piggy back on.

http://www.databaseanswers.org/data_models/index.htm

That site is very cool!

This is easy to do in Excel.

1) Loop through each workbook using VBA and paste all the info in the "Data" TAB attached
2) Columns A-I are from the individual workbooks
3) Copy down formulas in Columns J-L
4) Go to the "Pivot" TAB and refresh the pivot table

This shows the Unit, Tenant, Last Month Paid, Overdue Status and the days overdue.


**If you want me to write the VBA, post all your workbooks to google docs and PM me the link

Originally Posted by Xx7

This is easy to do in Excel.

1) Loop through each workbook using VBA and paste all the info in the "Data" TAB attached
2) Columns A-I are from the individual workbooks
3) Copy down formulas in Columns J-L
4) Go to the "Pivot" TAB and refresh the pivot table

This shows the Unit, Tenant, Last Month Paid, Overdue Status and the days overdue.


**If you want me to write the VBA, post all your workbooks to google docs and PM me the link

I WOULD LOVE YOUR HELP!!!! My only problem is the sheets have the customers credit card numbers on them, so i would need to take them all off of them first. basically its one big workbook and the sheets are titled "1 - Customer Name" thru "80 - Customer Name" and "B1 - Customer Name" thru "B42 - Customer Name" and then we have another set of storage units too.

And some of them have records dating back to 2006