[AccessD] Any easy way to do this?

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?


  A.D.,

  AFAICT there is no simple way to do this.

  The client has a table with 540 fields.  Things like HasCat, BoatLength, MailOrderBuyer (MOB) and so 
  forth.  These fields USUALLY contain a Y (HasCat) but may contain a code (BoatLength).

  The client wants to know "How many people have a cat and a boat > 20').  Things like that.

  He might be marketing to boat owners, so he asks for counts

  (BoatLen >30)
  HasCat 10987
  HasDog 15456
  MOB 28790

  So this is a count of the "junction" of two queries.  Always use "Boat length > 30" and then Join on 
  HasCat, HasDog and MOB.

  Four Base queries.  Three counts.

  (BoatLen >30) (Propulsion = Sail)
  HasCat 10987 3487
  HasDog 15456 4568
  MOB 28790 9769

  Here the client has asked for more counts, count people with a boatlength > 30 and another to count 
  people with a sail boat.

  FIVE queries, six counts.

  In all cases, this is just a count of PK with a join on the PKID between two different queries.

  Yesterday I actually "solved" the problem by building a form in C# which allows me to select a set 
  of views in a selected database where the name contains vBase - vBaseHasCat, vBaseHasDog, vBaseMOB, 
  vBaseBoatLen30, vBasePropulsionSail.  So I have to manually create the base queries, whatever those 
  might be for a specific count order.

  Then my form allows me to select any two base queries in two combos, dynamically builds a SQL 
  statement that counts the PK where the two are joined on PKID, and places the count in a text box on 
  the form.

  I am building an application for the client which allows me to do many different things.  I already 
  had code to allow me to select a database, and tables or views from that database into combo boxes. 
    I just had to add code to execute a SELECT SQL statement and get a value back.  Once I did that, I 
  built the form and tested.  By the end of last night I had it working for getting a hard coded 
  count.  Today I need to write code to dynamically create the SELECT Count() SQL using the names of 
  the two queries selected and pass that dynamic SQL into the code I wrote yesterday and I will have a 
  working system.

  Using this I can select the database, select two vBaseXXX queries, press a button and get a count. 
  I copy that count back into the spreadsheet the client gave me and move on to the next count.  Thus 
  I will select vBaseBoatLen in the top combo, vBaseHasCat in the bottom combo, and press the button. 
     Keep vBaseBoatLen in the top combo, select vBaseHasDog in the bottom, press the button. Select 
  vBaseMOB in the bottom, press the button.

  And so forth.

  I used to manually create a count view in sql server for each junction and manually execute those 
  junction count queries directly in a query window in SQL server.  If I have 6 columns and 9 rows 
  (and I was asked for such a thing yesterday) that is 56 different junction queries, created and 
  executed manually.  You can see why I want an easier way to do this.

  Taken to the next level, I could "easily" modify my form to select the column queries into a list 
  control, the row queries into a list control, press the button and get the result counts deposited 
  into a grid control.  THAT would be awesome, and an exact implementation of the spreadsheet as 
  specified by the client.

  This would turn a several hour job (designing 15 vBase queries and 56 junction queries and then 
  manually executing the 56 junction queries) into a 20 minute job (designing 15 vBase queries and 
  then just selecting them into two lists and pressing a button).

  I have a couple of college students coming to my office to help me write code.  I will be giving 
  this to them to take to that next level.

  John W. Colby
  www.ColbyConsulting.com