New Sample: Form_DragDropResizeArrange

By AD Tejpal

    This sample db demonstrates various methods for dragging / re-sizing / arranging of controls by user action on access form at run time. In each style, before closing the form, user has the option to save latest status of each control regarding its position and size. On next opening of form, controls will get depicted as per such saved status.

    For a control to become eligible for drag and/or resize action, its tag property should include the words Drag and/or ReSize. At any stage, the user can undo the last action by clicking the UnDo command button. As and when desired, all such controls can be made to revert back to their original design settings by clicking the command button at bottom left. This command button also serves to toggle between design settings and last saved user settings.

    Following alternative styles are demonstrated:

    1 - Style A - It covers the following features:

        1.1 - Drag / re-size by direct use of control's mouse events via class C_ControlsDDR. This class has an interesting feature: WithEvents functionality for different types of controls has been incorporated into a single class.

        1.2 - Grouped controls manipulation. As the user drags the mouse (with left button in pressed state) around a set of controls, a red rectangle gets drawn accordingly and all controls falling within this rectangle are treated as a group. This group of controls can then be subjected to any of the following actions as desired:
            (a) Drag the group of controls.
            (b) Align the grouped controls Left / Right / Top / Bottom.
            (c) ReSize the group as per Widest / Narrowest / Tallest / Shortest control.
            (d) Make horizontal or vertical spacing amongst grouped controls equal.

        1.3 - All controls enclosed by red rectangle drawn by the user (para 1.2 above) become subject to grouped action, even if not carrying the words Drag or ReSize in their tag values. For controls required to be kept immune to grouped action, the tag value should carry the word Exclude.

    2 - Style B:

        2.1 - It demonstrates induced manipulation of controls via drag/re-size handles - without depending upon mouse events of these controls. Three types of controls are illustrated, i.e. web browser, subform and image. Out of these, web browser and subform control do not have any mouse event of their own.

        2.2 - As the user clicks upon the target control, drag handle appears at top left while resize handle appears at bottom right. Dragging on top left handle (with left button pressed) moves the control while similar action on resize handle leads to resizing.

    3 - Style C:

        3.1 - It demonstrates a completely non-intrusive approach towards induced manipulation of controls without depending upon any of their event. While three types of controls are illustrated, i.e. web browser, subform and image, this approach is universally applicable to all types of controls (including lines which do not have any event at all).

        3.2 - As the user clicks anywhere on blank space of detail section near top left of target control, it is accompanied by  automatic detection of that control. In confirmation, a red marker appears at top left of the control. Dragging the mouse (with left button pressed) on form detail section leads to induced movement of control identified by the marker.

        3.3 - Similarly, when the user clicks anywhere on blank space of detail section near bottom right of target control, it is accompanied by  automatic detection of that control. In confirmation, a red marker appears at bottom right of the control. Dragging the mouse (with left button pressed) on form detail section leads to induced resizing of control identified by the marker.

    4 - Style D:

        4.1 - It demonstrates a completely non-intrusive approach towards induced manipulation of lines (lines do not have any event at all).

        4.2 - As the user clicks anywhere on blank space of detail section near top left of target line, it is accompanied by  automatic detection of that line. In confirmation, a red marker appears at top left of the line. Dragging the mouse (with left button pressed) on form detail section leads to induced movement of line identified by the marker.

        4.3 - Similarly, when the user clicks anywhere on blank space of detail section near bottom right of target line, it is accompanied by  automatic detection of that line. In confirmation, a red marker appears at bottom right of the line. Dragging the mouse (with left button pressed) on form detail section leads to induced resizing/rotation of line identified by the marker.

        4.4 - Rotating action on a line can be carried out in either direction (clock-wise or anti clock-wise). There is no limit to the angle of rotation. So long as left mouse button is kept pressed, the user can continue rotating the line in complete circles in either direction.

    Note: It is observed that combo box and list box do not behave in a completely satisfactory manner while being dragged or re-sized through direct use of mouse events of these controls. There is no such problem with induced drag/re-size approach, as demonstrated in styles B to D. Interestingly, styles C & D  are completely non-intrusive and do not depend upon any event of target control.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/Form-dragdropresizearrange_topic596.html

Really Bad Design Decisions: A Case Study

This article has moved: Really Bad Design Decisions: A Case Study

COUNT DISTINCT in Access: Part 5

Comparison of the Methods

SQL Server has a nice built-in function called COUNT DISTINCT, which is missing in Access SQL.

Over the last four posts, I've discussed different ways to simulate it in Access :

1. Subqueries the FROM Clause
2. Subqueries in the Field List
3. User-Defined Function
4. Crosstab Query (reader submitted method)

Each of these methods have advantages and disadvantages, and as promised, I'll address them here.

Subquery in FROM Clause

The main advantage of creating a subquery in the FROM clause is ease of use, that is, it's the easiest to figure out. It's possible to approach it step-wise by first removing the duplicates from the list to be counted.  See Problem 1 in Subqueries the FROM Clause.

The main disadvantage is lack of flexibility.  As I showed in Problems 2 and 3 in Subqueries the FROM Clause, you can't easily created other levels of aggregation nor simply add a second aggregate to the same level.  The reason is you've pre-limited the values available, so you need to create extra levels of subqueries to compensate.

Over all, this method is useful for simple distinct counts, but not for more complex ones.

Subquery in Field List

The main advantage of create a subquery in the Field List is it's flexibility.  Once you've created the initial query, adding a second aggregate can be added just like in any other aggregate query, that is, just add another field to the field list with an aggregate function.

The main disadvantage is that it's a little harder to figure out in the first place.  The subquery must be a correlated subquery, which is conceptually more difficult.  A correlated subquery is evaluated for each row in the main query, so it must be tied to the main query, and that's a more difficult concept.

This method is useful for more complex queries.  It also more closely simulates the T-SQL Count Distinct, which also works at the Field List level.  This means that if you need to upsize this query to T-SQL, it's as simple as replacing the subquery with the COUNT DISTINCT.

User-Defined Function

One advantage of with user-defined function is that once created, you don't have to figure out how to do a subquery for each query.  You can simply call the function and send in the appropriate values.  It also appears to perform fairly well, but I'll address performance below.

The main disadvantage is that it's fairly easy to produce an incorrect result. The Where and Group By arguments must match the main Where and Group By clauses of the main query or the value will be be incorrect.  However, In an extremely complex query, this method may be useful to reduce the level of complexity in the main query.

Crosstab Query Method

If you're familiar with crosstab queries, this method is nice and clean.  And it performs very well (see below).

But as I've noted before, the main problem is that you can't add additional aggregates.  For instance, you can't show the sum of one field and the count distinct of another in the same query.  This makes it more limited than the other methods.

Performance

First of all, it's silly to talk about performance without discussing indexes.  On non-indexed fields, each of the methods will perform much worse.  In this case, I indexed all of the fields involved in the aggregation: OrderID (primary key), Customer, and Order_Date.

When each of the methods were run against the sample data listed, they all ran nearly instantaneously.  That table consists of 4 customers and 3 days, totaling 22 records.

To test the performance, I created a table consisting of 19 customers over the course of 30 days totaling 113,000 rows.  Then I wrote a subroutine that opens each query, recording the time when it opens and when the query completes.  The code looks like this:

Sub test()
Dim starttime As Date
Dim endtime As Date

'test From
starttime = Now
DoCmd.OpenQuery "TestFROMLarge"
endtime = Now
Debug.Print "TestFROMLarge: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "TestFROMLarge"

'test UDF
starttime = Now
DoCmd.OpenQuery "TestUDF_Large"
endtime = Now
Debug.Print "TestUDF_Large: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "TestUDF_Large"

'test Xtab
starttime = Now
DoCmd.OpenQuery "XTab_Prob2_Large"
endtime = Now
Debug.Print "XTab_Prob2_Large: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "XTab_Prob2_Large"

'test FieldList
starttime = Now
DoCmd.OpenQuery "TestFieldListLarge"
endtime = Now
Debug.Print "TestFieldListLarge: " & DateDiff("s", starttime, endtime)
DoCmd.Close acQuery, "TestFieldListLarge"

End Sub

Running this code against the 100K table produced this:

TestFROMLarge: 1
TestUDF_Large: 1
XTab_Prob2_Large: 1
TestFieldListLarge: 8

That gives me some information, but not enough.  So I created an even larger file.  Still using the same 19 customers, but with data spanning a whole year.  This new table had nearly 1 million records.

Running my test code against the 1M table produced this:

TestFROMLarge: 10
TestUDF_Large: 4
XTab_Prob2_Large: 6
TestFieldListLarge: 74

This file gives me the granularity to see differences.  The first three still execute within similar time frames.  The Field List method takes nearly 8 times longer.

