Featured Sample: Listbox with Multiselection used in Query

Listbox with Multiselection used in Query

by Duane Hookom

This sample demonstrates how a multiselect listbox can be used as the criteria in a query. A generic function is used to return a True or False depending on if a field value is selected in a listbox on a form.

There are two list boxes in the demo: one is bound to a numeric field and the other a text field.

While this solution works great for smaller tables, it may not be the best solution for tables with 1000s of records.

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

.

Top Query Revealed: Parameters

Top Query Revealed: Parameters

This is the fifth and last in a series on the Top Query. In previous posts, I've discussed Simple Top Queries, Aggregate and Grouping Top Queries, Problem of Ties in a Top Query and Finding Random Records.

Create a Parameter for Top Value?

One question which often comes up with regard to Top queries is if you can supply the TOP predicate as a parameter like you can with Where criteria. Unfortunately, the answer is you can't, at least not with native SQL. You can, however, use code to modify your Top query programmatically.

To do this, we need to create a subroutine called TopParameter in a General Module. Of course, I could hard code it for a specific query, but it would be far more useful to have code that will modify any Top query that I supply it. So my code will include a QueryName argument that passes in the name of the query to be modified.

Sub TopParameter(QueryName As String)
On Error GoTo Err_TopParameter

Next, I'll declare some object and scalar variables.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strTopVal As String
Dim strSQLTemp As String
Dim StartPos As Integer

Then I need to open the current database and set the database object variable (db) to it. The QueryDefs method of the database object lets me open the query definition of the Top query I want to modify and set it to a querydef variable (qdf).

Set db = CurrentDb
Set qdf = db.QueryDefs(QueryName)

The SQL property of the QueryDef object reads the SQL statement of the query into a string variable so I can modify it.

strSQLTemp = qdf.SQL

Since every Top query has the TOP predicate with a space before and after it, I can find the position of the first space after the existing Top value. I need to do this because I have to remove the existing Top value and replace it with the new value.

StartPos = InStr(strSQLTemp, " TOP ") + 5

It's a good idea to test whether the query is actually a Top query or not, which I can do like this:

If StartPos = 5 Then
MsgBox "Not a Top Query"
GoTo Exit_TopParameter
End If

Now I'll prompt the user for the new Top value with an inputbox.

strTopVal = InputBox("Enter TOP value:")

Next I'll read all the text after the old Top value into a variable. To do that, find the first space after the TOP predicate like this:

strSQLTemp = Mid(strSQLTemp, InStr(StartPos, strSQLTemp, " "))

Now I've got all the pieces needed to rebuild the SQL string with the inputted Top value.

strSQL = "SELECT TOP " & strTopVal & strSQLTemp

Lastly, I'll set the SQL property of the query to the new SQL string, which will save the query with the new Top value.

qdf.SQL = strSQL

To finish it off, I'll add the Exit_TopParameter label which I used earlier to exit the routine if it is not a Top query. In addition, I'll clean up the object variables and add error trapping.

Exit_TopParameter:
db.Close
qdf.Close
Set db = Nothing
Set qdf = Nothing
Exit Sub

Err_TopParameter:
MsgBox Err.Description
Resume Exit_TopParameter
End Sub

To call the Query, I need to first call this code, then open the query, like this:

Call TopParameter("MyTopQuery")
DoCmd.OpenQuery "MyTopQuery", acNormal, acEdit

And that's it. Every time the code is run, it prompts for a new Top value, modifies, and then opens the query.

A free sample illustrating this process (and, indeed, all the information in the Top Query Series) can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233.

.

Featured Sample: Charts_AccessAndExcel.mdb

Charts_AccessAndExcel.mdb

by A.D. Tejpal

This sample db demonstrates management of chart objects on forms and reports. Display of Excel chart on Access form based upon Access data, is also covered.

Examples covered pertain to the readings of Blood Pressure and Pulse for patients. Each individual spell of stay in the hospital is identified by unique InPatient_ID.

Important tips for handling the chart object are also included.

Version - Access 2K/XP/2K3 (Access 2000 File Format)
References:
(a) Microsoft Excel Object library (version 9.0 or later)
(b) Microsoft Scripting RunTime
(c) Microsoft Graph - (Appropriate Version)
(d) DAO 3.6

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

.

Top Query Revealed: Top Query to find Random Records

