New Sample: Form_Treeview

Form_Treeview

By: A.D. Tejpal

This sample db demonstrates treeview control with two way synchronization visa-vis conventional form. Two styles of treeview control are covered as follows:

1 - Employees organization chart:

• 1.1 - Treeview at left shows the organizational set up in hierarchical style, department-wise, as per chain of command, while the adjacent subform at right displays the same information in conventional style.
• 1.2 - Name of employee in current row of subform is highlighted in light green. Name of top boss for this employee is highlighted in light maroon while all intermediate superiors in the chain of command reaching up to current employee are highlighted in light orange.
• 1.3 - In synchronization, corresponding nodes in treeview (current employee, top boss and intermediate superiors of current employee) get highlighted in colors matching those in 1.2 above.
• 1.4 - Treeview - Drag and Drop feature:
  Any employee node can be moved to another employee or department node. The resulting change in command structure gets reflected promptly in the treeview as well as the subform. If an employee node is dragged to empty space within treeview control (instead of specific destination node), a new department gets added, with the dragged employee designated as top boss in that department.
• 1.5 - Complete two way synchronization between treeview and the subform (Navigation / Editing):
  For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.

2 - Student grading:

• 2.1 - Treeview at left displays classes and students while the adjacent treeview shows subject-wise grades for current student.
• 2.2 - Subject-wise grades can be entered / edited conveniently just by clicking the pertinent check boxes.
• 2.3 - For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed.
• 2.4 - Complete two way synchronization between treeview and the subform (Navigation / Editing):
  For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.

Note: For editing the label caption for employee or student nodes in the above treeviews, select the node by clicking on its label. Thereafter, click again so as to make the contents editable.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=518

[top]

New Sample: Form_SearchAllFields

Form_SearchAllFields

by A.D. Tejpal

Explanatory Notes

This sample db demonstrates fast search across whole data source, covering all fields and all records. The search is conducted on all columns in "Across and then Down" style. Based upon space separated search words entered by the user, advanced search capability is provided as follows:

(a) Match field values having any of search words.

(b) Match field values having all of search words, but not necessarily in the same order as entered in search box.

(c) Match field values having all of search words, in the same order as entered in search box.

(d) Exact match of field values against full contents of search box.

As the user types into the search text box, equivalent criteria string gets displayed at bottom of form. Simultaneously, the total number of matches found across the data source gets displayed.

In addition, all matching fields get highlighted as follows:

(a) Light Maroon: If the record carries a match in any column and it also happens to be current record

(b) Light Green: All other records having a match in any column.

Various search options are selectable via an option group. Appropriate command buttons enable the user to find the first or next matching record as desired. A special subroutine ensures that the found record gets displayed at mid-position of the subform window.

In order to facilitate efficient use of FindFirst / FindNext methods of a recordset, different data columns are stacked into a single master column through a union query. This temporary query, generated in form's load event, is devised in such a manner that different fields of a given record appear as a set of contiguous single field rows. These sets of rows (a set representing one original record) follow the prevailing sort order of form's record source. This is achieved by using sequential numbers for records (as per their prevalent sort order). Within a given set of rows representing an original record, the sort order is based upon tab index of respective bound controls.

Note:
(a) While building the union query, care is taken that only visible bound controls (or unhidden columns in case of datasheets) are included in its output.

(b) For insertion of sequential numbers, conventional approach, using a subquery or recordset based function can prove un-acceptably slow for large data sets. In this sample db an exceptionally fast method using incrementing variable, with values stored in a collection, has been used. This is based upon the technique kindly suggested by Gustav Brock. Since values displayed by such a method tend to be volatile on navigation through normal query output, a temporary table has been used for holding the values.

(c) If the current record has more than one field matching the criteria, it will take that many extra clicks of FindNext command button to move over to next record having a match.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=517

[top]

New Sample: PreviousDateQuery.mdb

PreviousDateQuery.mdb
by Roger Carlson

This sample illustrates how to create a Previous Date query.

It shows how to:

1) Display the previous date in subsquent records of a table
or
2) Display the previous date in subsquent records of a table over a group.

There are two methods shown here: Using 1) a correlated subquery and 2) the DMax domain aggregate function. The correlated subquery is the faster method, but it returns a non-updateable recordset. The DMax method is slower, but the recordset is updateable.

HOWEVER, it should be noted that against a large dataset, neither method will be very fast!

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=516

[top]

What is the difference between HAVING and WHERE in Aggregate Queries?

Just today, I ran into one of the best explanations of the differences between the HAVING clause and the WHERE clause in aggregate queries (or Totals queries as they are called in Access).

It was in the Microsoft Public Newsgroups by fellow MVP, Vanderghast. The following is used with his permission:


The WHERE clause is evaluated before the AGGREGATION/GROUP formation, while the HAVING clause is evaluated after.

As example, you can eliminate records with a negative value before summing the values:

SELECT itemID, SUM(value)
FROM somewhere
WHERE value >= 0
GROUP BY itemID

Or, sum everything, and them, remove the groups where their sum is negative:

SELECT itemID, SUM(value)
FROM somwhere
GROUP BY itemID
HAVING SUM(value) >=0

