Solved

Very very very very very LOOOOOOOOOOOONG query in excel

Posted on 2010-08-27
60
501 Views
Last Modified: 2012-08-14
I have this workbook that generates some statistics based on queries from a database. The way it's set up now works perfectly, but I had to do a little workaround, which I'd rather avoid, if I could. So, this is the deal:

I used a QueryTable to get the queries from the server. There's 3 problematic ones, each in its own sheet. The problem with them is that the query is very big. The longest (basically, 8 queries joined with UNION) is about 8500 characters long. When I right click and chose Table-> Edit query (or however the translation is in English), it opens MS query, but as if it's a new query. That is, it opens the query builder, and when I close it and get to the editor, the SQL is empty. However, if I choose refresh in the QueryTable, it refreshes correctly.
I also tried to change the query in a macro (as basically the whole file works on a macro anyway) but there was some error which I can't recall at the moment.

So, every month I need to change some parameters in the query (date intervals). Those parameters are set in a cell in another sheet. I can't set parameters on the QueryTable because 1) it can't be graphically represented and 2) it won't even show on MS query. What I did was create a cell (or, in the case of the really long one, 2 cells) that generates the SQL query (like ="Select field from table where this=" & A1), then I edit the query, copy this and it works. However, I would prefer if I could just open the book, change the date and have it work right away.

1) Is there ANY way to set parameters dynamically in these kinds of queries in excel (i.e., with joins, unions, etc)?
2) Any ideas on how to do this by macro?
3) Is it even possible to use a query this big automatically? Or can't excel handle these many characters in some object's property?
0
Comment
Question by:Cluskitt
  • 22
  • 11
  • 8
  • +4
60 Comments
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540074
What platform is the data stored in ? is it SQL
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33540087
Yes, it's MS SQL 2005.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540097
I would suggest you create a SQL View for what you need
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540099
Your query can me modified in SQL instead and your query used in excel will be against the view
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33540157
I'm not sure how I'd go about implementing something like that. After all, the date keeps changing, and it's not just a matter of not specifying the date in where, because it's also part of the joins tables.
for example:
select * from table t1 inner join (select idfield, max(datefield) as md from table where datefield<'somedate' group by idfield) t2 on t1.idfield=t2.idfield and datefield=md where <some conditions happen, including date checks).

The real example is more complicated, but you get my drift. Not only are there datechecks in where clause, but it's also inside the inner join table. This is because I only want changes between the range, and not any change that comes later.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540175
No, you would setup the basic view with all the unions, then your excel would query the view for the data using the date ranges
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540182
sorry , missread.. but you could still use a view, you exclude the date range in your joins so ALL data is returned, then you just query the view with the date range.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33540203
I don't think that's possible for what I want. And here's why:

What one of the queries does (one of the smallest) is:
Given a date range, for example, 20100716 to 20100815, it will check all employees of one of the companies. That company has several "branches", though I guess you can treat them as if they were departments. However, it's quite often that there are tranfers between them. So I have to check exactly where they were at 20100815. If they were transfered at 20100816, it doesn't count for the statistic.
Likewise for their job. If they're promoted after the valid date, it doesn't count for the statistic.
So, if I set a view without date intervals, it will simply return the last department and job function. That would not be an accurate statistic.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33540277
I should point out that there one table for employees, one for "departments", one for job functions, one for contracts. Of these, the only one that doesn't have history is employees. For the rest, they may have multiple entries. For date X, only the most recent record that has date <= date X counts.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540600
You can still do that sort of view. if the query has date ranges (hard coded?) and you periodically have to change those dates then , instead of editing the query in excel, just edit and modify the view?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33540649
Well, than that wouldn't help me that much, would it? I mean, I already have to edit the queries (there are 5 of them). The way it's set up now, each month there's a new date range, but all I have to do is open MS query, copy the query from the excel cell and paste the SQL with the new data range. Editing the view in SQL would require more effort.
Besides, what I want is a way to automate the process. Right now I open the file, change the date in the first sheet cell, open each table in MS query, copy the new SQL from the cell adjacent to it, paste it, then return to excel. Once all tables are refreshed with the new SQL, I run the macro.
What I want to do is: open the file, change the date in the first sheet cell (or even enter it in a MsgBox) and run the macro. I know it may sound like I'm lazy, but the less time I lose with this, the less time overall I lose, because this is done for each of about 30 companies.
:)
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33540900
I am sure it can be done with VBA macros. but its very difficult to visualize your data.
You can use a VBA macro to automate those steps in Excel.
you just need some code to prompt for the new date(s) and then build the queries in code.
Using code like
ActiveWorkbook.ActiveSheet.Cells(i, 1).Value
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33541347
I think I already tried that when I was creating the file, and there was some sort of error. I think it's possible that excel doesn't allow that many characters to be inserted into one of its object's properties. I don't even remember how I did it now (something about sourcedata?), but I do recall that it failed.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33542454
Are you not able to shorten the query? , maybe create shorter named tables (using a view )
 
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33542558
The only way to shorten the query would be to split it into several ones. And even then, not really. Each table is identified by two IDs (company ID and employee ID) as well as date (except the employee table, which is unique). To get the info I want I would always have to do the inner joins. The only option would be to split into something like:
One table returns only department, one returns only function, one contract info. Then I merge them all together.

