[AccessD] Any easy way to do this?

Today we got a system working where I create the base queries in SQL Server, then we load the base 
queries in a list box on a C# form.  From there we use a "mover" paradigm to move them back and 
forth between two other list controls, one which represents the column queries, the other represents 
the row queries.

Once we did that and got inner join code working between the row and column queries, we automated it 
so that code iterated the rows and columns dynamically creating and executing the junction queries 
(inner join between two base queries).

With that happening, we then created a datagrid view, and in code created a table adapter, populated 
it with the correct number of columns (in the col query list) and then populated the table with the 
counts.  Bound the datagridview control and started poking the counts into the resulting table.  It 
is quite a wonder to watch as the junction queries run and populate the table with the counts.

When it is done I can select the data in the table, copy it to the clip board and paste into a 
spreadsheet or email to send to the client.

Other than the time to run the actual counts, this is now a 15 minute exercise to do counts, even 
for quite a large grid of counts.  I got a grid request the other day that was 7 columns by 8 rows, 
56 junction queries, plus 15 base queries.  I still have to create the 15 base queries but now I can 
just select those base queries into the row and column lists and press a button and voila, counts 
start filling in a table.

Quite satisfying to watch.

John W. Colby
www.ColbyConsulting.com


jwcolby wrote:
> A.D.
> 
> I will look at your function to perhaps replace my custom programming the base queries.  As I 
> mentioned I "solved" the problem using C# which to be honest is my preferred solution anyway. 
> Yesterday my assistant and I got a form running which grabbed the queries beginning with vBase into 
> a list box, allowed me to move them to two other lists (column and row) and then create dynamic SQL to
> 
> Select Count() PKID from vBaseColXXX inner join vBaseRowYYY on PKID
> 
> kind of thing, which returns the count of the junction (inner join) on any two selected vBase 
> queries.  Today we will be expanding that to allow me to select M Column queries, N row queries, and 
> populate a datagrid control with the counts of the junction of each column and each row vBase query.
> 
> What I have right now cuts my time from several hours for a 5 X 6 array of counts down to perhaps 20 
> minutes.  With the solution I expect to have at the end of the day I should be down to about 5 
> minutes or so.
> 
> Eventually I expect to be working with the field level of these tables, pulling field names into 
> combos with "possible values" into another combo, allowing me to create where clauses out in C# as well.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> A.D. Tejpal wrote:
>> J.C.,
>>
>>     The situation described by you appears to be suited for a single query with user defined function.
>>
>>     For sake of illustration, let the source table named T_A have the following fields:
>>     MOB - Text type ("Y" means Yes)
>>     HasCat - Text type ("Y" means Yes)
>>     HasDog - Text type ("Y" means Yes)
>>     BoatLength - Number type
>>     Propulsion - Text type ("Sail", "Motor" etc)
>>
>>     Create a table T_Ref having a single field named RefField (text type). Populate this single column with names of such fields from table T_A as are desired to be displayed as vertical base fields. This can be done programmatically if required.
>>
>>     Sample function  Fn_GetCount() as given below, may be placed in a general module.
>>
>>     Sample query Q_A as given below, should get you the desired results. For this illustration it will show three categories of count results (BoatLength > 20, Propulsion = 'Sail' and Propulsion = 'Motor'), against each of the vertical base fields (MOB, HasCat, HasDog etc)
>>
>> Q_A  (Sample query)
>> ===============================
>> SELECT T_Ref.RefField, Fn_GetCount("T_A",[RefField],"BoatLength > 20") AS BL_20Plus, Fn_GetCount("T_A",[RefField],"Propulsion = 'Sail'") AS Prop_Sail, Fn_GetCount("T_A",[RefField],"Propulsion = 'Motor'") AS Prop_Motor  
>> FROM T_Ref;
>> ===============================
>>
>>     For any additional criteria, or picking up the same from form controls, the sample query could be modified / expanded suitably as desired.
>>
>> Best wishes,
>> A.D. Tejpal
>> ------------
>>
>> ' Code in general module
>> '========================================
>> Function Fn_GetCount( _
>>                 SourceTableName As String, _
>>                 BaseFieldName As String, _
>>                 CriteriaString As String) As Long
>>     Dim Qst As String
>>     
>>     Qst = "SELECT Abs(Sum([" & _
>>             BaseFieldName & _
>>             "]= 'Y' And " & CriteriaString & _
>>             ")) FROM " & SourceTableName & ";"
>>     
>>     Fn_GetCount = _
>>         Nz(DBEngine(0)(0).OpenRecordset(Qst).Fields(0), 0)
>> End Function
>> '========================================
>>
>>   ----- Original Message ----- 
>>   From: jwcolby 
>>   To: Access Developers discussion and problem solving 
>>   Sent: Tuesday, April 13, 2010 17:58
>>   Subject: Re: [AccessD] Any easy way to do this?
>>
>>
>