Jim Lawrence
accessd at shaw.ca
Thu Apr 15 02:54:54 CDT 2010
Congratulations John.
It is not often that a fine piece of code is recognized especially by the
client who, in many cases, just understands and wants the result, period.
You have been working towards this result for months and it is done.
Again, good work John.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, April 14, 2010 6:55 PM
To: Access Developers discussion and problem solving
Subject: Re: [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?
>>
>>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com