[AccessD] Any easy way to do this?

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?
> 
>