Reply to: Todays rubbish job £18.12 per hour

[BoredBloke]

Shudder my ass.

So client wants a spreadsheet showing all their live orders, and then broke down by which are on which production line. They want graphs showing capacity on each production line so they can spot over resource levels into the future.

It must be a spreadsheet as various departments want to sort it , colour it in, and enter additional info to assist them with their job.

What is your proposed solution, that is shudder free?

A multi user database that each one can use to filter and run their own report. In exactly the same way you populate a spreadsheet so would I. I just use standard tools that are easy to maintain (because VBA is such a common skill set) using tools that are readily available.

Presumably all you do is create a connection to the data, throw a bit of SQL at it to grab the data and push it into Excel and format it in Excel using the Excel Object model (VBA).

[EternalOptimist]

But you have already said that your client has Access databases around that nobody knows what they do and are development dead ends. How does that reconcile with VBA being a £100 a day skill set.

You still have not said what you use to write your magic spreadsheets

wasting your time BB
i like the idea that you can put 'if' in front of something then answer a totally different question, and then talk cr@p for the rest of the thread.
you have the patience of a saint BB.

[BoredBloke]

No, but the point I was making doing a bit of vba, excel and powerpoint and creating a few databases wasnt that taxing and if it was a permie job that wages wouldnt have been that unreasonable.

But you have already said that your client has Access databases around that nobody knows what they do and are development dead ends. How does that reconcile with VBA being a £100 a day skill set.

You still have not said what you use to write your magic spreadsheets

[escapeUK]

Shudder indeed.

Shudder my ass.

So client wants a spreadsheet showing all their live orders, and then broke down by which are on which production line. They want graphs showing capacity on each production line so they can spot over resource levels into the future.

It must be a spreadsheet as various departments want to sort it , colour it in, and enter additional info to assist them with their job.

What is your proposed solution, that is shudder free?

[Sysman]

Shudder. Sounds like the sort of thing that is built as a stop-gap but ends up in production use for 10 years, "enhanced" by a string of people who ultimately vanish leaving hundreds of linked spreadsheets to later be untangled by a poor unsuspecting systems replacement programme.

Shudder indeed. Back in my permie days I was accused by senior management of wanting to reinvent the wheel by proposing a rewrite of such stuff.

It was a time bomb a-ticking.

Tick tock.

[escapeUK]

I've done a fair bit of interfacing myself and quite enjoy doing it, but it's certainly not something everyone can do.

No, but the point I was making doing a bit of vba, excel and powerpoint and creating a few databases wasnt that taxing and if it was a permie job that wages wouldnt have been that unreasonable.

[Sysman]

Well, as someone who writes systems from scratch and integrates them with other peoples systems / databases to me its easy.

I've done a fair bit of interfacing myself and quite enjoy doing it, but it's certainly not something everyone can do.

[escapeUK]

Once you actually leave and they can't maintain the thing that generates the spreadsheets, they'll just leave that untouched and build any add-ons required by using linked spreadsheets etc. It's basically inevitable.

Probably, personally I think they should just employ a young permie with a bit of programming experience who can follow what Ive done and make tweeks as required.

Their history says they wont and will just soldier on regardless.

[BoredBloke]

They have lots of easy to support access apps already, that no one can understand or support and are developmental dead ends.

Auditing? What is this, Health and Safety?

First of all, you have not said what you write your magic spreadsheets in, but if it is so obscure that only you know it, how do you expect the business to support it going forward and how is it any better for the business than the Access databases that "that no one can understand or support and are developmental dead ends". I thought you said databases were easy - why don't they just get a few £100 per day folk in to take them to bits and understand them. Tell me how they are likely to understand what you do when its all hidden away in code. If you got hit by a bus tomorrow, how long till your spreadsheets move away from being magic to worse than the developmental dead ends because nobody knows how they work.

Secondly, auditors - many companys have audits of their systems to ensure that they are fit for purpose and correct, especially ones dealing with money.

[escapeUK]

Oh, in exactly the same way that I would get access to do it. Except what I do is easier for the business to support and easier to audit.

They have lots of easy to support access apps already, that no one can understand or support and are developmental dead ends.

Auditing? What is this, Health and Safety?

[Freamon]

