NewSample: Query_GrpSequentialsAndMissing

by AD Tejpal

This sample db demonstrates identification of data blocks as well as missing portions in a sequential series. Three types of source data are covered:


(a) Number series.

(b) Alpha-numeric series.

(c) Date series.

Subform on left shows all records, duly highlighting the start of each new block of sequential series. Subform at right depicts group-wise gist (for each PersonID) of sequential blocks as well as missing portions, duly indicating the start and end values for each set. Missing blocks are highlighted in light grey.

Version: Access 2000 file format.
 
You can find the sample here: http://www.rogersaccesslibrary.com/forum/query-grpsequentialsandmissing_topic556.html
 
.

New Sample: TableNormalizationByPureSQL

by A.D. Tejpal

This sample db demonstrates pure query based solution for normalization of data held by a non-normalized table (T_Source) and posting the converted contents to destination table (T_Normalized).


Steps:

1 - Create the empty destination table T_Normalized with a structure identical to that of table T_Source, but without the non-normalized fields (e.g. Red, Green, Blue, Yellow in this sample).

2 - Add two new fields to the newly created table T_Normalized. One meant for holding the names of non-normalized fields and the other for holding corresponding values. In the current sample, these two fields are named Color and Stock respectively.

3 - Create an auxiliary table named T_SourceFieldsConverted having a single field meant for holding names of non-normalized fields. In the current sample, this field is named Color. Populate this table with the names of non-normalized fields (i.e. Red, Green, Blue, Yellow in this sample).

4 - Execution of append query Q_AppNormalized will populate destination table T_Normalized with normalized data, duly converted from source table T_Source. This query is based upon Cartesian join between tables T_SourceFieldsConverted and T_Source.

Version: Access 2000 file format.
 
You can find the sample here: http://www.rogersaccesslibrary.com/forum/tablenormalizationbypuresql_topic554.html
 
.

Domain Function Example: Rolling Average in Query

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

1. Simulate AutoNumber with DMax
   
2. Running Sum with DSum
3. Numbered Query with DCount
4. "Difference Between" with DLookup/DMax
5. Begin Date and End Date from Effective Date

Another value that is difficult to produce in a query, is the Rolling Average for a given number of records.

For instance, suppose I wanted to display a rolling average for the last 12 weeks for the table below:

Figure1

For Week 26, I need to display the average for weeks 15-26 (39.85). For Week 25, it would be the average for weeks 14-25 (43.85), and so forth. For weeks with less than 12 in the recordset, it will average only those weeks available. So Week 9 would only average weeks 7-9 (53.67).

In other words, this:

Figure 2

The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on -- or the previous 12, for that matter. The only way to do it is to somehow identify the previous records in terms of a Where condition. Since this Where condition must be evaluated for each line, O can do this with a domain aggregate function or a correlated subquery. In this case, two domain functions and two subqueries.

For either method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, ID fits the bill.

Domain Function Method (DCount and DAvg)

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. DCount in particular will return the number of records in a given recordset. DAvg will return the average of a given recordset. Both functions have three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

The first step in this process is to create an unbroken sequence number for the records. It must be unbroken so I can subtract 12 from it to average the correct number of weeks. The second step produces the average.

Step1: DCount_RollingAverage1:

SELECT DCount("ID","Table1","ID <=" & [ID]) AS Sequence, tWeek, tValue
FROM Table1
ORDER BY ID DESC;

The Order By clause in the query is important. This will sort the query on the ID field. I'll need to have that order to use the criteria argument in the DCount.

Here's how it works.

For each record in the query, Access runs the DCount function. The DCount returns the number of records in the domain where the ID in the function is less than or equal to the ID in that record of the query.

So in the first record, the ID is 1. So the DCount opens the domain (essentially opens the Customers table again) and it sees that there is only 1 record whose ID is less than or equal to 1. So it returns 1.

