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]