Friday, July 31, 2009

New Sample: Report_PseudoGraphs

Report_PseudoGraphs

by A.D. Tejpal

Explanatory Notes This sample db demonstrates simulated graphs on access reports, using a single text box for a given series. The following situations are covered:
  1. Monthly salary as per scales notified from time to time. For a given month and year, the most recent scale notification upto that month year becomes applicable. Whenever there is a change in salary from one month to the next, different back color is assigned so as to facilitate visual appreciation.
  2. Room bookings.
  3. Score sheets for star war games depicting the initial count of units for engine impulse, engine warp-1, engine warp-2 and superstructure. Depending upon field values in source table, series of numbered boxes are depicted.

Note: (a) Simulation of graph using a single text box is implemented by suitable manipulation of report's MoveLayout property. (b) In form F_Salary, the technique for programmatically positioning specific block of records appropriately in the display window of subform control showing notification of salary scales, is also demonstrated. (c) In order to keep sample data for salary scales current with respect to the present year, update query named Q_SalaryMaster_UpDt is executed via load event of form F_SwitchBoard. While undertaking practical adaptation of this sample db, this statement should be disabled.

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

.

Tuesday, July 28, 2009

A Business Case for Micrsoft Access

I read the following article some years ago and just recently ran into it again. It details how Access can be used as a part of an organization's over-all business strategy.

Database Evolution: Microsoft Access within an Organization's Database Strategy
by Luke Chung, President of FMS, Inc.

Here's a part:

Abstract

There has been a lot of confusion over the role of Microsoft Access within an organization. Sitting between the power of Excel and client server databases, Access extends from simple end-user tasks to mission critical operations. This paper hopes to cover the issues surrounding Access:

  • Why it's become problematic in large organizations including the Sarbanes-Oxley Act (SOX)
  • Where it's appropriate to be used, and
  • Where it's not

It also focuses on the overall principle that most Access applications that become mission critical did not start out that way, but evolved into that role.

Software applications share many similarities with biology and Darwinian forces. Some applications evolve and survive, while others go extinct. Anticipating, rather than fighting, the inevitable process of database evolution and natural selection is the key to using Access effectively within an organization.

Read the rest of it here: http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp.

It's well worth it.

.

Thursday, July 2, 2009

New Sample: Form_CustomSeriesByDefaultProperty

Form_CustomSeriesByDefaultProperty

By A.D. Tejpal

This sample db demonstrates a novel approach involving generation of alphanumeric sequence as per specified prefix, via a common function embedded as default value property of the bound control carrying such values.

As the data entry progresses, the default value keeps incrementing as required, without having to undertake any special manipulation via form's current event. Moreover, it is not necessary to use even other form events like Dirty / Before or After Insert / Before or After Update etc.

The prefix is selected via combo box on the main form. Whenever, a new prefix is selected, the newly displayed default value on the subform gets set 1 higher than the existing highest in alpha-numeric sequence pertaining to the selected prefix. If there is no existing entry with the given prefix, the default value gets set to prefix followed by 000001.

Type of prefix can be selected via the option group as follows:
(a) Prefix by combo box selection.
(b) Prefix as per current year (yyyy)
(c) Prefix as per current year month (yyyymm)

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

.

Wednesday, July 1, 2009

Select Queries Part 4: PARAMETERS, TOP, DISTINCT, and TRANSFORM...PIVOT

This is my final post in this series about Select Queries.

In Select Queries Part 1: Simple Queries, I discussed the SELECT and FROM clauses. In Select Queries Part 2: Restricting Rows - the Where Clause, I talked about the WHERE clause. Select Queries Part 3: Sorting and Grouping (ORDER BY, GROUP BY) showed the Order By and Group By clauses.

This time I'm going to discuss a few lesser known but powerful clauses: Top, Distinct, Transform...Pivot, and Parameters.

PREDICATE (optional)

Another less well known section of a SQL statement is the Predicate. The Predicate follows the SELECT keyword and precedes the Field List. There are two important predicates: TOP and DISCTINCT.

Top Predicate

The Top Predicate allows you to display the top(x) values from a query in terms of either a number or percentage. I can't really talk about the TOP predicate without discussing the ORDER BY clause because the rows displayed are determined by the ORDER BY clause.

Examples:

SELECT TOP 10 ProductName
FROM Products
ORDER BY Cost

This will display ten rows of ProductNames whose costs are the LOWEST.

SELECT TOP 25% ProductName
FROM Products
ORDER BY Cost DESC

This will display 25% of the total number of rows of the Product table whose costs are HIGHEST.

