What programs/Software should I use?

[SASHA_D]

I am hoping someone can give me guidance on a fairly major project I am working on.

I am responsible for producing lots of figures, charts etc. each month and the current process takes a very long time!
I have been charged with improving this process, which currently includes the following:-

Slightly changing some CSV files each month
Importing the CSV files to an Access database containing lots of other data and information
Running some queries off an SQL database and importing the results to the access database
Running a few queries in Access
Exporting the query results to Excel
Manual manipulation of the Excle files
Producing graphs and tables in excel

I am fairly experienced in Access and know a little Access VBA
I know a bit of SQL to run queries from SQL query analyzer
I have dabbled with VBA in excel
But I wouldn't really call myself a programmer

I need to automate the process as much as possible, but I am not sure which way to go. I would need a database to combine the data from CSV files and other information as current, but also need to build up a historical store of data.
I would then want some kind of front end to select the months and year to look at, plus different categories etc. then produce a table and chart to excel or easily exported to excel for distribution to other people.

Would a combination of Access and Excel using VBA be sufficient for this?
A colleague has mentioned using SQL, in particular MySQL, which is downloadable for free.
They have also said that you can develop a front end in visual basic and there are programs freely available where you can build a visual basic front end by drag and drop methods.
They also mentioned that you could build a front end using Java.

I have also read bits on this site about ASP and PHP and how they can be used as a front end with Access as a back end database.

In short, I am getting confused about which combination of tools would be best suited for this job!
Any assistant/opinions/thoughts would be greatly appreciated!
The output tables/charts etc. would have to be distrubuted to a large range of people in the company.
The database or front end would just be viewed by a couple of people.


Many thanks!
Sasha

 

[The_Doc_Man]

I'll offer some thoughts, but the choice is ALWAYS up to you.

The database or front end would just be viewed by a couple of people.

This means that simple security - or security by "gentleman's agreement" - might be adequate. Access Workgroup security might not be required. However, if these "couple of people" could be simultaneous users, you should consider at least minimal security to prevent exclusive-open problems.

On the other hand, it ALSO means "low probability of record locking issues" because Access works pretty well with small groups of users.


{Paraphrase} Import data from multiple sources, massage it, spit it out to Excel, muck it about even more, spit out some pretty-pretties for the bosses to ignore later. {/Paraphrase}


I sense that this is at least a big contributor to your confusion, if not the primary source thereof. This is a "forest and trees" issue. Step back a bit.

WHY must you muck about in the data? (I don't want to know - I'm illustrating a thought process...) Is this mucking about something that could be done by an automated process or does it require eyeball-and-brain type judgement? Does it involve mechanical filtration of data? Does it involve minor editing of some text fields? Does it involve numeric operations? Can you define ON PAPER the steps you need to perform, to such degree of precision that you could write a BASIC code sequence to do the mucking on a per-record or per-field basis?

Slightly changing some CSV files each month.
Importing the CSV files to an Access database containing lots of other data and information

I suspect you have put the cart before the horse here. If you can import the CSV files to a table, you can do a whole lot of automated mucking about in VBA code, if only you know what you want to do. (See above comments.)

but also need to build up a historical store of data.

The sizing of this historical data store governs whether you CAN use Access or whether you will need to step up to a product with a larger capacity. I don't know the capacity of MySql but I do know that Access can tell you how much you can store. Use the Help files to find "Specifications" - and then drill down to the topics to find the various specific limitations.

I would then want some kind of front end to select the months and year to look at, plus different categories etc. then produce a table and chart to excel or easily exported to excel for distribution to other people.

This requirement doesn't affect whether you use Access exclusively or merely use Access as a front-end to something else. Because Access has tools built-in to specifically allow tighter integration to other MS Office components, I would not abandon it completely.

I have also read bits on this site about ASP and PHP and how they can be used as a front end with Access as a back end database.

These are not good ways to integrate with Excel for charting. There are reasons to use these products, but the things you listed do not, at first glance, appear to me to be "right" for ASP or PHP application. But heck, I could be wrong. It has been known to happen. Maybe even more than once or twice!

The output tables/charts etc. would have to be distrubuted to a large range of people in the company.

