Database for Excel file

Topic

New Reply

Okay, here goes. Wasn’t sure if this should be posted here or in the one for spreadsheets. You guys have ALWAYS come through for me before so I’m hoping this request is do-able. It would be MAJOR for me. I’m sitting at my desk manually entering data into yearly contracts which have been created in Excel. (copy attached) You will see there are several formulas to make life easier. The numbers that create the contract costs are in a block off to the side, as you can see. As my boss’s business covers four counties with different sales tax, the formulas need to be different, obviously. Also, there are a number of tax exempt properties in all four counties. So I have created 16 or 17 different Excel files. My question is, would it be possible to enter this information into an Access database or Excel spreadsheet and merge into the contracts? Even if the figures/costs change from year to year, it would at least save having to manually enter over 800 names and addresses onto each contract. This year the costs didn’t change, so it would have saved MUCH time had it been done last year. Any other suggestions, please let me know. Crossing my fingers! Thanks!

Reply | Quote

Replies

Actually, it would be possible to do the entire thing in Access if you chose to. Or you could put just the property/contact info into an Access database, and then use Automation from Access to create each Excel file from an Excel template. That way you could put the cost data for each year into a table, and do the costs as well. It would be a good sized development effort, but if you have all of 2010 to do it, the time savings in 2011 would be substantial.

Reply | Quote

I think I would keep the Excel files since they are already set up. In the past I have only set up very simple tables and queries in Access so, to start, would I set up a table with the variables (names, addresses, costs)? I need to take just one step at a time since I’m not familiar with a lot of the features in Access.

Reply | Quote

That would be my recommendation as well if you haven’t worked much with Access? Are you somewhat familiar with VBA (Visual Basic for Applications)? If you were going to do any automation, you would need that background first. And you would need to understand the object model for both Excel and Access at least to some degree. I presume right now you have an Excel workbook for each client for each year they have used the service. I would start with a Customer/Client table that had basic information such as an ID number, name, service address, billing address (if different), and some fields to indicate pricing variations such as the senior discount. Then I would set up a second table to provide the different options a client might choose. Finally, I wouild create a table that had the ID number, a year field, and the selection of services that the client chose that year. That would be a start. Getting the data from your workbooks would be the next challenge. Then you would want to create some forms to simplify the editing of the data.

Beyond that, you start getting into tracking pricing by year, tracking payments, scheduling applications, tracking applications, etc. It would also be possible to write VBA code to populate the tables from the workbooks you already have. Your current data isn’t what the database world calls normalized, but as long as your cells in the workbooks are used consistently, and if you have a consistent naming convention for the workbooks, you should be able to acomplish that task in a fairly straightforward manner. Post back if something doesn’t make sense to you.

Reply | Quote

It seems that you could save a lot of time by using what you have and creating a couple of lookup tables in a linked sheet/workbook.
Then replace the formulas in the contract with vlookup’s or hlookup’s depending on the table setup. Of course you would have to designate cells in the contract worksheet to identify things like county, tax status, etc. and use these references in the lookup formulas.
Also investigate the use of the indirect() function to grab the values in these designated cells and plant them in the lookup formulas.
I don’t have enough information from your example to know if the pricing is fixed or if you need a table for this based on the size of the property but this is also doable in the same fashion.

I’ve included a file I used to teach a course. Check out the Data tab and the formula for Salary. Hopefully this will better explain what I tried to say above.

Good luck.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote