by AD Tejpal
This sample db demonstrates filling up of missing values 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 the start and end values for each missing range of sequential values per PersonID. Subform at right depicts all the missing sequential values duly filled in. Each record representing start of a new sequence is highlighted in color.
Note:
A single field table (T_Ref) populated with sequential numbers from 0 onwards is used as the driver for eliciting the missing values. The number of records in this table should be such as to cover the maximum likely range of missing values.
Version: Access 2000 file format.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic558_post573.html
.
Tuesday, February 8, 2011
Monday, February 7, 2011
Begin Date and End Date from Effective Date
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:
For instance, suppose I have a PriceList table where the price for each product is in effect for only a certain date range. But since maintaining a Begin Date and End Date is prone to error, I'd like to simply store an Effective Date in the record. Conceptually, I don't really need both a Begin Date and End Date. A record is in effect from its own Effective Date to the Effective Date (minus 1) of the next row. In other words, consider figure 1below. In row 1, Product 1 is $3 from 1/1/2009 (EffectiveDate) to 12/1/2009 (Effective Date of row 2 minus 1).
So from this data:
I'd like to a create query which would produce following result:
The problem is that SQL does not have positional notation like Excel does. There's no way to simply point to the record following the one you're on. The only way to do it is to somehow identify the next record in terms of the data stored in the record.
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, PriceID fits the bill.
The technique is similar to creating a Difference Between Query, but instead of finding the difference between a field on this row and the same field on a previous row, I want to show the value of the field on the NEXT row in the current row.
Over all, I need three steps:
1. Identify the Primary Key of next row.
2. Feed that value to a function (or query) that identifies the next date value,
3. Display the next date value on the current record (manipulating as necessary)
DMax 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. 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:
I need a DMin function to return the PriceID of the next record in the table:
DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]
Next, I'll feed that to a DLookup function, which will return the date value from the next row given number of records.
DLookUp("EffectiveDate","PriceList","PriceID = " & DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]))
Lastly, I'll subtract 1 from [EffectiveDate] of the current record and give the column an alias:
DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))) AS EndDate
The full query, looks like this:
SELECT PriceID, ProductID, EffectiveDate AS BeginDate, DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))) AS EndDate, Price
FROM PriceList
ORDER BY PriceID;
The NZ() function is needed to prevent the last row of a group from displaying an ERROR.
The Order By clause in the query is important. This will sort the query on the PriceID field. I'll need to have that order to use the criteria argument in the DMin.
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 3a: Shows the calculated EndDate with NULL for the end date of the last record in the group.
This, of course, accurately represents the data because I don't know the end date of the currently effective price. Having a NULL in the field is most correct from a design standpoint. However, null values are difficult to query, so it is easier from a practical standpoint to put an actual value in the end date that is far in the future. I usually use 12/31/9999 for a future date that's not likely to become obsolete any time soon.
I can easily accomplish this with another NZ function:
SELECT PriceID, ProductID, EffectiveDate AS BeginDate, Nz(DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))),#12/31/9999#) AS EndDate, Price
Subquery and Outer Join Methods
This query can also be done with a correlated subquery or with an Outer Join, which I may discuss at a later date. However, you can find all three methods on my website in this sample: BeginDateEndDateQuery.mdb.
.
Other Examples:
- Simulate AutoNumber with DMax
- Running Sum with DSum
- Numbered Query with DCount
- Rolling Average with DAvg and DCount
- "Difference Between" with DLookup/DMax
For instance, suppose I have a PriceList table where the price for each product is in effect for only a certain date range. But since maintaining a Begin Date and End Date is prone to error, I'd like to simply store an Effective Date in the record. Conceptually, I don't really need both a Begin Date and End Date. A record is in effect from its own Effective Date to the Effective Date (minus 1) of the next row. In other words, consider figure 1below. In row 1, Product 1 is $3 from 1/1/2009 (EffectiveDate) to 12/1/2009 (Effective Date of row 2 minus 1).
So from this data:
Figure 1
I'd like to a create query which would produce following result:
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 following the one you're on. The only way to do it is to somehow identify the next record in terms of the data stored in the record.
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, PriceID fits the bill.
The technique is similar to creating a Difference Between Query, but instead of finding the difference between a field on this row and the same field on a previous row, I want to show the value of the field on the NEXT row in the current row.
Over all, I need three steps:
1. Identify the Primary Key of next row.
2. Feed that value to a function (or query) that identifies the next date value,
3. Display the next date value on the current record (manipulating as necessary)
DMax 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. 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:
I need a DMin function to return the PriceID of the next record in the table:
DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]
Next, I'll feed that to a DLookup function, which will return the date value from the next row given number of records.
DLookUp("EffectiveDate","PriceList","PriceID = " & DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]))
Lastly, I'll subtract 1 from [EffectiveDate] of the current record and give the column an alias:
DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))) AS EndDate
The full query, looks like this:
SELECT PriceID, ProductID, EffectiveDate AS BeginDate, DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))) AS EndDate, Price
FROM PriceList
ORDER BY PriceID;
The NZ() function is needed to prevent the last row of a group from displaying an ERROR.
The Order By clause in the query is important. This will sort the query on the PriceID field. I'll need to have that order to use the criteria argument in the DMin.
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.
This, of course, accurately represents the data because I don't know the end date of the currently effective price. Having a NULL in the field is most correct from a design standpoint. However, null values are difficult to query, so it is easier from a practical standpoint to put an actual value in the end date that is far in the future. I usually use 12/31/9999 for a future date that's not likely to become obsolete any time soon.
I can easily accomplish this with another NZ function:
SELECT PriceID, ProductID, EffectiveDate AS BeginDate, Nz(DateAdd("d",-1,DLookUp("EffectiveDate","PriceList","PriceID = " & Nz(DMin("PriceID","PriceList","ProductID = " & [ProductID] & " And PriceID > " & [PriceID]),0))),#12/31/9999#) AS EndDate, Price
FROM PriceList
ORDER BY PriceID;
Figure 3b: Shows the calculated EndDate with an artificial end date far in the future in the last record of the group.
Subquery and Outer Join Methods
This query can also be done with a correlated subquery or with an Outer Join, which I may discuss at a later date. However, you can find all three methods on my website in this sample: BeginDateEndDateQuery.mdb.
.
Tuesday, February 1, 2011
New Sample: Mode.mdb
by Roger Carlson
Access has no Mode function, so you have to write one of your own. This sample database shows how to do that.
You use the function much like the built-in Domain functions (DLookUp, DMax, and so on). That is, you must provide the 1) field name, 2) table name, and 3) a 'Where' Criteria. When used in an aggregate query (see below) you MUST add each field 'grouped by' into the Where Criteria.
See this for more on Domain Aggregate functions see Domain Functions Demystified.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/modemdb-intermediate_topic557.html
Access has no Mode function, so you have to write one of your own. This sample database shows how to do that.
You use the function much like the built-in Domain functions (DLookUp, DMax, and so on). That is, you must provide the 1) field name, 2) table name, and 3) a 'Where' Criteria. When used in an aggregate query (see below) you MUST add each field 'grouped by' into the Where Criteria.
See this for more on Domain Aggregate functions see Domain Functions Demystified.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/modemdb-intermediate_topic557.html
Subscribe to:
Posts (Atom)