Then it processes the second record. The ID of that record is 3, and the DCount function sees that there are only 2 records which have an ID whose value is less than or equal to 2. So it returns 2.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

 Figure 3

Step2: DCount_RollingAverage2:

Now that DCount_RollingAverage1is a recordset with an unbroken sequence, I can use as it as the record source for the query that will create the rolling averages:

SELECT Sequence, tWeek, tValue, DAvg("tValue","[DCount_RollingAverage1]",
"Sequence Between " & [Sequence] & " And " & [Sequence]-12) AS [12-Week Rolling Average]
FROM DCount_RollingAverage1;

For each record in the query, Access runs the DAvg function. The DAvg returns the average for the range of values between the sequence number and the sequence number minus 12.

So in the first record, the Sequence is 26. So the DAvg opens the domain (essentially opens Table1 again) and averages weeks 15-26. Then it processes the second record, averaging weeks 14-25 and so forth.

Figure 4

Subquery Method

There is no way to combine these two queries into one. To do that, I'd need to use a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: RollingAverages.mdb.

Domain Function Example: "Difference Between" in Query

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

1. Simulate AutoNumber with DMax
2. Running Sum with DSum
3. Numbered Query with DCount
4. Rolling Average with DAvg and DCount
5. Begin Date and End Date from Effective Date

Earlier, I looked at the Running Sum query. A similar (but opposite) problem is the Difference Between query. Unlike a running sum, which adds the value of a field in a record to the value of the same field in the previous record, the "difference between" query subtracts the value of the previous record from the value of the current record to show the difference.

For instance, suppose I wanted to display the difference in days between orders in the table below:

 

Figure1: Need to calculate the difference between records.

The difference in days between records 1 and 2 is 4, between 2 and 3 is 7, between 3 and 4 is -22, and so forth.

The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on. The only way to do it is to somehow identify the previous record in terms of a Where condition. Since this Where condition must be evaluated for each line, I can do this with a domain aggregate functions (DMax & DLookup).

For this method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, OrderDetailsID fits the bill.

The technique is similar to creating a Numbered Query or a Running Sum in a query, but instead of just counting or summing all the records above the current record, I have to find just the previous record. This adds an additional complication, which requires an additional domain function.

Specifically, I need three steps:

1. Use a domain function (DMax) to find the unique identifier the previous row.
2. Then feed that value in to another domain function (DLookup) that identifies the previous value,
3. And then subtract the previous value from the current value.

One way to show the difference between records is over the whole recordset. Later, I'll look at showing the difference between over groups of records.

Difference Between - Over All
Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. They have three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

Here are the steps:

1. I need a DMax function to return the OrderDetailID of the previous record in the table:
   DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID])
2. Next, I'll feed that to a DLookup function, which will return the date value from the previous row given number of records.
   DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID]))
3. Lastly, I'll subtract the this value from [OrderDate]of the current record and give the column an alias:[OrderDate]-DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID])) AS DaysBetween

The full query, looks like this:

SELECT tblOrderDetails.OrderDetailID, tblOrderDetails.OrderID, tblOrderDetails.OrderDate, nz([OrderDate]-DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & nz(DMax("OrderDetailID","tblOrderDetails","OrderDetailID < " & [OrderDetailID]),0)),0) AS DaysBetween

FROM tblOrderDetails

ORDER BY tblOrderDetails.OrderDetailID;

The two NZ() functions are needed to display a zero on the first line. Otherwise, it would return an ERROR.

The Order By clause in the query is important. This will sort the query on the OrderDetailID field. I'll need to have that order to use the criteria argument in the DMax.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

Figure 2: Shows the time difference in days between subsequent records.

Difference Between - Over Group
 SELECT tblOrderDetails.OrderDetailID, tblOrderDetails.OrderID, tblOrderDetails.OrderDate,

Nz([OrderDate]-DLookUp("OrderDate","tblOrderDetails","OrderDetailID = " & Nz(DMax("OrderDetailID","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID < " & [OrderDetailID]),0)),0) AS DaysBetween

