Do you think this can be achieved with Excel?

JulyKnot

LucianH. Thank you for that. I've no experience of macros so need to learn about them first and then try to understand the steps you've created.

JulyKnot

I don't have Access

Lum

JulyKnot wrote: »

I don't have Access

This is for the best


Also, try the pivot table option before messing around with macros, especially if this is something you are doing in the workplace.

JulyKnot

It's actually just something I'm messing about with but useful as a learning tool for what excel can do too. Was hoping for inspiration from here and I've got it so thanks all.

I've never used pivot table.

paddyrg

Where I see you making your own life difficult is by using A-D for data then repeating different data in E-H, and different again in I-L... You're confusing storing data with formatting. Just make the list longer in A-D, and the data stays pure, then you can do all the formatting on a separate sheet with (for instance) pivot tables.

BTW, not sure what bad experience Lum has had with Access, but it is actually a very decent little database package if you know what you're doing. It isn't meant for big systems but had some great simple tools for small stuff. I can't say it ever lost any of my data, most often it is poorly designed systems atop access that cause issues!

Lum

In a single-user setup, if you don't do a compact and repair from time to time it will eventually break, though most of the time a compact and repair will fix it.

But my post was really about using Access in the workplace, where it becomes a whole new level of evil. Here is the lifecycle of an access database in the workplace:

1. Meet Bob (who I have named after a certain Microsoft product that I love equally as much as this guy). Bob works in the finance department of BigCorp and his job is to produce the weekly reports using figures from the big SQL-based finance database. This job is certainly one that could be automated by someone who knows what they're doing, but that isn't what's about to happen, it's about to be automated by Bob.
2. Bob, knows just enough to be dangerous, so he fires up Microsoft Access, creates a new database and configures it to pull data from the main database, he uses macros and a bit of VBA to pull figures from the main database and store them in his tables and manipulate them, he then uses the Access reporting tools to spit out the weekly reports in a fraction of the time.
3. This goes down well and Bob takes on additional responsibility, he extends his database to do more things, eventually Bob ends up with a few staff working for him, all of whom are using his database.
4. This is where the rot sets in as while Access can, in theory, handle multiple users. In practice what happens is that when you access it over a network you massively increase the chance of corrupting your database, and each new user you add doubles it again.
5. A year passes, bob has now, effectively, been a finance manager for a year, so he jumps ship to get a better paying job with another company. He hands in his month's notice and is asked to train his replacement. None of these people have any clue about how to develop in MS Access so all they get is shown how to use the manager functions he's added in his database.
6. Now we have a bunch of people using this access DB, they have no knowledge of how their job was done without it. It is now a business critical tool.
7. And then it breaks in some way and a ticket is sent up to IT, at which point some poor sod from desktops is sent down to have a look. Until now IT had no idea that this database existed, it isn't documented anywhere, Bob's code is unintelligible, doesn't follow any coding standards, doesn't have any comments to explain what the hell certain things are doing. To Bob the things he was doing were obvious, perhaps to another person with an accountancy of finance background, who can also code, they may be, but to the person from IT who walked into this mess it may as well be written in hieroglyphs.

If you're lucky, a compact&repair is all that is needed to fix this, and then IT can set about developing a supported replacement. If you're unlucky it broke because something changed, some financial regulation, or some stupid bug that stops it working after a certain point, or in certain specific circumstances.


When this happens, you're basically screwed.




I have customers who have 30-40 people accessing an Access DB that was never designed to be used at that scale. It literally breaks every couple of weeks. I've managed to get it to last a few months at a time without manual intervention, by writing a nightly scheduled task to run a compact&repair on it, but these people really need to migrate it to SQL server, and don't want to spend the money.


All I can do is advise them in writing that eventually their setup will break for good and that they must move before it happens. They will never do this but my backside is covered.

paddyrg

Ahh Lum, all sounds familiar, it is Bob and the corporate culture that lets him do this that is broken, not the tool he does it with. I know, I had to deal with that exact scenario over and over in one place I worked and entirely agree it is horrible when it is dumped on IT, or business users refuse to hear when you tell them they have all their eggs in one basket.

It actually gets worse, Bob, being an enthusiastic amateur, didn't realise he has inner joins on everything, the same table pulled 6 times over as different aliases, and that access linked tables are extremely network intensive. He doesn't even realise that the skunkworks tools he's created and proliferated don't give the results he thinks they do. He makes business decisions based on flawed data, then bases an empire on flawed data and premises.

I can't blame Access for this, I even used access 2(!) to replace a load of skunk apps the business had written in dbase 4 which wouldn't make it through Y2K compliance testing. One of those replacement apps got me hauled before a director for 'being wrong', fingers were pointed, sackings were threatened, you get the idea. I was terrified, and they were right, in some instances the old and replacement systems produced different results. Bit of digging, the only thing that kept me my job was proving their skunk app had been randomly underbilling customers for years and years. Yet it was me they were angry at for the figures not matching.

Back in the olden days of the internet as well, Toyota's website ran off an access back end - it was stable upto 85 concurrent (admittedly short, stateless, web) connections. But their guy had designed it well and it wasn't serving a whole site, just some elements. I totally agree that 30-40 people hanging a rich client stateful app off a single mdb is madness and cruising to fail. One thing maybe worth trying is providing them with a compact and repair shortcut next to the app shortcut on their computers. Poor solution to a worse problem.

