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]

New Sample: Form_CustomAutoComplete

Form_CustomAutoComplete

by A.D. Tejpal

This sample db demonstrates customized auto-completion of data entry as per area of interest. Based upon desired profile (i.e. area of interest, e.g. Aviation, InfoTech, Medical etc) as selected in the combo box, reference phrases meant to be used for auto-completion get displayed in the subform at left.

The auto-complete feature comes into play as soon as typing commences on a new line in the text box in subform at right. Appropriate portion of text from closest match in reference phrases gets displayed next to the cursor, shaded in selected state, as suggested continuation. If the user wishes to accept the suggested continuation, pressing right arrow clears the shading, converting it into accepted text.

On the other hand, if the user continues typing further, the shaded portion of suggested continuation continues to shrink correspondingly, so long as there is no deviation from reference text. As soon as there is a deviation, new suggested text, as per next closest match with reference phrases, gets displayed, duly shaded - and so on.

At all stages, currently matching reference text is shown highlighted in special color, on the subform at left.

If no match is found within the selected profile, a search for matching reference text in other profiles is conducted. If found, the particular phrase is used for auto-completion. Simultaneously, its linkage to current profile is implemented.

If desired, the user can also type fresh reference text directly in the subform at left. Records for reference text, freshly added during current session under a given profile get highlighted in special color.

If newly entered data contains completely new sentences (marked by line breaks) not present in reference table, these are added to this table after obtaining user's consent via form's BeforeUpdate event. Simultaneously, appropriate linkage to current profile is established by inserting necessary entries in bridging table T_Link (many to many relationship).

Version: Access 2000 file format.

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

[top]

What Is A Join: Part 3 (Cartesian Joins)

Cartesian Joins (Cross Joins)

This is part 3 of a multi-part series on the SQL Join. Last time I talked about the most common type of join, the Equi-Join (see: What is a JOIN: Part 2 (Inner Join)). This time, I'm going to talk about the least common join type: the Cartesian or Cross Join.

Cartesian Joins are notable mostly for being avoided. A Cartesian Join matches every record in one table to every record in another. Except in very rare instances, this is something to be avoided at all costs. Since it joins all records from both tables, the resultset of a Cartesian join can quickly become huge. A Cartesian join of two tables, each holding a thousand records will result in a query displaying a million records (1000 x 1000). Cross-joining two tables with a hundred thousand records each will exceed the 2GB limit of Access.

A Cartesian join is most commonly created in the Query Builder by neglecting to add a join line between the tables.

Since Orders has 4 records and OrderDetails has 7 records, the resultset of the Cartesian join will have 28 records:

Cartesian Join
OrderNumber	OrderID	Quantity
111	1	1
222	1	1
333	1	1
444	1	1
111	2	2
222	2	2
333	2	2
444	2	2
111	3	3
222	3	3
333	3	3
444	3	3
111	1	2
222	1	2
333	1	2
444	1	2
111	1	1
222	1	1
333	1	1
444	1	1
111	3	2
222	3	2
333	3	2
444	3	2
111	3	1
222	3	1
333	3	1
444	3	1

As you can see, the information is fairly useless.

The corresponding SQL statement (SQL View) looks like this:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders, OrderDetails;

There is no Join clause between the tables or Join criteria, simply a comma between the tables.

But as I said, there are some rare uses for a Cartesian join.

One almost trivial use is to add a value to each record of a table. For instance, suppose I have a table called User with one record.

User

UserName

Roger

If I create a Cartesian join with my Orders table, I'll get:

Cartesian Join 2
OrderID	CustomerID	OrderNumber	UserName
1	1	111	Roger
2	2	222	Roger
3	3	333	Roger
4	1	444	Roger

Now, of course there are other ways to do this. However, the Cartesian join has the additional property of being non-updateable. If this is a desirable effect, then a Cartesian join can be useful.

But the main use of a Cartesian join is in creating joins in the Where clause, which I'll talk about in What is a Join: Part 4 (Equi-Joins in the WHERE Clause) .

[top]