FROM tblOrderDetails

ORDER BY tblOrderDetails.OrderDetailID;

Figure 3: As the OrderID changes, the DaysBetween resets to zero.

The only difference between this query and the OverAll query is the addition of

"OrderID = " & [OrderID]

to the "Where" condition of the DMax function. This sets the value of the first record of each group to zero.

Subquery Method

As I said, these can also be done with a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: DaysBetween.mdb

Domain Function Example: Running Sum with DSum

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.


Other Examples

1. Simulate AutoNumber with DMax
2. Numbered Query with DCount
3. Difference Between with DMax
4. Rolling Average with DAvg and DCount
5. Begin Date and End Date from Effective Date

Running Sum

One thing that is easy to do in a report, but difficult to produce in a query, is a Running Sum. A running sum adds the value of a field in a record to the value of the same field in the previous record.

In a report, there are two common types of running sums: Over All and Over Group. To create a running over all, I place a textbox control on the details section, setting the Control Source property to the field I want summed and setting the Running Sum property to Over, as in Figure 1. Figure 2 shows the results. To create one over a group (say each Order number), I'd set the Running Sum property to Over Group

Figure 1

Figure 2

But it's not so easy in a query. The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record above the one you're on. The only way to do it is to somehow identify the previous record in terms of a Where condition. Since this Where condition must be evaluated for each line, I can do this with a domain aggregate function (DSum) is ideal.

For this method to work, I must have a unique record ID. The Autonumber field is ideal for this. It doesn't matter if there are gaps in the sequence, but I have to sort on this field, so there cannot be duplicates and they must be in the order I need displayed. In the above sample, OrderDetailsID fits the bill.

There are two different ways I might want to see the running sum:

1. Over the whole list (see Figure 3)
2. Over each group, where the running sum resets to zero as the Order ID changes (see Figure 5).

Running Sum Over All

There are two methods to create a running sum: DSum domain aggregate function and a correlated subquery. I'm going to concentrate on the DSum method here.

DSum

SELECT OrderDetailID, OrderID, ProductID, Price,
DSum("Price","tblOrderDetails","OrderDetailID <=" & [OrderDetailID]) AS RunningSum
FROM tblOrderDetails;

The DSum function works much the same as the DCount in the Numbered Query example, but instead of counting the records, it sums them. Unfortunately, the DSum does not return a formatted number.

Figure 3

So if you are summing a currency field, you'll have to apply the formatting yourself. To do that, we can modify the DSum function adding the Format function to display the number as currency. Like so:

Format(DSum("Price","tblOrderDetails","OrderDetailID <=" & [OrderDetailID]), "Currency") AS RunningSumFormatted

Figure 4

Running Sum Over Group

In order to get the running sum for each grouping of OrderID, all I need to do is add another condition to the "Where" argument of the DSum:

DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]) AS RunningSum

In this case, "OrderID = " & [OrderID]

The complete SQL statement (including formatting):

SELECT OrderDetailID, OrderID, ProductID, Price,
Format(DSum("Price","tblOrderDetails","OrderID = " & [OrderID] & " And OrderDetailID <=" & [OrderDetailID]),"Currency") AS RunningSum
FROM tblOrderDetails;

Figure 5

Subquery Method

Both types of running sum can also be done with correlated subqueries. I may discuss this at a later date. However, you can find both methods on my website, in this sample: RunningSumInQuery.mdb.

.

New Sample: Form_MtoMViaCheckBoxArray

by AD Tejpal

This sample db demonstrates use of check box array for populating a junction table serving many to many relationship between clients and training courses. Data is stored in normalized manner in tables T_Clients, T_Courses and T_ClientCourses. No temporary table is needed.


Two styles of user interface are demonstrated:

1 - Normal Style (Using Virtual Records Matrix):
-------------------------------------------------

1.1 - Client particulars are displayed in the parent form while all available course options are displayed in a subform, as a matrix of virtual new records.