You cannot have a WHERE clause on an aggregation:

WHERE SUM(something) > 0
since the aggregation does not exists, yet.

You cannot have an HAVING on a non-aggregated, non-grouped field, since that field is 'lost' after the aggregate:

SELECT a, SUM(b)
FROM table
GROUP BY a
HAVING c > 0

as example, maybe we need some data:

        a       b        c
        10      1       -3
        10      2        4
        11      4       -5

After the aggregation, we have

a       SUM(b)
10      3
11      4

what will be 'c' in the HAVING clause?

On the other hand:

SELECT itemID, SUM(b)
FROM table
WHERE c > 0
GROUP BY itemID

makes sense, and the result is now

a     SUM(b)
10    2

Vanderghast, Access MVP

[top]

New Sample: Report_FixedRowsPerPgOrGrp

Report_FixedRowsPerPgOrGrp

by

A.D. Tejpal

This sample db demonstrates printing of fixed number of rows per page or group in an access report. For each case, two alternative methods are shown:

(a) Solution based upon VBA code in report's module. This does not call for any interference to report's record source. (b) Solution based upon SQL of report's record source, minimizing the need for VBA code.

SQL based solution as per (b) above makes use of a driver table having a single field populated with sequential numbers from 1 onwards.

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=515

[top]

New Sample: Report_PrintLabelsCustomStart

Report_PrintLabelsCustomStart

by A.D. Tejpal

This sample db demonstrates printing of labels in such a manner that starting position of first label as well as different number of copies for each label can be specified by the user. Out of available source data, only the labels selected by user will actually get printed. The report used for this demo has three columns.

Two methods are covered as follows:

(a) Everything is managed via VBA code. No interference to report's record source is needed.

(b) The query serving as report's record source is devised in such a manner that it takes care of specified start position for first label to be printed as well as different number of copies for each label as desired. VBA code is minimal, just for hiding the label control when blank.

Method (b) above makes use of driver table T_Ref having a single field RefNum populated with numbers 1 to 200. If the number of copies for any label are likely to exceed this number, contents of table T_Ref can be expanded suitably.

Source data consisting of three fields holding top middle and bottom lines for the intended label, is displayed in a subform along with a column displaying the desired number of copies in each case. The last column has bound check boxes enabling the user to select which of the labels are to be printed.

Sample label pertaining to current record in the subform is displayed on the parent form. Any editing of contents in the subform gets reflected promptly in the sample label.

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=514

[top]

What Is A Join Part 5: Outer Joins

Outer Join (Left Join or Right Join)

So far in this series (What Is A Join: Part1, Part2, Part3, and Part4), I've concentrated on the Inner Join, which is the most common type of join. However, the Inner Join has a limitation. It will only match field values that exist in both tables. Going back to our Order and OrderDetails tables:

Orders
OrderID	OrderNumber
1	111
2	222
3	333
4	444

You can see that there is an OrderID "4" in the Order table.

OrderDetails
OrderDetailID	OrderID	Quantity
1	1	1
2	2	2
5	3	3
6	1	2
7	1	1
8	3	2
9	3	1

However, there are no records for OrderID "4" in the OrderDetails table.

Query1
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

Thus, OrderID "4" does not appear in the resulting Inner Join. But there times when you want to show all of the records of one table and the associated records in another, whether or not all of the records in the first table have a match.

This is called an Outer Join. In Access, there are two types of Outer Joins, Left Join and Right Join. The Left and Right refer to which side of the equal sign the table is on in the JOIN clause. I'll get back to that in a minute.

To create an Outer Join in the Query Builder, start with a standard Inner Join. Then right click on the Join line, and select Join Properties :

You'll get a pop-up box with three choices:
Selecting either Option 2 or 3, will create an Outer Join. If you select 2, you'll be creating a Left Join. If you select 3, you'll be creating a Right Join. The result will look like this:

And the resultset will look like this:

LeftJoin
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1
444

The SQL for this query looks as follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;

The "Left" and "Right" handedness doesn't really have anything to do with the table positions in the Query Builder. It has more to do with the positions of the tables with respect to the JOIN keyword.

An Inner Join will look like this:

FROM Order JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

Since it is joining only matching records, it doesn't matter what order the tables appear in the clause. But to create an Outer Join, you have to indicate which table will show all records. In Access, you do that with LEFT JOIN and RIGHT JOIN.

FROM Order LEFT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Left Join, the table on the left of the JOIN keyword (Order) will show all records.

FROM Order RIGHT JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

In a Right Join, the table on the right of the JOIN keyword (OrderDetails) will show all records.

Multiple Joins with Outer Joins

An outer join (left or right) can participate in a multiple table join only under circumstances. If the query also involves an equi-join and certain outer joins, it will result in the Ambiguous Outer Join error. For more information about this error, see my post: What is an Ambiguous Outer Join?

One last thing about Outer Joins: Earlier, I discussed creating an Equi-Join in the Where clause, but as far as I know, in Access you can't create an Outer Join that way.

Next time, I'll look at a specific application of the Outer Join: the Unmatched Query.