In other words, the query creates a standard Select Query, applies the sort order in the ORDER BY clause, then displays just the top X values from the query.

To add a TOP predicate in the Query Builder, go to the Properties of the Query and look for the Top Values property:


A word about duplicates: If there are duplicates in Top rows, they will all be displayed. So, sometimes Top 10 could return 11 or even more rows. It is possible to remove these duplicates with a subquery. To do that and a lot of interesting things with the TOP predicate, download a free database sample from my website called: TopQuery which goes into more detail including:

  • Removing Duplicates.

  • Top values with Aggregates (Totals Query)

  • Top values per Group

  • Returning Random X records from your table.

  • User input TOP value (parameter).

DISTINCT Predicate

The DISTINCT predicate will remove duplicates from your result set based on the fields in your Field List. For instance, if I had a Products table that looked like this:

ProductName Cost
----------- ----
Ax           $3
Hammer       $15
Hammer       $10
Wrench       $5
Wrench       $4

SELECT DISTINCT ProductName
FROM Products

Would return:

ProductName
-----------
Ax
Hammer
Wrench

Removing the duplicated rows. The effect is the same as using GROUP BY clause without an aggregate function:

SELECT ProductName
FROM Products
GROUP BY ProductName;

DISTINCTROW

The DISTINCTROW predicate is unique to Access. It will return unique records across the entire record, not just the fields in the Field List.

SELECT DISTINCTROW ProductName
FROM Products

returns:

ProductName
-----------
Ax
Hammer
Hammer
Wrench
Wrench

Honestly, though, I've never found a good use for DISTINCTROW because I always have a Primary Key in all my tables, so all of the rows are already unique.

To create either the DISTINCT predicate in the Query Builder, set the Unique Values property to Yes, to create the DISTINCTROW predicate set the Unique Rows value to Yes.



TRANSFORM...PIVOT (Crosstab Query)

The Crosstab Query is an Access Specific query that allows you to display recordset results in a much more compact form. For instance, if I had an OrderDetail table that looked like this:


I could use a Crosstab Query to show the data like this:



The Crosstab Query used two clauses, the TRANSFORM, which comes before the SELECT statement, and the PIVOT, which follows the GROUP BY. In SQL View, it looks like this:

TRANSFORM Sum(Quantity) AS SumOfQuantity
SELECT CustomerNum, OrderID
FROM OrderDetail
GROUP BY CustomerNum, OrderID
PIVOT ProductName;

In the Query Builder, it looks like this:


The easiest way to learn Crosstab Queries is to use the Crosstab Wizard that's available when you create a New Query.

PARAMETERS Clause (Optional)

Queries can also accept user input. This input is called a Parameter. Parameters are placed in the WHERE clause of a SQL statement and takes the form of a Prompt in square brackets. For example:

SELECT * FROM Customers
WHERE CustomerNum [Enter Customer Number]

When the query is run, a parameter input box appears:


When the user inputs a valid customer number, the query returns the row(s) applicable to the customer entered.

A parameter can also reference a control on a form. Suppose I have a Customer form and I want to see all of the orders associated with a particular customer. I could have a query that looks at the CustomerNum in the current form and return all of the order numbers for that customer.



So if I had a query that looked like this:

SELECT CustomerNum, OrderNumber
FROM Customer INNER JOIN Orders
ON Customer.CustomerID = Orders.CustomerID
WHERE CustomerNum=[forms]![frmCustomer]![CustomerNum]

It would look at the value in the CustomerNum text box on the frmCustomer form and use that as the parameter value to return all the orders for customer 100.

Access does a pretty good job of figuring out what the data type of a parameter is. However, there are times when it gets confused. This is particularly true when trying to use parameters with a Crosstab query. In these cases, you can define what the data type of the parameter will be with the PARAMETERS Clause.

The PARAMETERS clause goes before the SELECT statement in a Select query:

PARAMETERS [Enter Customer Num] Text(255);
SELECT Customer.*
FROM Customer
WHERE CustomerNum=[Enter Customer Number];

Notice the semi-colon at the end.

In a Crosstab query, it goes before the TRANSFORM statement:

PARAMETERS [Enter CustomerNum] Text(255);
TRANSFORM Sum(Quantity) AS SumOfQuantity
SELECT CustomerNum, OrderID
FROM OrderDetail
WHERE CustomerNum=[Enter CustomerNum]))
GROUP BY CustomerNum, OrderID
PIVOT ProductName;

To create parameters in the Design View, choose Parameters from the Design Ribbon (A2007) or from the Query>Parameters Menu (A2003 and before). You will get a dialog box that looks something like this:


.