I have found that exporting relevant subsets of data to Excel and charting same within the same .XLS file makes distribution easy - as, for example, an attachment to your e-mail system. Outlook? Groupwise? Doesn't matter. Whatever you use, it can probably handle arbitrary attachments. By having many SELECT queries defined in Access but available for export to Excel, you can pretty painlessly meet this requirement with respect to selectivity, data extraction in reasonable chunks, and formatting charts.

BTW, if your charting is frequently the same exact format, there are ways to store templates that can become the basis for future charting activities. Look up "templates" in Excel - or better still, look up "templates" on a page where you have a chart open. The dynamic Help linkage used in Office makes it pretty simple from a chart page to find chart help.

 

[SASHA_D]

Thank you Mr Doc Man ,

You have provided me with some food for thought!
I believe all the steps can probably be automated,
Your paraphrase sounds accurate!
the overview of the steps are that we are calculating montly year on year sales for a large number of shops and we only want to include a shop if it is open in the same month in both years (like-for-like), so if doing a report on performace for a year, it would I guess involve a combination of 12 monthly queries.
Other reasons for the manual mucking about are formatting the data, converting data types, adjusting some figures for extra ordinary events e.g. a car went through a shop window recently so it was closed for a few days or sometimes because of comms problems we don't receieve all the data.

speaking with a colleague we are probably going to go down the route of building a mySQL database that will include lots of data, including data needed for these reports.
I'm thinking Access could then be used as a front end to this database,
which would then spit out the subset of data requested and produce a nicely formatted table and chart-I will look into the templates you describe.
Also, could all this part be done in VBA in Access i.e. exporting the subset of data to excel, formatting it nicely and producing a chart?
Or would it need code in Access to export it, then code in excel to format and chart it?

thanks!

Sasha.

 

[SASHA_D]

Thank you Mr Doc Man ,

You have provided me with some food for thought!
I believe all the steps can probably be automated,
Your paraphrase sounds accurate!
the overview of the steps are that we are calculating montly year on year sales for a large number of shops and we only want to include a shop if it is open in the same month in both years (like-for-like), so if doing a report on performace for a year, it would I guess involve a combination of 12 monthly queries.
Other reasons for the manual 'mucking about' are formatting the data, converting data types, adjusting some figures for extra ordinary events e.g. a car went through a shop window recently so it was closed for a few days or sometimes because of comms problems we don't receive all the data.

speaking with a colleague we are probably going to go down the route of building a mySQL database that will include lots of data, including data needed for these reports.
I'm thinking Access could then be used as a front end to this database (not sure how easy/difficult it is to link Access to mySQL)
which would then spit out the subset of data requested and produce a nicely formatted table and chart-I will look into the templates you describe.
Also, could all this part be done in VBA in Access i.e. exporting the subset of data to excel, formatting it nicely and producing a chart?
Or would it need code in Access to export it, then code in excel to format and chart it?


thanks!

Sasha.

 

[The_Doc_Man]

formatting the data, converting data types, adjusting some figures for extra ordinary events

Of these three, only the "adjusting for extraordinary events" is in the category I earlier called "eyeball-and-brains" editing - where judgement is needed. The others can be done through a query. In fact, if they exist in a format that CAN be converted, you don't actually have to convert them in your DB. Just convert them in a SELECT query and export the query rather than the raw table. Focus a bit more on the process of that "adjustment" to automate it based on the least amount of required inputs since it is those manual inputs that will slow you down.

Or would it need code in Access to export it, then code in excel to format and chart it?

I have not played with using Access to do a graph using Excel as an application object. I use Access graphics templates instead.

However, you could search this form for relatively recent posts on MS Graph or Graphs in general to see that many folks don't like the Access interface to MS Graph. To be gently polite about it, many forum contributors believe that the MS Access/Graph interface SUCKS.

You can always export to Excel - and graphing with Excel at that point becomes a fairly simple four-panel "Chart Wizard" experience if Access has done the bulk of the "massaging" effort. Which is why setting up the query to do the reformatting is so important.

To me, the biggest graphing problem will be getting graph scales and legends and axis labels right. Basically, this is something you just have to play with until you get it like you like it. 'cause MS Graph is tricky at best when run from another program like Access or Excel.

I'm thinking Access could then be used as a front end to this database (not sure how easy/difficult it is to link Access to mySQL)

It happens all the time. Look up ODBC connections in Access help and in any manuals or other documentation you can find for MySQL to find out how this works. You will need one MySQL library module (usually a .DLL file) per workstation planning to use this interface to MySQL.