What is a JOIN: Part 2 (Inner Join)

Equi-Join (Inner Join)

This is part 2 of a multi-part series on the SQL Join. For an introduction to the whole topic, take a look at:
What Is A Join: Part 1.

This time I'm going to talk about the Equi-Join, also called an Inner Join. An equi-join combines records from two tables which have common values in both tables and displays only those with matching records. Since an equi-join is the most common type of join, it is often simply referred to as a Join.

Consider the following Order and OrderDetail table

Orders
OrderID	OrderNumber
1	111
2	222
3	333
4	444

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

Joining them on the OrderID field results in:

Single Join Query
OrderNumber	OrderID	Quantity
111	1	1
111	1	2
111	1	1
222	2	2
333	3	3
333	3	2
333	3	1

Notice that although the Orders table has a record for Order Number 444, it does not display in Single Join Query. That's because there are no matching records in OrderDetails. An equi-join shows only those records with matching values in both tables.

The SQL for this query is:

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

In the Query Builder, it looks like this:

Notice the 1 and infinity symbol (∞) on the Join line. These indicate that a Relationship exists between these tables and that the OrderID is a Primary Key for the table on the "One-side". This is important, because if you want a query with a join of two tables to be updateable, the field or fields on the "one-side" of the join must either be a Primary Key or a Unique Index. I'll talk more about the updateability of queries in a later post.

Multiple Joins

A query is not limited to a single join. Each join is between just two tables, but the FROM clause of the query can have many joins. For instance, I can add the Products table

Products
ProductID	ProductName	Cost	Price
1	hammer	$1.00	$2.00
2	nail - 10p	$0.01	$0.02
3	saw	$5.00	$10.00
4	wrench	$6.00	$7.00
5	nail - 8p	$0.01	$0.02
6	drill	$20.00	$40.00
7	screw - 1x4	$0.02	$0.04
8	hammer	$4.00	$5.00

to the above query to get the product and price for the order.

Something like this:

MultipleJoin
OrderNumber	OrderID	Quantity	ProductName	Price
111	1	1	hammer	$2.00
222	2	2	nail - 10p	$0.02
333	3	3	saw	$10.00
111	1	2	wrench	$7.00
111	1	1	saw	$10.00
333	3	2	hammer	$2.00
333	3	1	saw	$10.00

In the Query Builder, it looks like this:

The SQL is:

SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity, Products.ProductName, Products.Price
FROM Products INNER JOIN
(Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID)
ON Products.ProductID = OrderDetails.ProductID;

Next time, I'll discuss the least common type of join, the Cartesian or Cross Join.

.

What is a Join: Part 1 (Introduction)

What is a Join: Part 1

Introduction

This is the beginning of a multi-part series devoted to Joins in Microsoft Access.

A Join connects the records of two tables based on common information in the records. In What Is Normalization: Part II, I discussed how to organize data into smaller tables to reduce redundancy. In What is Normalization: Part III, I discussed relationships and their role in defining how tables should be put back together so all of the data is retained. The Join is a SQL (Structured Query Language) implementation of a relationship. In other words, if normalization splits tables apart, the Join is how you put them back together.

In Select Queries: Part I, I talked briefly about the SQL FROM clause, where Joins are created. The general SQL syntax for the most common type of Join (Equi-Join) is:

FROM Table1 JOIN Table2 ON Table1.JoinField = Table2.JoinField

Where JoinField is a field in each table which holds the same data. So, if I had Order and OrderDetail tables, they would both have to have an OrderID field in common to relate the records in the two tables. The FROM clause for this join would be:

FROM Order JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

Although the Equi-Join (also known as an Inner Join) is the most common, there are many different types of joins:

1. Equi-Join (Inner Join)
   
2. Cartesian Join (Cross Join)
   
3. Equi-Joins in the Where Clause
   
4. Outer Join (Left Join or Right Join)
   
   1. Unmatched Query
      
   2. Full Outer Join
      
5. Self-Join
   
6. Theta Join
   

In subsequent posts, I'll be discussing each of these in turn. Next up, the Equi-Join.

.