Top Query to find Random Records

This is the fourth in a series on the Top Query. In previous posts, I've discussed Simple Top Queries, Aggregate and Grouping Top Queries, and the Problem of Ties in a Top Query.

One rather surprising use for a Top query is to return a resultset of randomly selected records. It's also surprisingly simple. All you really need to do is sort on a random number, or rather, generate a random number in the Order By clause of the query.

So how do you get a random number? Fortunately, Access has a built in function called Rnd that will supply one.

To use Rnd, you need to supply it with a seed or an initial value to generate a pseudorandom number. Any numeric field will do for a seed value, but I generally use an autonumber, primary key field. In the case of the Orders table, that would be OrderID, but OrderNum would have worked as well.

SELECT TOP 10 Account, OrderNum, [Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY Rnd(OrderID);

Every time this query is run, it will return 10 different records, well, sort of. Used as is, the Rnd function will produce a different set of records each time. However, if you close the database and re-open it, it will run the same records in exactly the same sequence.

In order to get a truly random set of numbers each time, you have to add the Randomize command. To do that, you have to create your own function. In a global module, create the following function:

Public Function gRnd(FeedNum As Long) as Double
Randomize
gRnd = Rnd(FeedNum)
End Function

Then use your user-defined rounding function in your Order By clause:

SELECT TOP 10 Account, OrderNum, [Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY gRnd(OrderID);

A free sample illustrating this process can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233

Next time, I'll finish up the Top Query by looking at Creating a Parameter for Top Value.

Top Query Revealed: Top Query Problem - Ties

Top Query Problem - Ties

As I said last time in Top Query Revealed: Aggregate Values and Top Values By Group, one problem with Top queries becomes apparent when there are duplicates in the top values. When this happens, the query will return more values than asked for.

Simple Top Queries

For instance, the following query will produce four values, rather than three because there are two orders with a total price of $27.50.

SELECT TOP 3 Account, OrderNum, [Total Price]
FROM Invoices
WHERE Account=237001
ORDER BY [Total Price] DESC;

When asked for the top three values, Access can't decide which of the two duplicates is in the top three, so it includes both of them. Figure 4 shows the result.

Figure 4: Result of a query asking for the top three values when there is a tie. Four records are returned.

The solution is to add an additional field to the Order By clause.

SELECT TOP 3 Account, OrderNum, [Total Price]
FROM Invoices
WHERE Account=237001
ORDER BY [Total Price] DESC, OrderNum DESC;

In Figure 5, you'll see that by adding OrderNum to the Order By clause, it does in fact return three values. Since I used DESC in the clause, it returned the larger of the duplicated order numbers: 542724. If I had used ASC (or left it blank) it would have returned 542723 instead.

Figure 5: Top 3 query with one of the duplicates removed.

Top Query By Group

But as you can see in Figure 6, the problem of duplicates can happen in top queries by group as well.

Figure 6: Account 237001 has four records: two with $27.50.

Unfortunately, the solution is not the same. Simply adding a second field to the Order By clause of the subquery, doesn't work; not by itself anyway. However if we add to this what we learned about using a Totals query to return aggregate values, we can find a solution.

In our previous Totals query, we used the Sum aggregate function. This time, however, we'll use a different aggregate function. The Max aggregate function returns just the maximum value of a group rather than summing the group. Adding this to our main query, we get this.

SELECT Account, Max(OrderNum) AS MaxOfOrderNum, [Total Price]
FROM Invoices
GROUP BY Account, [Total Price]
HAVING [Total Price] In
(SELECT TOP 3 [Total Price]
FROM Invoices I2
WHERE Invoices.Account = I2.Account
ORDER BY I2.[Total Price] DESC, I2.OrderNum DESC)
ORDER BY Account, [Total Price] DESC;

So adding the aggregate function to find the maximum OrderNum in the main query and adding a sort on OrderNum in the subquery solves the problem and we get output like Figure 7.

Figure 7: Solution to the duplicates problem in a top query by group.

Notice that account 237001 now has only 3 records and the number of records returned is 378 rather than 403.

A free sample illustrating the "tie" problems and solutions can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233

Next time, I'll talk about how use a Top Query to find Random Records.

.

New Sample: Report_SortAsPerGrpCalc

Report_SortAsPerGrpCalc

by A.D. Tejpal

This sample db demonstrates two alternative styles for sorting between groups as per total sum for each group, as follows:

(1) Report R_SortByGrpSumDesc_A makes use of calculated expression in Sorting and Grouping (S&G) dialog box. The report is grouped as per publishers and these groups are sorted in descending order as per total group amount (Stock * UnitCost) for each publisher. The expression used directly in S&G dialog box is:

=CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher = '" & [Publisher] & "'"),0))

(2) Report R_SortByGrpSumDesc_B makes use of calculated field named GrpAmount in the source query. This field name is used directly in S&G dialog box. The expression in the source query (design grid) is:

GrpPrice: CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher = '" & [Publisher] & "'"),0))

Note:
(a) For (2) above, use of subquery (instead of DSum()) for arriving at the total value won't suit, attracting an error message while trying to run the report. This is because subqueries are not amenable to direct use as source fields for group levels in an access report.

(b) For consistent sorting results, output of Nz() function, when used in a query or S&G dialog box, is required to be converted to desired data type.

Imp:
(a) Name of a calculated control can not be used directly in Sorting and Grouping dialog box, which only accepts either a field name featuring in the record source or an expression.

(b) User defined function deriving its value by referring to the calculated control itself, is also not found effective in Sorting and Grouping dialog box. This could be attributable to the fact that values held by various controls are not yet exposed in report's open event, whereas field settings for sorting and grouping are required to be enforced at this stage itself.

(c) For a solution independent of source query, an expression based upon either a built in or a user defined function can be used in the Sorting and Grouping dialog box, as demonstrated in report style A in this sample db.

Version: Access 2000 file format.

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

.

New Sample: Form_VirtualRecordsMatrix

Form_VirtualRecordsMatrix

by AD Tejpal

This sample db demonstrates generation and display of a matrix of virtual new records. This arrangement has the advantage that while the user can see at a glance all the available data entry slots, no redundant record actually gets inserted in the source table until some data is actually entered in it.

For example, task planner for December would show 31 virtual records, one for each day of the month. If the user makes entries only for 5th and 20th December, only these two records will actually get inserted in the table, while records pertaining to all the 31 days continue to get displayed.

Three styles have been demonstrated in this sample db:
(a) Task planner as per each day of the given month.
(b) Task planner as per half hourly time slots on the given day.
(c) Bike sales: For each batch of bikes received, a matrix having number of virtual records matching the batch size (number of bikes received in the given batch) is displayed. As and when a bike gets sold, ticking the check box inserts the record in source table with automatic filling in of sale date as well as sale stamp fields.

Note:
Styles (a) and (b) above use a modified version of calendar form originally developed by Allen Browne. Following features have been incorporated while implementing the adaptation:
(a) Use of calendar form as a subform.
(b) Mild highlighting (persistent) of today's date - This is in addition to strong highlighting of selected date.
(c) Two way synchronization between calendar subform and task planner subform.

Version: Access 2000 file format.

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

.

Top Query Revealed: Aggregate Values and Top Values By Group

Top Query Revealed: Aggregate Values and Top Values By Group

Last time, in Top Query Revealed: Simple Top Query, I talked about the simple top query. A simple top query returns the top values based on individual field values. I showed how this query:

SELECT TOP 10 Account, OrderNum,
[Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY [Total Price] DESC;

Returned the following results:

Figure2: Results of simple Top query.

Top Query based on Aggregate Values

As useful the simple top query is, what if I wanted to find the top 5 Accounts by total sales rather than individual invoices? The solution is to use a Totals (or Aggregate) query.

SELECT TOP 5 Account,
Sum([Total Price]) AS [SumOfTotal Price]
FROM Invoices
GROUP BY Account
ORDER BY Sum([Total Price]) DESC;

Notice the addition of the Group By clause and the Sum function. The result of this query can be seen in Figure 3.

Figure 3: Result of top 5 accounts by total sales.

Another useful thing to do with a Top query is to return the top values for each of a group of data. For instance, suppose instead of the top 3 values for a particular Account, I wanted the top 3 values for all Accounts.

Top Query By Group

To do this, I need a correlated subquery. A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery. A correlated subquery opens a separate instance of the table for each record in the main query, allowing you to compare the results of the subquery to results from the main query.

In this case, my subquery looks like this:

SELECT TOP 3 [Total Price]
FROM Invoices I2
WHERE Invoices.[Account] = I2.[Account]
ORDER BY I2.[Total Price] DESC

The key here is the From clause:

FROM Invoices I2

This creates an "alias" for the table, renaming it I2. This is important because when we embed this query within another query also based on the Invoice table, it needs to know which instance of Invoices we are asking for.

If we run the subquery as is, it will ask us for the value of Invoices.[Account] in a parameter prompt. If we give it an account number, say 391002, it will return the top 3 values for that account.

But when we embed this query in another query, instead of prompting us for the account number, it will take the account number from the each record of the main query, match it to the subquery, and return the top values for that record. The complete query looks like this:

SELECT Account, OrderNum, [Total Price]
FROM Invoices
WHERE [Total Price] In
(SELECT TOP 3 [Total Price]
FROM Invoices I2
WHERE Invoices.[Account] = I2.[Account]
ORDER BY I2.[Total Price] DESC)
ORDER BY Account, [Total Price] DESC;

And returns the values in Figure 4.

Figure 4: Shows the result of the query to show the top 3 invoices for each Account.

Notice, however that account 237001 has 4 values. Why is that? Because two records have the same value, Access cannot decide which to display, so it displays both. Duplicates can happen in both simple and group top queries, but the solution is different for each. I'll address that next in Top Query Problem: Ties.

A free sample illustrating the Top Query can be found here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233.

.

Top Query Revealed: Simple Top Query

Top Query Revealed: Simple Top Query

Introduction

SQL, the query language of Microsoft Access, has many powerful, yet little known features. One of these is the Top Query. A Top query allows you to limit the results of a query to a certain number (or percentage) of records.

The term "top" is deceiving, because it implies "largest" values. However, this is not strictly the case. You can choose the top largest values or the top smallest values. As I'll show later, you can even choose random records.

Simple Top Query

Suppose I want to create a query which shows me the top 10 invoices (based on price) in my Invoices table.

To create a TOP query in SQL, add the TOP predicate immediately following the SELECT keyword followed by the number of records you want to see:

SELECT TOP 10 * FROM Invoices

But that's not the end of it. To define what is "top", you must sort on one or more fields, using use DESC for largest numbers and ASC for smallest numbers. (Technically, you don't need to add ASC if you wanted the smallest numbers because ascending order is default.) Adding that, the whole query looks like this:

SELECT TOP 10 *
FROM Invoices
ORDER BY [Total Price] DESC;

This will return 10 records with the largest values in the Total Price field. But you can also choose to see the top (or bottom) percent. So if I wanted to see the top 5% best sellers, I could do this:

SELECT TOP 5 PERCENT *
FROM Invoices
ORDER BY [Total Price] DESC;

Although I used the asterisk (*) in the above queries, I did so only for simplicity. Like any query, you can also include a field list in your query, like so:

SELECT TOP 10 Account, OrderNum,
[Pairs Shipped], [Total Price]
FROM Invoices
ORDER BY [Total Price] DESC;

You are not limited to the SQL view of a query to create a Top query, however. To create a TOP query in the Query Builder, open the Query Properties and look for the TOP property. See Figure 1.

Figure1: Shows the Query Builder window to create a Top query.

The drop down box gives you suggestions, but you are not limited to the values in the list. Choosing All, removes the Top predicate from the SQL statement and returns the query to a normal Select query.

The results of this query, whether created in the Query Builder or directly in SQL can be seen in Figure 2.

Figure2: Results of simple Top query.

Simple Top Query With Where Clause

I'm not limited to showing the top values for all invoices. I can also narrow the scope of the query with a Where clause just as you can with any Select query. For instance, if I wanted to see the top 3 invoices of a single Account (say, 237001), my query would look like this.

SELECT TOP 3 Account, OrderNum, [Total Price]
FROM Invoices
WHERE Account=237001
ORDER BY [Total Price] DESC;

Result of top 3 invoices for Account 237001.

As useful as this is, what if I wanted to find the top 10 Accounts in terms of total sales rather than individual invoices? Or suppose instead of the top 10 values for a particular Account, I wanted the top 10 values of each Account. I'll talk about that next time in: Top Query Revealed: Aggregate Values and Top Values By Group.

.

This Recordset Is Not Updateable. Why?

This Recordset Is Not Updateable. Why?
by Roger Carlson

Introduction

Non-updateable recordsets are a problem that may have many causes and may produce many different error messages. Some of those include:

"This recordset is not updateable."
"Operation must use an updateable query."
"Recordset is not updateable" (seen in the status bar of a query, form, or datasheet view of a table.)

What does this mean? Well, sometimes you can edit data in the Datasheet View of a query to change the information in the underlying table. Other times, you can't. When you can't, the query is "non-updateable". When you try to create a recordset object based on a non-updateable query, the recordset becomes non-updateable.

The Microsoft Office Access Help system has a fairly extensive list that details when queries are updateable and non-updateable. However, this list is difficult to find. It is also in different places depending on the Access version you're using. You would think that typing "updateable recordset" would find the information, but it doesn't.

In Access 2003, you can find this information if you type: "When can I update data from a query?"
In Access 2007, type: "edit data in a query"

I thought it would be useful to list the information in a place that's a little easier to find.

When Recordsets Are Always Updateable

A recordset is always updateable when:

1. It is based on a single table.
2. It is based on a query based on a single table.
3. It is based on a query based on tables with a one-to-one relationship.

When Recordsets Are Never Updateable

A recordset is never updateable when:

1. It is based on a Crosstab query.
2. It is based on a Union Query.
3. It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field.
4. It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions
   Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
5. It is based on a Query that includes a linked ODBC table with no unique index.
6. The database was opened as read-only or is located on a read-only drive.
7. It is a SQL pass-through query.
8. It is a query whose UniqueValues property is set to Yes. (That is, it is a query with a DISTINCT predicate.)
9. Cartesian Joins (that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view.)
10. Query based on three or more tables in which there is a many-to-one-to-many relationship.
    Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
11. Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

Recordsets Are Updateable Under Certain Conditions

Some queries, especially those involved in a Join, will not be updateable under some conditions, but will be under others. In other queries, even if the query itself is updateable, some of the fields will not be. The following are cases of query problems and their corresponding solutions.

1. Query based on a Join of tables with no Relationship.

• Problem: If a query is based on two or more tables that DO NOT have a relationship established (with Referential Integrity enabled), the query will be non-updateable.
• Solution: Create a Primary Key or Unique Index on ALL of the fields used in the Join on the "one-side" table. To be clear, this means ONE primary key or unique index based on all of the fields, not separate indexes on each field.

In a query based on a Join of tables with a one-to-many relationship (1:M), you might not be able to edit the data in one or more fields. As the following examples show :

2. Join field from the "one" side

• Problem: If you have a 1:M relationship created between two tables, you cannot change the primary key field (used in the Join) of the table on the "one" side of the relationship.
• Solution: Enable cascading updates between the two tables.

3. New records, if the "many" side join field doesn't appear in the datasheet

• Problem: In a query based on a 1:M relationship, you can create a new record and fill in the fields that come from the "one" side table, but if the join field from the "many" side table is not visible in the query (that is, the foreign key), you cannot add data to the "many" side fields.
• Solution: Add the join field from the "many" side table (ie, foreign key) to your query to allow adding new records.

4. New records on the "one" side that are duplicates of other "one" side records.

• Problem: When adding a new record, if you try to type into the "one" side fields, you will be attempting to create a new record. Even if you use the same primary key values, it will give you an error.
• Solution: Add a value to the "many" side join field (foreign key) that matches the "one" side join field (primary key) of an already existing record. The "one" side values will simply appear.

5. Join field from the "many" side, after you've updated data on the "one" side

• Problem: If you are currently editing fields from the "one" side of the relationship, you cannot change the "many" side join field (foreign key).
• Solution: Save the record; then you'll be able to make changes to the "many" side join field.

6. New records, if entire unique key of ODBC table isn't output

• Problem: This is different than #5 under Never Updateable. In this case, the primary key of the linked ODBC table exists, but is not added to the query.
• Solution: Select all primary key fields of ODBC tables to allow inserts into them.

7. Query does not have Update Data permissions

• Problem: Query (or underlying table) for which Update Data permission isn't granted.
• Solution: To modify data, permissions must be assigned.

8. Query does not have Delete Data Permissions

• Problem: Query (or underlying table) for which Delete Data permission isn't granted
• Solution: To delete data, permissions must be assigned.

Conclusion

The causes of non-updateable recordsets are many and varied. Some have solutions and others don't. Hopefully, this list will help you know the difference.

Addendum (January 28, 2010):
This is in response to a comment below.  I thought it was important enough to add to the main article.

Linked Excel Sheets Not Updateable
The question is why are my linked sheets from an Excel Workbook non-updateable?  The answer is not technical but legal.  Several years ago, Microsoft lost a patent infringement lawsuit that involved the ability to update records in Excel from Access.  The upshot is that in Access 2003 and later versions, by design, you can no longer update data in Excel spreadsheets.

.

New Sample: Report_SpellCheck

Report_SpellCheck

by A.D. Tejpal

This sample db demonstrates spell check on a report prior to its opening. Two alternatives are covered:

(a) Record source is updateable.
(b) Record source is non-updateable.

Version: Access 2000 file format.
References: DAO 3.6

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

.

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]

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.

New Sample: Flex Grid Appointment-Bookings Demo

Flex Grid Appointment-Bookings Demo

by Peter Hibbs

This zip file contains two demo databases which use the FlexGrid control to display appointments and bookings on a calendar type display (similar to the MS Outlook appointments system).

The Appointments database also has some basic code to synchronise MS Outlook appointments with the database.

The Bookings database can display bookings as colour codes on a calendar display for a year, month or day and allows for multiple bookings over multiple days.

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

.

What is a Join: Part 4 (Equi-Joins in the WHERE Clause)

Equi-Joins in the WHERE Clause

In my last post in this series on the JOIN (What Is A Join: Part 3 (Cartesian Joins)), I discussed the Cartesian Join, which has very few uses and in most cases should be avoided at all costs. However, there is one practical use for a Cartesian join as long as you restrict its output with a WHERE clause.

So far, I've created the Joins in the FROM clause of the SQL statement. This is standard SQL, but it's also possible to create an Equi-Join in the WHERE clause. As a general rule, it's better to create your join in the JOIN clause, but there are circumstances under which is it useful to do it in the WHERE, which I'll show in just a bit.

To create a Join in the WHERE clause, you create a Cartesian join and add a WHERE clause equating the common fields of both tables. In the Query Builder, add both tables to the table window, but you DON'T create a Join line between them. If the Query Builder adds one automatically, delete it. In the WHERE clause you make the join field of one table equal to the join field of the other.

For instance, if I wanted to create this query:

in the WHERE clause instead, I would do it like so:

Notice there is no Join line between the tables and the OrderID of the OrderDetails table is equal to the OrderID field of the Orders table. The SQL statement looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID)=[Orders].[OrderID];

