[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


A.D. Tejpal wrote:
> JC,
> 
>     Could you please provide some sample data along with corresponding sample output that is being sought ?
> 
> Best wishes,
> A.D. Tejpal
> ------------
> 
>   ----- Original Message ----- 
>   From: jwcolby 
>   To: Access Developers discussion and problem solving ; Sqlserver-Dba 
>   Sent: Monday, April 12, 2010 21:58
>   Subject: [AccessD] Any easy way to do this?
> 
> 
>   My client is constantly asking for "table counts" by which I mean filling in a table that looks kind 
>   of like a crosstab (but isn't) but for 4 or 5 vertical fields for 4 or 5 horizontal fields.
> 
>   FieldK FieldL FieldX FieldZ
>   FieldA Cnt? Cnt? ? ?
>   FieldB etc etc
>   FieldC
>   FieldD
> 
>   This isn't even a groupby since we are not talking values inside of FieldA, but rather a total count 
>   WHERE Field In ('X','Y','Z') and Field K is not null (or something similar).
> 
>   This is just killing me in terms of time to complete this as the only way I am thinking of is to 
>   create 16 count queries.
> 
>   Is there a better way?
>   -- 
>   John W. Colby
>   www.ColbyConsulting.com