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.

.