Doing this for 5 queries would be ineffective enough, but seeing as one of the queries is in fact 8 queries like this one all connected would increment it even more. Just so you can see how it is, I'm attaching a part of the large one. This one just goes to the database and, given the date interval, gets all records that have PRO 002 (this is a promotion).
Promocoes.sql
0
 
LVL 16

Expert Comment

by:13598
ID: 33546515
Regarding 1) I don't understand what you mean by you can't use a value in a cell because it can't be graphically represented and it won't show up in the query but you could have a cell  on your sheet linked to the cell on the sheet that has the date value and use it as a parm in the query. If by can't be graphically represented you mean the formatting doesn't match the formatting in the db then you would set the cell to format the value correctly.

Use data from a cell as a parameter value
1.On your worksheet, type the values you want to use as criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) in the query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.).
2.Click a cell anywhere in the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) created with a parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.).
3.On the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), click Query Parameters .
4.In the list on the left side of the Parameters dialog box, click the parameter you want to change.
5.Click Get the value from the following cell.
6.On the worksheet, click the cell that contains the value you want to use.
If you want to refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the data whenever you change the value in the cell, select the Refresh automatically when cell value changes check box.
7.Click OK.
8.To refresh the data, click Refresh Data  on the External Data toolbar.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33546548
You can use parameters in Excel, so you put a date in a cell and it becomes a value in the query. You can only use it once, but you could do something like the snippet below.

You then use placeholders in the query.

The placeholder is a question mark.

MS Query will then ask you to join up parameters to the placeholders.

You can get the query to auto-refresh when the value in the spreadsheet changes, but for a long query, you'd probably not do that.



As for the query itself. Load it into Query Analyser and run the Indexing Optimization wizard.

I often do this for long running queries and get a significant increase.
   A     B

1 Date 2010/08/27

2 Copy1 =B1

3 Copy2 =B1

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33547771
Lose MS Query.  Instead create a Stored Procedure, execute it from VBA in the Excel sheet by passing the parmeters in and return the resultset to the spreadsheet.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33547773
On second thoughts, if you find VIEWs intimidating, perhaps you had best continue editing your spreadsheet every month.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33548707
Good advice from acperkins there. Using a sp with params (msquery or vb) will both work.

Optimization could also help.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33549144
Why isn't there a table/whatever with a history of employment?

Surely that would make things a lot easier, not just for this particular problem but others.

It don't think it would be too difficult to set up.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33549500
Ok, let's try to answer all:

1- 13598: Normally, I could insert a [condition] in the parameters of MS Query, then specify what cell I want to have the value. However, this only works for simple queries. Once you add join, unions and stuff, MS Query can no longer represent the query graphically (though it will still calculate it) and won't accept parameters.

2- There seems to be a slight misunderstanding here. The query doesn't take a long time to run. It's actually quite fast. It's very long in terms of characters used (over 8k).

3- I think I tried the ? for the parameters, but somehow it failed. I'll try again, though and let you know (on monday only, now).

4- acperkins, I don't find views intimidating. I just don't see a way for me to use them when the parameters I want also affect the way the view works (i.e., the data in the inner join, not just in where).

5- I'll have a look to see if I can get the stored procedure going.

6- imnorie, there is a history of employment. It keeps track of hiring, renewal, firing, etc and also contract type, duration, etc. But the only thing I require from it are info from one field and to know that he's not fired at date X. The fact that this table exists doesn't really make anything else easy, cause I still need to consult the department history, job function history, etc.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33549520
Do you mean a single value is over 8000 characters?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33549530
No, I mean the SQL itself is over 8000 characters. SELECT this from ..... etc, etc for 8500 characters.
So many characters, in fact, that when getting a cell to calculate the correct query for the current month (="select this " & a1 & "...."), I had to use two cells, cause I had reached the maximum number of characters a formula can hold.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33549566
Aha. Maybe we could take a look at optimizing that...

Turning it into a stored procedure should be pretty simple.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33549590
I'll only be able to work on it monday. I'll let you know how it goes then :)
0
 
LVL 33

Expert Comment

by:Norie
ID: 33549605
Are you sure that SQL can't be slimmed down a bit?

I've only had a quick look and I don't really know what sort of database structure you are using but there seems
to be a lot of repetition.

Have you considered creating a UNION query and then querying that with the criteria to get your results?

You seem to be trying to do everything in one big query.

How did you create the SQL in the first place?

You seemed to have mentioned that shortening the query, splitting it up whatever wouldn't work because it would be inefficient.

How?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33549639
> You seem to be trying to do everything in one big query.

Yes, that is what I'm doing, actually. It's just 3 queries joined by union. I've connected them all cause the fields are almost the same and it makes my VBA a lot more easy and fast when making the statistical calculations. I could break them, but that would slow my VBA.
But even if I could break them, I would still want to have a parameter. I know that when I wanted to use them on queries with more than one parameter (using the ?) I wasn't successfull, but I can try it again.

BTW, opinions of MS Query seem to be pretty low (even my own). I mostly use it for convenience as I'm already used to the QueryTable object. However, if there's a better option (especially one that still allow editing the data on the spot) then I'm all ears (or eyes, seeing as this is text only ;)
0
 
LVL 33

Expert Comment

by:Norie
ID: 33549777
How would breaking the queries up slow your VBA code?

Are you creating these queries, or the SQL for them using VBA?

If so how are you then using them to get the required results?

If you are using SQL Server, or any other database for that matter, why not try using ADO?

Also why a UNION query and why UNION ALL?

If the execution of the query is slow then that might be behind it.

Instead of UNION why not try creating a table from the queries?

What data do you need to edit on the spot?

The result of the query, the parameters, the criteria?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33549821
>>If you are using SQL Server, or any other database for that matter, why not try using ADO?<<
That is what I was alluding to when I mentioned the author should dump MS Query and instead execute it from VBA.
Also why a UNION query and why UNION ALL?

>>I suspect that would be because they are stored in different tables (for better or worse) that are not related and the data needs to be "aggregated".  UNION ALL is more efficient then UNION and where possible is a better option.

>>If the execution of the query is slow then that might be behind it.<<
Not that is not what the author is stating.  You need to re-read point 2 here http:#a33549500.

Here is a formatted version of the same query.  It is not complicated and there is no reason to refactor.  What the author is trying to address is how to execute it.
SELECT t1.tb2_num_empregado * 1,

        t1.tb2_fnc_cod_funcao,

        t2.tb2_fnc_cod_funcao,

        r1.trb_cct_cod_centro_custo,

        r1.trb_cct_cod_centro_custo,

        t1.tb2_arz_cod_accao

 FROM   rh_trbt0 AS r1

        INNER JOIN (SELECT  trb_emp_empresa,

                            trb_num_empregado,

                            MAX(trb_dt_efectiva) AS [md]

                    FROM    rh_trbt0

                    WHERE   trb_dt_efectiva < '20100716'

                    GROUP BY trb_emp_empresa,

                            trb_num_empregado

                   ) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa

                              AND r1.trb_num_empregado = r2.trb_num_empregado

                              AND r1.trb_dt_efectiva = r2.md,

        rh_tb2t0 AS t1

        INNER JOIN (SELECT  tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao,

                            MAX(tb2_dt_efectiva) AS [md]

                    FROM    rh_tb2t0

                    WHERE   tb2_dt_efectiva < '20100716'

                    GROUP BY tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao

                   ) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa

                              AND t1.tb2_num_empregado = t2.tb2_num_empregado

                              AND t1.tb2_dt_efectiva > t2.md

 WHERE  tb2_dt_efectiva > '20100615'

        AND tb2_arz_cod_accao = 'PRO'

        AND tb2_arz_cod_razao = '002'

        AND t1.tb2_emp_empresa = '32'

        AND r1.trb_emp_empresa = '32'

        AND r1.trb_num_empregado = t1.tb2_num_empregado

        AND NOT EXISTS ( SELECT t3.tb2_num_empregado

                         FROM   rh_tb2t0 AS t3

                         WHERE  t3.tb2_emp_empresa = '32'

                                AND t3.tb2_num_empregado = t1.tb2_num_empregado

                                AND t3.tb2_dt_efectiva > t2.md

                                AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )

        AND r1.trb_dep_departamento = '9000'



UNION ALL



SELECT t1.tb2_num_empregado * 1,

        t1.tb2_fnc_cod_funcao,

        t2.tb2_fnc_cod_funcao,

        r1.trb_dep_departamento,

        r1.trb_dep_departamento,

        t1.tb2_arz_cod_accao

 FROM   rh_trbt0 AS r1

        INNER JOIN (SELECT  trb_emp_empresa,

                            trb_num_empregado,

                            MAX(trb_dt_efectiva) AS [md]

                    FROM    rh_trbt0

                    WHERE   trb_dt_efectiva < '20100716'

                    GROUP BY trb_emp_empresa,

                            trb_num_empregado

                   ) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa

                              AND r1.trb_num_empregado = r2.trb_num_empregado

                              AND r1.trb_dt_efectiva = r2.md,

        rh_tb2t0 AS t1

        INNER JOIN (SELECT  tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao,

                            MAX(tb2_dt_efectiva) AS [md]

                    FROM    rh_tb2t0

                    WHERE   tb2_dt_efectiva < '20100716'

                    GROUP BY tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao

                   ) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa

                              AND t1.tb2_num_empregado = t2.tb2_num_empregado

                              AND t1.tb2_dt_efectiva > t2.md

 WHERE  tb2_dt_efectiva > '20100615'

        AND tb2_arz_cod_accao = 'PRO'

        AND tb2_arz_cod_razao = '002'

        AND t1.tb2_emp_empresa = '32'

        AND r1.trb_emp_empresa = '32'

        AND r1.trb_num_empregado = t1.tb2_num_empregado

        AND NOT EXISTS ( SELECT t3.tb2_num_empregado

                         FROM   rh_tb2t0 AS t3

                         WHERE  t3.tb2_emp_empresa = '32'

                                AND t3.tb2_num_empregado = t1.tb2_num_empregado

                                AND t3.tb2_dt_efectiva > t2.md

                                AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )

        AND r1.trb_dep_departamento <> '9000'

ORDER BY 1

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 350 total points
ID: 33549863
Here is your Stored Procedure and it is trivial to call.  You can test it as follows:
EXEC usp_Suafuncao '20100716', '20100615', 'PRO', '002', '32', '9000'
CREATE PROCEDURE usp_Suafuncao

			@dt_efectiva1 datetime,			-- Change the data types appropriately

			@dt_efectiva2 datetime,

			@arz_cod_accao varchar(3),

			@arz_cod_razao varchar(3),

			@emp_empresa varchar(2),

			@dep_departamento varchar(4)