It doesnt use spreadsheets in that way, like I said its purely for the presentation layer. So nothing links to nothing, it opens a blank workbook and runs queries and pushes them into worksheets, formats them , collates them, makes graphs of them etc etc.

Before this, someone would manually run a query, paste it into a spreadsheet, he would then sort it by customer, insert rows , add formulas, make graphs etc etc It took him 3 hours so he only did it once a week. So their data was mostly out of date and no one trusted it. My system runs in 90 seconds.

Yes they will probably be using it for 10 years as it does everything they want it to and yes I do tend to end up being the person they dont want to let go. As everyones problem the answer seem to be me, not happy about that though.

Once you actually leave and they can't maintain the thing that generates the spreadsheets, they'll just leave that untouched and build any add-ons required by using linked spreadsheets etc. It's basically inevitable.

[JaybeeInCUK]

Well, as someone who writes systems from scratch and integrates them with other peoples systems / databases to me its easy.

I dont tend to do much VBA as I find it a bit amateurish, and I prefer to write my code as a stand alone program that then drives the spreadsheet (if a spreadsheet is needed as the presentation layer.) My current client coined the term "Escape's magic spreadsheets" which I quite like. Some of my recents programs live in the system tray in the same way that MSN messenger does and are automatically rerunning queries every 15 minutes (to give such figures as order intake for instance), which then drive the spreadsheets if someone wants more detailed info.

Easy is relative.

Indeed. It's one thing to import an Excel s/sheet into a new db.

Try modding out a brand new DB schema with several dozen tables, PK and FK constraints, sprocs containing inner joins/loops/global variables etc, throw in some IS packages for integration with your presentation layer, and THEN we'll see.

[BoredBloke]

It doesnt use spreadsheets in that way, like I said its purely for the presentation layer. So nothing links to nothing, it opens a blank workbook and runs queries and pushes them into worksheets, formats them , collates them, makes graphs of them etc etc.

Before this, someone would manually run a query, paste it into a spreadsheet, he would then sort it by customer, insert rows , add formulas, make graphs etc etc It took him 3 hours so he only did it once a week. So their data was mostly out of date and no one trusted it. My system runs in 90 seconds.

Yes they will probably be using it for 10 years as it does everything they want it to and yes I do tend to end up being the person they dont want to let go. As everyones problem the answer seem to be me, not happy about that though.

Oh, in exactly the same way that I would get access to do it. Except what I do is easier for the business to support and easier to audit.

[escapeUK]

Shudder. Sounds like the sort of thing that is built as a stop-gap but ends up in production use for 10 years, "enhanced" by a string of people who ultimately vanish leaving hundreds of linked spreadsheets to later be untangled by a poor unsuspecting systems replacement programme.

It doesnt use spreadsheets in that way, like I said its purely for the presentation layer. So nothing links to nothing, it opens a blank workbook and runs queries and pushes them into worksheets, formats them , collates them, makes graphs of them etc etc.

Before this, someone would manually run a query, paste it into a spreadsheet, he would then sort it by customer, insert rows , add formulas, make graphs etc etc It took him 3 hours so he only did it once a week. So their data was mostly out of date and no one trusted it. My system runs in 90 seconds.

Yes they will probably be using it for 10 years as it does everything they want it to and yes I do tend to end up being the person they dont want to let go. As everyones problem the answer seem to be me, not happy about that though.

[BoredBloke]

Shudder. Sounds like the sort of thing that is built as a stop-gap but ends up in production use for 10 years, "enhanced" by a string of people who ultimately vanish leaving hundreds of linked spreadsheets to later be untangled by a poor unsuspecting systems replacement programme.

It does have a ring of I'm the only one who can do this so you'll have to keep me here forever about it.

To be honest I've seen the same thing happen with spreadsheets and Databases written in noddy VBA. They get built as a workaound for whatever big system has been delivered which fails to meet all the goals or cannot be changed quickly or easily to meet a change in the business. As they stay there for longer they get a life of their own to the point that they become critical. At one place I was at recently, the traders daily P&L sheets were produced by a series of Excel spreadsheets that were developed in Excel 97 and had their initail roots in Lotus 123. Nobody in the business seemed to know how they worked or the logic in them and didn't want to touch them because of this.