Earlier, I showed that without the Where clause, I would get 28 records. However, with the Where clause, I get the same result as I did when I created the join in the FROM clause.

Where Clause Join
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

In general, this is not as efficient as creating the Join in the FROM clause.

Joining fields of different data types

Suppose the join fields in my two tables are different datatypes. Suppose OrderID in the Order table it is numeric and in OrderDetails it is text. This can happen when dealing with external data sources over which you have no control. You can't create a join on fields of different datatypes, so you have to use a conversion function to convert one of the fields. Since OrderID in the Orders table is a Long Integer, I can use the CLng function to convert OrderID in the OrderDetails table.

I can change my original Join clause:

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

As follows:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders INNER JOIN CLng(OrderDetails)
ON Orders.OrderID = OrderDetails.OrderID;

This will work. However this cannot be represented in the Design View of the query. If you ever open the query in Design View, it will give you an error and remove the Join line altogether. You must close the query without saving or your query will be ruined.

But if you create the Join in the WHERE clause:

SELECT Orders.OrderNumber, OrderDetails.OrderID,
OrderDetails.Quantity
FROM Orders, OrderDetails
WHERE OrderDetails.OrderID = CLng([Orders].[OrderID]);

This can be represented in the Design View.

Join Date/Time fields that have times

I have also seen cases where a DateTime field in one table held just the date value, but held date and time in the other table. I needed to use the DateValue function to remove the time from the DateTime in the one table. Again, it was external data over which I had no control.

Creating the Join in the WHERE clause solved the problem nicely. Something like this:

SELECT Patient.MRN, Patient.DischDate,
Billing.Charge
FROM Patient, Billing
WHERE DateValue(Patient.DischDate) = Billing.DischDate
AND Patient.MRN = Billing.MRN;

In my next post, I'll look at Outer Joins.

[top]