My company's IT Dept Blows : )

[jeffcravener]

Ok, now, with that off my chest, let me get to me question : )

I am a local call manager for one of Aetna's customer service center sites.

I am new, and they have always used excel to do all their reporting. The problem I have with this, is for 5 ifferent reports, you have to key in the same info more than once!!

So, I started automating some of the reports using VB, BUT, then I came up with the ingenious idea of proposing that we use Access to store all the data, then I would create an app in VB to pull all the data from TCS and CMS and centralize it. Then we could query and info we wanted....

BUTTTT....the freakin' IT dept won't support Access, or any database on the network. They said I could keep it on my PC and do the reporting for my site, but I was hoping to help all the sites out.

Now, I had heard the word 'pivot table' before with excel...not sure what it is, but, is there a way to use excel as I wanted to use access? MAybe have different workbooks be the tables, and then link them together somehow?

Keep raw data in 2 or three reports, and then have 'template' reports that are read only, and then use VBA to create combo boxes to say which site you want (the combo box would be loaded from another excel sheet)...etc...etc...

Any ideas/suggestions/rejectons would be much apprecited : )

 

[fneily]

Sounds like you're just keeping a big Excel "list" with sites and their info and just want alot of summarizations. If you learn Filtering and Pivot tables, you'll do all your work in 1 minute. I'm not kidding. I've taken a large accounting firm, gypsum company, and salereps from doing three days of work down to a single minute in producing 180 reports. Pivot tables are awesome. And to think it's an original Microsoft product!

Neil

 

[jeffcravener]

Awesome! That's what I was hoping to hear....thanks!

 

[KenWright]

Jeff, you really need to take a look at Pivot Tables before deciding they are the way to go for what you want. Apart from the unhelpful (and uncalled for - Absolutely not the norm), initial response you got in the MS Newsgroups, the rest of the answers should have been helpful.

To get a feel for Pivot Tables, and it is well worth learning how to use them, regardless of whether they figure in your final solution or not, you might want to take a look at Debra Dalgleish's intro at the following link:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

This will at least give you a feel for whether or not it will work in your particular environment.

Regards
Ken...............

 

[danvlas]

You're the manager...

If I were you, I'd call the IT department for a meeting and start a debate on switching or not to a new system, based on facts and not on personal egos of some ITs that think they know it all...

A few thoughts:
Excel is a great tool for calculation and analysis, no doubt about it. But it's the wrong choice for storing related data to retrieve it later.
Also, it sucks when used by multiple users at the same time.
Also, it's a flat file system. You have to fill in all cells in a column, even though they are repeating, to have a workable set of data. It's easy, I know....but unnecessary.
Also, it allows 'orphan' data to be stored, thus more mistakes and more hours of searching what was wrong and why... in my experience, in such cases the IT guys always blame users for not taking care of the data...and the managers buy it

The right way to work (im my opinion) is to have a database - be it Access, SQL Server, Oracle, who cares- to store data in normalized related tables and export relevant rows of information to Excel.

In this way you can get the benefits of both a database that is designed to store the information and of Excel that would do the calculation and analysis in no time.

Remember, an Excel worksheet can store max 65536 rows including the 'table header'.
This number is a piece of cake for any database.

If no success, tell your IT guys you're going to look for database professionals to do the analysis and design. Maybe point them to these forums, maybe they learn something...Just mind they don't cut your internet connection




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html