AS



SET NOCOUNT ON

			

SELECT t1.tb2_num_empregado * 1,

        t1.tb2_fnc_cod_funcao,

        t2.tb2_fnc_cod_funcao,

        r1.trb_cct_cod_centro_custo,

        r1.trb_cct_cod_centro_custo,

        t1.tb2_arz_cod_accao

 FROM   rh_trbt0 AS r1

        INNER JOIN (SELECT  trb_emp_empresa,

                            trb_num_empregado,

                            MAX(trb_dt_efectiva) AS [md]

                    FROM    rh_trbt0

                    WHERE   trb_dt_efectiva < @dt_efectiva1				-- '20100716'

                    GROUP BY trb_emp_empresa,

                            trb_num_empregado

                   ) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa

                              AND r1.trb_num_empregado = r2.trb_num_empregado

                              AND r1.trb_dt_efectiva = r2.md,

        rh_tb2t0 AS t1

        INNER JOIN (SELECT  tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao,

                            MAX(tb2_dt_efectiva) AS [md]

                    FROM    rh_tb2t0

                    WHERE   tb2_dt_efectiva < @dt_efectiva1				-- '20100716'

                    GROUP BY tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao

                   ) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa

                              AND t1.tb2_num_empregado = t2.tb2_num_empregado

                              AND t1.tb2_dt_efectiva > t2.md

 WHERE  tb2_dt_efectiva > @dt_efectiva2									-- '20100615'

        AND tb2_arz_cod_accao = @arz_cod_accao							-- 'PRO'

        AND tb2_arz_cod_razao = @arz_cod_razao							-- '002'

        AND t1.tb2_emp_empresa = @emp_empresa							-- '32'

        AND r1.trb_emp_empresa = @emp_empresa							-- '32'

        AND r1.trb_num_empregado = t1.tb2_num_empregado

        AND NOT EXISTS ( SELECT t3.tb2_num_empregado

                         FROM   rh_tb2t0 AS t3

                         WHERE  t3.tb2_emp_empresa = @emp_empresa		-- '32'

                                AND t3.tb2_num_empregado = t1.tb2_num_empregado

                                AND t3.tb2_dt_efectiva > t2.md

                                AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )

        AND r1.trb_dep_departamento = @dep_departamento					-- '9000'



UNION ALL



SELECT t1.tb2_num_empregado * 1,

        t1.tb2_fnc_cod_funcao,

        t2.tb2_fnc_cod_funcao,

        r1.trb_dep_departamento,

        r1.trb_dep_departamento,

        t1.tb2_arz_cod_accao

 FROM   rh_trbt0 AS r1

        INNER JOIN (SELECT  trb_emp_empresa,

                            trb_num_empregado,

                            MAX(trb_dt_efectiva) AS [md]

                    FROM    rh_trbt0

                    WHERE   trb_dt_efectiva < @dt_efectiva1				-- '20100716'

                    GROUP BY trb_emp_empresa,

                            trb_num_empregado

                   ) AS r2 ON r1.trb_emp_empresa = r2.trb_emp_empresa

                              AND r1.trb_num_empregado = r2.trb_num_empregado

                              AND r1.trb_dt_efectiva = r2.md,

        rh_tb2t0 AS t1

        INNER JOIN (SELECT  tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao,

                            MAX(tb2_dt_efectiva) AS [md]

                    FROM    rh_tb2t0

                    WHERE   tb2_dt_efectiva < @dt_efectiva1				-- '20100716'

                    GROUP BY tb2_emp_empresa,

                            tb2_num_empregado,

                            tb2_fnc_cod_funcao

                   ) AS t2 ON t1.tb2_emp_empresa = t2.tb2_emp_empresa

                              AND t1.tb2_num_empregado = t2.tb2_num_empregado

                              AND t1.tb2_dt_efectiva > t2.md

 WHERE  tb2_dt_efectiva > @dt_efectiva2									-- '20100615'

        AND tb2_arz_cod_accao = @arz_cod_accao							-- 'PRO'

        AND tb2_arz_cod_razao = @arz_cod_razao							-- '002'

        AND t1.tb2_emp_empresa = tb2_emp_empresa						-- '32'

        AND r1.trb_emp_empresa = tb2_emp_empresa						-- '32'

        AND r1.trb_num_empregado = t1.tb2_num_empregado

        AND NOT EXISTS ( SELECT t3.tb2_num_empregado

                         FROM   rh_tb2t0 AS t3

                         WHERE  t3.tb2_emp_empresa = tb2_emp_empresa	-- '32'

                                AND t3.tb2_num_empregado = t1.tb2_num_empregado

                                AND t3.tb2_dt_efectiva > t2.md

                                AND t3.tb2_dt_efectiva < t1.tb2_dt_efectiva )

        AND r1.trb_dep_departamento <> @dep_departamento				-- '9000'

ORDER BY 1

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 33549943
I realise the OP wants to aggregrate the data and the probable reason they need/want to

A UNION query is one way of doing that, but there could be others depending on circumstances.

As for the query being slow/not slow perhaps I missed that part.

I might have been getting a bit mixed up with the OP also seeming to mention that there VBA code would be affected.

The use of VBA is probably the best way to go, I assumed the OP was already using it somehow.

Another poster also suggested it's use but in a different context than using stored procedures.

I was thinking of using VBA to create the required SQL through concatenation, which could include any parameters/criteria
that need to be taken from a worksheet or elsewhere.

I really like the idea of using a stored procedure but I'm pretty new with them, not try using them with ADO yet.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33550696
imnorie, the reason it would affect my code to break the query is that I set the queries in a way so that all I have to do is read top to bottom, adding as I go. If I break them, I would have to change the code so that it searches for the info.
That is, right now the code will give me, for example, all hire info, all fire info, all promotion info and all transfer info. This is ordered by employee. All I do is, top to bottom, read it and add it (with just a simple check to see if it's still the same employee). If I break it, one for hired, another for fired, etc, I would have to search for the employee, seeing as a hired employee isn't likely to have a promotion and vice versa, and I would have to check which employee would be next (assuming they're each ordered by employee), and keep track at which line each table is.
Seeing as my VBA code, right now, takes about 3 minutes for all calculations, and, even with the way it's now, the refreshing of tables takes 1 minute (refreshing, itself, only takes about 10 seconds for all tables, about 2s per table), I'd rather change my SQL than change my VBA.

Now, even though I won't be able to test it until monday, I'm pretty sure that acperkin's solution is what I want. I've had that feeling since he suggested it, and with his example now, I'm pretty sure. It will allow me to get the same result without changing VBA code.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33550726
Sorry you've lost me now.

What is the VBA code doing exactly?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33550758
The VBA code is generating statistics based on queries returned. Remember that I have 5 queries on the excel file that I use for it. It has varied statistical data, of which this is just a part, even though, in terms of query, it's the largest. This one just returns employment history.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 33554790
I suggest you implement acperkins stored procedure. After you've done that, you might want to consider moving your VBA into the database as well.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33556506
That wouldn't be a good idea. My intent here is to make the excel file totally automated, so that anyone can access the file through a share and open it (in case I'm on holidays, for example). I don't want them accessing the database :)
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 33556966
They wouldn't need any more access to the database than they do already.

The idea here is get the SQL into the server where it should be.

Using VBA to supply the parameters to the stored procedure and have the server do all the work.

This makes your Excel spreadsheet significantly easier to work with.

You could even drop Excel and use something like Crystal Reports or another reporting tool. The tool just reports the data. The server does all the work.

It also means that if you accidentally end up with several copies of the spreadsheet, you don't have different versions of the query/vba code floating around your network. Maybe not too important for a straight reporting tool, but can lead to all sorts of sysadmin fun when the "I didn't do anything" tweaks come along.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33557079
> After you've done that, you might want to consider moving your VBA into the database as well.

I was actually refering to this.

Anyway, I'm the only one that works with this file, except on the rare occasions when I'm on holiday. The client(s) want an excel file, so I send them an excel file. In the end, it's not that difficult, and once I get the stored procedure working, it should become even easier. I'll probably write the code so that it calculates for all companies and saves each to its own file.

It seems the solution to my problem was simply a stored procedure, but I haven't worked that much with databases as developer, mostly just as IT admin, so I haven't used one before. Seems simple though, and will let you know as soon as it's working (or not, though I doubt that).
0
 
LVL 33

Expert Comment

by:Norie
ID: 33557361
Am I missing something here?

I'm quite new to SQL Server, which I believe is what the OP is working with, but
is it actually possible to use VBA actually within it?

I know VBA could be used in Excel to work with the data.

I'm also a little bit curious as to why it appears that the queries need to be performed
in a particular order to get the required statistic results.

Can't the stored procedures etc be used to get the data from the database into Excel?

Once that's done code can be written in Excel to get the finished product.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33557405
As I said, it seems that stored procedures is the answer I want.

I don't know how to use VBA in SQL either, but, in this specific case, I'm not sure if I want to follow that route.

The queries don't really NEED to be performed in that particular order. It just speeds up my VBA. If I change the order, I would have to change the VBA and instead of reading sequentially without a need to validate the data, I would have to actually add checks to see which data from which table I would need to read next. Or create a new way to load it. All in all, this is just a simpler, faster way to execute it, because I know exactly what will return and how and which order.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33557469
Well whatever works I suppose, that's the important thing.

I was just thinking that you could get  the data into Excel, then disconnect from the server and let it
continue on it's merry way while you do the rest in Excel VBA.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33567791
Ok, I've tested this with one of the queries. In SQL server everything is as it should be. EXEC usp_ContagemMesDep '20100816','32' (this is a different query from the one above that returns the currently employed information) returns the same as the previous query I had used. Now my problem lies in calling this in Excel.

I used the same QueryTable object, and replaced the SQL with the call for the usp. But I'm having some trouble with the parameter. When I replace the SQL with EXEC usp_ContagemMesDep '20100816','32', it works, so I know it's not the connection. But when I try: EXEC usp_ContagemMesDep ?,'32'
and then point the parameter to my cell, I get an error. First it was: Invalid date (the cell just had 20100816). Then I created a new cell with '20100816'. Now it says "Incorrect parameter. Excel needs a different value than the one introduced" (or similar, I'm translating).

Also, I'm not sure how I would go about adding more than 1 parameter. If I use
EXEC usp_ContagemMesDep ?,?
it will only detect one parameter (used twice). Maybe I'll have to use a different object other than QueryTable?
0
 
LVL 16

Expert Comment

by:13598
ID: 33567950
You could try something like this (make your parameters name type and size fit yours):
Dim Cmd1 As New ADODB.Command
 
Cmd1.ActiveConnection = Yourconnectionobject
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "Call  usp_ContagemMesDep (?,?)"
'Add parameters
Cmd1.Parameters.Append & _
       Cmd1.CreateParameter("LIB", adChar, adParamInput, 10,
       Library)
Cmd1.Parameters.Append & _
       Cmd1.CreateParameter("FIL", adChar, adParamInput, 10,
       Table)
Cmd1.Execute
 
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 150 total points
ID: 33568020
I think you need to convert your Excel date to something that SQL Server understands as a date.

As far as I can see '20100816' is a string/text so if the stored procedure expects a date that could be the cause of the error.

I think there are a couple of functions, CAST and CONVERT, that might help there.

By the way have you considered building the SQL for the query like this?

Don't know if it'll make much difference than the parameter method, but might be worth a shot.
strSQL = "EXEC usp_ContageMeDep CONVERT(datetime, " & Range("A1").Value &", 126) , '" & Range("B1").Value & "'"

Open in new window

0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33568140
Actually, SQL server converts '20100816' to date just fine. I did say that the SQL was fine. Also, if I don't use a parameter, but add '20100816' to the SQL, then it also works fine.
Also, using VBA it would be relatively easy to use the parameters I want. The problem is that I'm using the TableQuery object, and all I do is right click, table->External Data Properties, then change the SQL in the connection properties. If possible I'd like it to remain this way, instead of changing all my tables to VBA.

In future files, I will probably use more efficient objects, but for now I don't want to change this file too much.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33568316
Sorry I'm getting increasingly confused here, probably my fault.

I thought you were and did want to use VBA?

Also what do you mean by 'convert' the tables to VBA?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33568407
What I mean is:
I have the tables created in excel, with Data->Get External Data->From Other Sources->From MS Query.
It created the connection (conn string and SQL string) and the QueryTable object.
Then I have the VBA code. All it does is read the table rows. There is no interaction between VBA and the tables. Nor is it really necessary. The table SQL allows for parameters. I just can't get it to read '20100816' whether the cell has '20100816' (incorrect parameter) or 20100816 (ODBC error, invalid datetime).
Now, if I have to replace all the tables that were manually added to excel, I guess I can. I'd just rather find a way to change the already existing objects to accept parameters, as they're supposed to.
0
 
LVL 16

Expert Comment

by:13598
ID: 33568501
What is the value '20100816'  supposed to be. You say that when you actually key in that value it works, right? So did you format the cell that contains this value to be a string type 8 characters long? and then pass that cell value as the first parameter?
0
 
LVL 16

Expert Comment

by:13598
ID: 33568516
Hold on I just read your last post. It appears to be expecting a datetime field so did you format your cell that contains the parameter value to be datetime with the same specs as your db field?
0
 
LVL 16

Expert Comment

by:13598
ID: 33568576
Please take a look at the Use data from a cell as a parameter value section in this article.
http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP010216113.aspx
 
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33568692
I don't send the parameter as date. I send it as a 'yyyymmdd' string. MS SQL server then converts the string to date. It works fine like that. The problem isn't the date, because, if instead of using this SQL
EXEC usp_ContagemMesDep ?,'32'
I use
EXEC usp_ContagemMesDep '20100816','32'
it works just fine.
0
 
LVL 16

Expert Comment

by:13598
ID: 33568712
How does it know what value to retrieve for ?
0
 
LVL 33

Expert Comment

by:Norie
ID: 33568770
I've just created a simple stored procedure and used a query table with parameters in Excel.

One of the parameters was a date from a cell.

It didn't like 20100816 or '20100816 but accepted 16/08/2010, which obviously Excel treats as a date.

It also accepted 16 August 2010.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33568795
What do you mean?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 33568846
imnorie: I've tested with a date type value, and yes, it works fine. So I guess that was the problem. However, I really don't understand it. If I use '20100816' in SQL server it works, if I use it in the SQL of the query table it works, if I use it as a parameter, it doesn't work. I don't really understand why.
Anyway, I guess the initial question is answered and I'll close this. Thanks to all for your help.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33568907
I couldn't tell you why either.

It's probably just one of those things - different versions of things just use slightly different syntax.

I tried some other variations in Excel and SQL Server, with parameter queries and non parameter, even tried using CONVERT in the stored procedure
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 33572124
To clarify, this statement:
> After you've done that, you might want to consider moving your VBA into the database as well.
What I meant was, convert your row-by-row VBA code into more efficient SQL code.
You can use windowing functions in SQL that don't require you to order your resultset in any particular way. They do the processing all in one set rather than stepping through records and identifying group breaks etc.
Advantages:
-You can move all of your processing on to the server rather than the client
-You only have to maintain source code in one spot
-The business logic is removed from the presentation logic
-You can implement security on the server
-Processing is more efficient as it is set based rather than cursor based
-Processing is more efficient as it can make use of database indexes
If you're interested, perhaps start a new question on it.
PS you can't put VBA in a database but you can for example compile a .Net assembly and call that natively from a database, although there is cettainly no call for it in this case.
 
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 6 18
Convert .PDF 6 38
Index/Match with Multiple Criteria 2 14
TT Auto DashBoard 4 12
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now