Surprisingly, the User Defined Function performs the best of all of them.  My expectation would have been that it was the slowest.  Also surprisingly, the Field List method was the slowest.  I would have thought that a correlated subquery would execute faster.

If you'd like to test this for yourself, I've bundled this whole series with the database into a sample available my my website: http://www.rogersaccesslibrary.com/forum/countdistinctmdb-intermediate_topic595.html

Because of size considerations, it does not have the 1M table, so you'd have to create that yourself.

Count Distinct In Access: Part 4

Crosstab Method (Reader Submitted)

Note: In the comments section of Part 3: User-Defined Function, Patrick mentioned another method that uses a Crosstab Query.  Even though I didn't develop it, I'm including it in this series for completeness.  So, thank you, Patrick. 

SQL Server has a nice built-in function called COUNT DISTINCT, which is missing in Access SQL.

What does COUNT DISTINCT do?  Well, there are times when you want to count distinct values in a query, that is, a count of values without duplicates.  For instance, given the following table, how many distinct customers have orders?

ORDERS

OrderID	OrderDate	Customer	Amount
1	1/17/2008	Ajax Inc.	$310.00
2	1/17/2008	Ajax Inc.	$510.50
3	1/17/2008	Ajax Inc.	$311.00
4	1/17/2008	Baker Corp.	$5,144.00
5	1/17/2008	Baker Corp.	$61.00
6	1/17/2008	Baker Corp.	$110.50
7	1/17/2008	Baker Corp.	$11.00
8	1/17/2008	Crystal & Co.	$111.85
9	1/17/2008	Crystal & Co.	$511.00
10	1/18/2008	Baker Corp.	$711.95
11	1/18/2008	Baker Corp.	$810.00
12	1/18/2008	Baker Corp.	$310.59
13	1/18/2008	Crystal & Co.	$311.00
14	1/18/2008	Crystal & Co.	$811.50
15	1/18/2008	Ajax Inc.	$512.00
16	1/18/2008	D&D LLC	$211.00
17	1/18/2008	D&D LLC	$3,311.50
18	1/19/2008	Ajax Inc.	$410.00
19	1/19/2008	Ajax Inc.	$610.50
20	1/19/2008	Baker Corp.	$4,411.00
21	1/19/2008	Baker Corp.	$511.50
22	1/19/2008	Baker Corp.	$611.50

In SQL Server, I can do this:

SELECT COUNT(DISTINCT Customer) AS CountOfCustomer FROM Orders

Which will give me the following:

CountOfCustomer

4

In Access, if I use the Distinct predicate with the count:

SELECT DISTINCT Count(Customer) AS CountOfCustomer FROM Orders;

I get:

CountOfCustomer

22

Since Access SQL does not have the Count Distinct function, what can I do?
There are actually four different methods for simulating the Count Distinct:

1. Subqueries the FROM Clause
2. Subqueries in the Field List
3. User-Defined Function
4. Crosstab Query (this post)

Each of these methods have advantages and disadvantages, and I'll address each in turn. 

 Crosstab Query Method

A crosstab query presents aggregated data in an easy-to-understand grid. 
To create a simple Count Distinct as above, use the following SQL:

TRANSFORM Count(*) AS Cell
SELECT Count(cell) AS CountOfCustomer
FROM Orders
GROUP BY "Anything"
PIVOT Customer In (null);
To get the following result:

CountOfCustomer	<>
4

More Complex Queries

The Crosstab can also be modified to produce a customer count grouped by the OrderDate:

TRANSFORM Count(*) AS Cell
SELECT OrderDate, Count(cell) AS CountOfCustomer
FROM Orders
GROUP BY OrderDate
PIVOT Customer In (null);

OrderDate	CountOfCustomer	<>
1/17/2008	3
1/18/2008	4
1/19/2008	2

Problem: Additional Aggregation

However, in the other methods I've discussed, I was able to add additional aggregates to the query like this:

OrderDate	CountOfCustomer	CountOfOrderID	SumOfAmount
1/17/2008	3	9	$7,080.85
1/18/2008	4	8	$6,989.54
1/19/2008	2	5	$6,554.50

I can't figure out how to do that with the Crosstab query, so I'd say this method, although fast, is somewhat limited in terms of flexibility. 

Patrick insists this method has MUCH better performance than the UDF method.  Next time, I'll look at each of the methods and discuss their pros and cons, so we'll see.