1.2 - As and when the user clicks a check box into selected state against desired course in the subform, the row in question gets converted into a freshly entered actual record in the junction table.

1.3 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted. This is accompanied by a virtual new record getting displayed in lieu of the recently deleted actual record (At all times, the subform matrix continues to display all available course options).

1.4 - Total number of courses opted for current client as well as the grand total for all clients are also displayed in the parent form

2 - Spreadsheet Style - Datasheet View (Using Crosstab Query):
-----------------------------------------------------------------

2.1 - Adequate number of check boxes along with child labels are inserted in the form at design stage. This is a one time exercise, using the subroutine P_AddControls().

2.2 - Each check box column of this datasheet form (used as a subform) represents an individual course. A crosstab query, based upon Cartesian join between tables T_Clients and T_Courses, serves as the record source.

2.3 - As and when the user clicks a check box into selected state in desired course column, a record with appropriate values for ClientID and CourseID gets added to the junction table.

2.4 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted.

2.5 - First three columns display client name, list of courses opted and total number of courses opted respectively. These three columns are frozen so as to always remain in view while the user moves across check box columns representing individual courses.

2.6 - Third column depicts total number of courses opted for each client. Bottom cell of this column shows the overall total courses opted for all clients. Overall total is also depicted within parenthesis in header caption for this column, so as to always remain in view despite vertical scrolling.

2.7 - For each course, total number of clients who have opted for it, is depicted within parenthesis in the caption for respective column header, so as to always remain in view despite vertical scrolling.

You can find this sample here:
http://www.rogersaccesslibrary.com/forum/Form-mtomviacheckboxarray_topic553.html

.

Domain Function Examples: Numbered Query With DCount

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

1. Simulate AutoNumber with DMax
2. Running Sum with DSum
3. Difference Between with DMax
4. Rolling Average with DAvg and DCount 
5. Begin Date and End Date from Effective Date

Numbered Query

One interesting problem is how to create a numbered sequence in a query, that is, have each record numbered sequentially.

This is fairly easy to accomplish in an Access report. All you need to do in a report is add an unbound text box. In the control source, put =1 and set the Running Sum property to Over All.

But suppose you don't want to do it in a report. Suppose you want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregated function DCount and the second uses a Correlated Subquery. Since this series is devoted to domain functions, I'm going to concentrate on that.

Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the Customers table, there are two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.

DCount Method

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. DCount in particular will return the number of records in a given recordset. It has three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

The specific DCount expression we're going to use looks like this:

DCount("CustID","Customers","CustID <=" & [CustID]

In the query, it will look like the following.

SELECT DCount("CustID","Customers","CustID <=" & [CustID]) AS Sequence,
CustName, CustPhone, CustID
FROM Customers
ORDER BY CustID;

The Order By clause in the query is important. This will sort the query on the CustID field. We'll need to have that order to use the criteria argument in the DCount.

Here's how it works.

For each record in the query, Access runs the DCount function. The DCount returns the number of records in the domain where the CustID in the function is less than or equal to the CustID in that record of the query.

So in the first record, the CustID is 1. So the DCount opens the domain (essentially opens the Customers table again) and it sees that there is only 1 record whose CustID is less than or equal to 1. So it returns 1.

Then it processes the second record. The CustID of that record is 3, and the DCount function sees that there are only 2 records which have an CustID whose value is less than or equal to 3. So it returns 2.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

The output of this query looks like Figure 1. Strictly speaking, you wouldn't need to show the CustID number in the query at all. However, I included it to show that while the order is the same as CustID, the sequence number does not have gaps in the numbering sequence.

You don't need to use a number field as your Order By field. You can sort on text fields and number the query as well.

If you wanted to sort on the Customer Name field (CustName), you would change the DCount to the following:

DCount("CustName","Customers","CustName <='" & [CustName] & "'")
 The output would look like this:

Subquery Method
As I said, this can also be done with a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: NumberedQuery.mdb.
.