So, I do have to concede that access used inappropriately is bad news, much like excel used inappropriately is bad news (and believe me, I've seen excel every bit as abused as access, in fact moreso!). In itself, however, used appropriately it is actually quite respectable for those single user projects that don't require a SQL Server license. And lock down that real db so Bob can't get to the data as linked tables, he has no need to, he should be asking for and getting reports, and IT should be responding.

Lum

But the big difference between Access and Excel is that when a similar project happens in Excel, it is unlikely to become a multi-user affair, and it is more likely that the stupidity will happen in Excel formulas, rather than VBA or macros.

It's much easier to find someone who knows Excel formulas, and the scope of damage is reduced because it can't be multi-user.

Sure allowing people free reign to do bizarre stuff with Excel is giving them a little bit of rope, but Access gives you enough rope to hang yourself and your entire team.


Locking down that DB doesn't always help either. Once back in my youth I was basically a female version of Bob. I was doing a modern apprenticeship and was stuck in Purchasing for three months. I was on the millennium compliance project where basically we had to ensure that all our suppliers were millennium compliant by spamming them with letters and maintaining a spreadsheet of who was and wasn't compliant, and producing weekly reports on a Friday, a job that took two of us half a day to do.

The supplier database was untouchable, it was on a 70s era unix system that was only accessible by telnet, but undeterred, I wrote an Access DB that fired up our terminal emulator software and sent it keystrokes via DDE and then screen-scraped the details, populating my own DB with the supplier's details, it would then open up the excel spreadsheet and populate that with data as needed, then pull the results of the letter spamming campaign back into access where I could generate the report.

At the time I didn't know what DoEvents() was for, and we were still using Windows 3.11 but I basically took a two person job and automated it to the point of loading this DB, clicking a button and then my machine would lock solid and unusable for three hours while me and the other girl went for an extended pub lunch.

When our three month stint was up and the next apprentices came along, I showed them how to use my DB and that was the end of it. I thought I was doing them a favour, and not of the wider consequences.

A year later and I've actually got a job at this company. We've just migrated to Windows NT4 (and god there were some comical disasters in that deployment) and now the Purchasing department can't run their reports.

I'm 3rd line desktop support, so guess who has to go over and figure out what her old crappy database did?

JulyKnot

paddyrg wrote: »

Where I see you making your own life difficult is by using A-D for data then repeating different data in E-H, and different again in I-L... You're confusing storing data with formatting. Just make the list longer in A-D, and the data stays pure, then you can do all the formatting on a separate sheet with (for instance) pivot tables.

BTW, not sure what bad experience Lum has had with Access, but it is actually a very decent little database package if you know what you're doing. It isn't meant for big systems but had some great simple tools for small stuff. I can't say it ever lost any of my data, most often it is poorly designed systems atop access that cause issues!

The four columns is for a reason. The other three relate back to the first.I am being dictated to by the app and the csv template to import into it. In other words each piece of data uses up four columns.
Ie headings for the four columns set by the app template:Item name, quantity, category, remarks.
So in the 'database' excel spreadsheet
First piece of data would take up columns A to D
Second piece E to H etc.

I have to dismiss using Access. This is just a little problem I've created for myself to see if it can be done with excel and if so would be useful to have and creating a worksheet for the data storage
As an afterthought though I wonder if there is any true need and I could maybe use just four columns B to to E and use column A as the differentiator/ header between groups of data. Will think about this properly when less tired........ No I don't think that will work

Thanks again everyone for giving me starters to go away and play around with.

Mr_Toad

Lum wrote: »

In a single-user setup, if you don't do a compact and repair from time to time it will eventually break, though most of the time a compact and repair will fix it.

But my post was really about using Access in the workplace, where it becomes a whole new level of evil. Here is the lifecycle of an access database in the workplace:

1. Meet Bob (who I have named after a certain Microsoft product that I love equally as much as this guy). Bob works in the finance department of BigCorp and his job is to produce the weekly reports using figures from the big SQL-based finance database. This job is certainly one that could be automated by someone who knows what they're doing, but that isn't what's about to happen, it's about to be automated by Bob.
2. Bob, knows just enough to be dangerous, so he fires up Microsoft Access, creates a new database and configures it to pull data from the main database, he uses macros and a bit of VBA to pull figures from the main database and store them in his tables and manipulate them, he then uses the Access reporting tools to spit out the weekly reports in a fraction of the time.
3. This goes down well and Bob takes on additional responsibility, he extends his database to do more things, eventually Bob ends up with a few staff working for him, all of whom are using his database.
4. This is where the rot sets in as while Access can, in theory, handle multiple users. In practice what happens is that when you access it over a network you massively increase the chance of corrupting your database, and each new user you add doubles it again.
5. A year passes, bob has now, effectively, been a finance manager for a year, so he jumps ship to get a better paying job with another company. He hands in his month's notice and is asked to train his replacement. None of these people have any clue about how to develop in MS Access so all they get is shown how to use the manager functions he's added in his database.
6. Now we have a bunch of people using this access DB, they have no knowledge of how their job was done without it. It is now a business critical tool.
7. And then it breaks in some way and a ticket is sent up to IT, at which point some poor sod from desktops is sent down to have a look. Until now IT had no idea that this database existed, it isn't documented anywhere, Bob's code is unintelligible, doesn't follow any coding standards, doesn't have any comments to explain what the hell certain things are doing. To Bob the things he was doing were obvious, perhaps to another person with an accountancy of finance background, who can also code, they may be, but to the person from IT who walked into this mess it may as well be written in hieroglyphs.

If you're lucky, a compact&repair is all that is needed to fix this, and then IT can set about developing a supported replacement. If you're unlucky it broke because something changed, some financial regulation, or some stupid bug that stops it working after a certain point, or in certain specific circumstances.


When this happens, you're basically screwed.




I have customers who have 30-40 people accessing an Access DB that was never designed to be used at that scale. It literally breaks every couple of weeks. I've managed to get it to last a few months at a time without manual intervention, by writing a nightly scheduled task to run a compact&repair on it, but these people really need to migrate it to SQL server, and don't want to spend the money.


All I can do is advise them in writing that eventually their setup will break for good and that they must move before it happens. They will never do this but my backside is covered.

What you need is a good Oracle developer.