Excel + SQL schtuff
Discussion
I'm currently trying to automate some data analysis in Excel. It's currently done by pasting the data, and manually applying filters and then some copying and pasting followed by some bodging.....I prefer a more elegant solution.
The current wip is done by macros altering the filters on a data set, which is linked to a chart. It's very clumsy but it does the job. However, I want more advanced functions. I've been able to get most of them by queries in an access database, e.g. averages of the different types of product. This is not possible in excel without some major use of advanced filtering and some manual hard work. As such, it looks like I have to use an Excel front end with an access backend, which is not what I want, especially as the file gets passed around somewhat. Importing the data from the external database is fine, it's manipulation afterwards. In access this is easy, just create a query. I can then import that into Excel.
But I can't run queries on a table in excel! I can from other workbooks, but not from the same one. I tried setting up an ODBC source to itself, but predictably it crashed...so what can I do? Pivot tables are no good to me btw, they just don't seem of any use. I can't apply proper filtering of data to them (e.g. begins with t3) So after much waffle, is it possible to run a SQL query on an internal table? And is it possible to run sql for a chart, to save manipulating on data? Or any other of the wall ideas? Otherwise I'll stick with a bastardised access-excel interface...
Ta
The current wip is done by macros altering the filters on a data set, which is linked to a chart. It's very clumsy but it does the job. However, I want more advanced functions. I've been able to get most of them by queries in an access database, e.g. averages of the different types of product. This is not possible in excel without some major use of advanced filtering and some manual hard work. As such, it looks like I have to use an Excel front end with an access backend, which is not what I want, especially as the file gets passed around somewhat. Importing the data from the external database is fine, it's manipulation afterwards. In access this is easy, just create a query. I can then import that into Excel.
But I can't run queries on a table in excel! I can from other workbooks, but not from the same one. I tried setting up an ODBC source to itself, but predictably it crashed...so what can I do? Pivot tables are no good to me btw, they just don't seem of any use. I can't apply proper filtering of data to them (e.g. begins with t3) So after much waffle, is it possible to run a SQL query on an internal table? And is it possible to run sql for a chart, to save manipulating on data? Or any other of the wall ideas? Otherwise I'll stick with a bastardised access-excel interface...
Ta
I'd certainly try to avoid an Acess/Excel hybrid, especially if anyone else is ever going to try to use it on their PC.
Do you always have access to the "external database" you mentioned, or is it an absolute requirement that you take a snapshot of its data and then generate reports from that snapshot?
I imagine ideally you'd chose from one of the following:-
(1) External DB backend, Access front end.
(2) External DB backend, Excel front end.
(3) External DB queried for snapshot, fattish Access application to slice and dice.
(4) External DB queried for snapshot, fattish Excel application to slice and dice.
Personally I'd usually go for (2) or (4). Ideally I'd go for (2) and I'd use the backend to do as much of the aggregation and filtering as possible ... afterall, that's what databases are for ... but connectivity and DB load may make that impractical.
Excel's filters can do stuff like "begins with" and "contains", though it can be a bit cumbersome to try to automate them. On the whole I avoid trying to slice and dice data in worksheet formulae and worksheet manipulations like filters. I'd prefer to have raw data dumped on one set of worksheets, and then have some VBA which slices and dices and then writes its output to a different set of sheets. Dynamically manipulating the data in Excel is nice in theory, but it is often hard to set up a spreadsheet so that it works reliably over time. People are far less likely to muck up VBA code than they are to accidently edit formulae on a worksheet, or accidently set a filter.
Do you always have access to the "external database" you mentioned, or is it an absolute requirement that you take a snapshot of its data and then generate reports from that snapshot?
I imagine ideally you'd chose from one of the following:-
(1) External DB backend, Access front end.
(2) External DB backend, Excel front end.
(3) External DB queried for snapshot, fattish Access application to slice and dice.
(4) External DB queried for snapshot, fattish Excel application to slice and dice.
Personally I'd usually go for (2) or (4). Ideally I'd go for (2) and I'd use the backend to do as much of the aggregation and filtering as possible ... afterall, that's what databases are for ... but connectivity and DB load may make that impractical.
Excel's filters can do stuff like "begins with" and "contains", though it can be a bit cumbersome to try to automate them. On the whole I avoid trying to slice and dice data in worksheet formulae and worksheet manipulations like filters. I'd prefer to have raw data dumped on one set of worksheets, and then have some VBA which slices and dices and then writes its output to a different set of sheets. Dynamically manipulating the data in Excel is nice in theory, but it is often hard to set up a spreadsheet so that it works reliably over time. People are far less likely to muck up VBA code than they are to accidently edit formulae on a worksheet, or accidently set a filter.
I work with Excel/Access everyday and there is absolutely nothing wrong with it!
Excel is very useful as an interface which is where it comes into its own. Access is very good at holding data.
Create an ADO recordset from the data with the exact format of the data you wish to tabulate
ie SELECT tID, tName, FORMAT(tDate,"dd/mmm/yyyy"
FROM tblTest
set range= transpose(recordset.getrows)
If you need some examples of the connection strings etc then drop me a mail
Excel is very useful as an interface which is where it comes into its own. Access is very good at holding data.
Create an ADO recordset from the data with the exact format of the data you wish to tabulate
ie SELECT tID, tName, FORMAT(tDate,"dd/mmm/yyyy"
FROM tblTest set range= transpose(recordset.getrows)
If you need some examples of the connection strings etc then drop me a mail
Bit of an update - Access is out of the question - it would seem I'm the only person in the company who knows how to use it 
So Excel it is. I'll try explaining again.
I connect using ODBC to the external database, then drag down all data I need associated with my primary key. This takes about 10 minutes for a months worth of data, as there is some scripting behind the scenes in the database that utilises a few external dlls to get some other bits - in short it's complicated. I then need to take this data and sort it out into a further few tables. I can get this data from the external database by querying again, but I havn't got all day (352 different tables, some called table1 for instance - design doesn't come into it).
So I want to drag all the data down, and then query it myself. Piece of cake if it was in Access, or another Excel file, but I have to contain all my information in the one file So - if I have some data defined by a name, how do I perform some sort of query onto it to extract some data and place it another sheet? It seems a stupidly simple thing to ask, but google is useless.
So Excel it is. I'll try explaining again. I connect using ODBC to the external database, then drag down all data I need associated with my primary key. This takes about 10 minutes for a months worth of data, as there is some scripting behind the scenes in the database that utilises a few external dlls to get some other bits - in short it's complicated. I then need to take this data and sort it out into a further few tables. I can get this data from the external database by querying again, but I havn't got all day (352 different tables, some called table1 for instance - design doesn't come into it).
So I want to drag all the data down, and then query it myself. Piece of cake if it was in Access, or another Excel file, but I have to contain all my information in the one file
So - if I have some data defined by a name, how do I perform some sort of query onto it to extract some data and place it another sheet? It seems a stupidly simple thing to ask, but google is useless.Can you give a concrete example of what you want to do? E.g. explain what type of data columns you have, what sort of filter criteria you want to apply, what kind of aggregation you might want to use.
p.s. is the result of your query on the external database a single big table, or are you returning mulitples result sets?
p.s. is the result of your query on the external database a single big table, or are you returning mulitples result sets?
www.excelforum.com/
I find this site really useful for queries I have at work. (Use the search function within the forums.)
I'm afraid all of this goes straight over my head.
I find this site really useful for queries I have at work. (Use the search function within the forums.)
I'm afraid all of this goes straight over my head.
Edited by Platinum on Monday 4th September 22:56
I know you said that no one else understands Access, but I take it that the users will be using Excel??
Why not Create tables within Access, and copy the data into there? That way you can use ADO within Excel and query the Access DB....Especially as speed seems an issue with the main DB.
Why not Create tables within Access, and copy the data into there? That way you can use ADO within Excel and query the Access DB....Especially as speed seems an issue with the main DB.
I have thought about this using Access, and tbh it seems like I'll have to. All I need now is know how to get the Access database to connect to the main one and refresh itself remotely using VB. Lastly (promise), is it possible to stick said database on a samba drive and it still work? IT are useless and I'm not allowed the internet because I'll spend on the time on football sites..
Have you tried Orbis TaskCentre from www.orbis-software.com I think it will be able to do the majority of what you are after by using ODBC and an automated routine to extract the relevant info, which can then be sent out via e-mail.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff