Pulling out data from a spreadsheet

Question

I need to add information of clients who have visited the office, which department, who they were referred by, who they were referred to etc. I have linked a screenshot of what I have currently made in Excel. Most of the columns have drop down menus so that it is easy to choose options. I basically need help with pulling information out. I want my database to do the following:

-Total number of clients that the office has seen as a whole

-Total number of individual students each department in the office has seen

-The ability to distinguish between clients who have attended an event and those who have met one on one with a staff person

-Total number of appointments at any given time in the office as a whole or individual departments

-Total number of appointments for each individual staff member

-Total number of clients referred to a particular department and the % that actually met with that department

-Data about a particular client (I have a meeting with John...oh look he has already met with someone else about this)

-Demographics (how many first - year clients have visited the office)

-Numbers that had meetings for a specific reason.

Is there any particular database application I should use that will allow me to do all this (like Access, though I have never used it!) ? Or can I do this in excel? Help!

https://fbcdn-sphotos-c-a.akamaihd.net/hphotos-ak-xpa1/v/t1.0-9/10336734_10154382532535241_3603881073715416650_n.jpg?oh=b9684843fcea882e4f76defa1da12808&oe=54570A7E&gda=1413624424_eb8d7965194751df3ff70ad2f00d31b0

Answer 1

You can definitely do all of this in Excel with VBA - the application used depends entirely on what you need. Some things to consider:

• If you will have multiple clients (Excel applications) that are going to get and post data you'll be better off with having the data in an actual database, though you can use Excel for this too.

• If it's only the one application that cares about the data it's perfectly fine to just have the database be a sheet on your Excel file.

• If the data is sensitive and you have to protect it - you really want to use a database as cracking Excel is relatively simple.

Regarding which database application / format you should use also depends on your needs and preferences. Access works just fine if you are storing the database locally or on a network drive. You can also import or query data from Access easily.

Answer 2

I would use the Power Pivot Add-In for this. It has a rich functional syntax (in particular CALCULATE) which I expect would be capable of all of those requirements.

The output presentation would be Excel Pivot Tables and Charts, based on the Excel Data Model with calculations that you develop in Power Pivot.

http://www.powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif/

That site is probably the best starting point for someone with Excel skills wanting to learn Power Pivot.

I would leave the data in the Excel table - Power Pivot can link to that. The main benefit of the various flavors of SQL including Access are improved storage technologies - they will not magically create those calculations for you without complex SQL coding.