New Sample: AppendValuesToExcel.mdb

AppendValuesToExcel.mdb

by Roger Carlson

This sample demonstrates how to append data from a database to an Excel spreadsheet using Office Automation to find the next empty row in the spreadsheet.

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

.

New Sample: MovePriority.mdb

MovePriority.mdb

by Dave Mason

This sample allows the user to arbitrarily move records up or down in a form's Continuous Forms view.

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

.

New Sample: MultipleLabelsOffsetBatch2.mdb

MultipleLabelsOffsetBatch2.mdb

by Roger Carlson

This is the latest sample in a family of samples that illustrate how to print multiple labels. Each sample gets progressively more complex.

1. MultipleLabels.mdb ( intermediate )
   This form illustrates how to print multiples of specific labels.
2. MultipleLabelsOffset.mdb ( intermediate )
   This sample is a more complete version of MultipleLabels.mdb. It is meant to be a simple minded stand-alone address label program.
3. MultipleLabelsOffsetBatch.mdb ( intermediate )
   This sample is a similar to MultipleLabelsOffset.mdb, but prints multiple labels for multiple people in a batch.
4. MultipleLabelsOffsetBatch2.mdb (intermediate)
   This sample is a similar to MultipleLabelsOffsetBatch.mdb, but adds the ability to have different numbers of labels printed for each address.

[top]

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]