Domain Functions in Microsoft Access

By Lee Allard, Director of Institutional Research, Siena College  

For those of us who use Microsoft Access, a useful but sometimes overlooked toolkit is the set of “domain” functions. In some respects, these domain functions are similar to the more familiar aggregate functions (and may produce the same results in some cases), but domain functions have some additional and unique functionality. One important use of domain functions, illustrated in this Tech Tip, is to produce sequence numbers for a group of related records - something otherwise difficult to accomplish in Access. 

As a basic example, let’s start with the simple Access data table shown below, which shows grade data for a group of students. There are multiple records for each student, which vary depending on the number of courses taken.

DomainAccess1.jpg

We will use a domain function called “DCount” to assign a sequence number to each record for a given student. The query structure will appear as follows: 

DomainAccess2.jpg

This is a very simple Access query, with one twist, namely the DCount function in the last column. The full syntax (not visible in the query above) is as follows:  

Course_seq: DCount("stdt_id","dcount example table","[stdt_id]=""" & [stdt_id] & """ and [course]<= """ & [course] & """ ") 

The syntax for all the domain functions, while somewhat clumsy, follows the same basic pattern. The first element, enclosed in quotes, is the field to which the function will be applied. In this case, it is stdt_id, since we want to generate a set of sequence numbers for the records for each student. The second element is the “domain” (i.e. table or query) from which the data is drawn, in this case “DCount example table.” The third and most complex element is the criteria field, which specifies the criteria that will determine how the domain function is calculated. The whole criteria field must be enclosed in quotes. In the example above, the criteria field indicates that for each student we are looking for the number of courses less than or equal (alphanumerically) to the current course.  

This is a bit confusing at first glance, so let’s go through an example of how the DCount function will read the data from the first block of records in the example table, specifically for the student named Robert Smith. The first element of the criteria field ([stdt_id]=""" & [stdt_id] & """) simply specifies that we are evaluating the function within each group of records for a particular student. (Note: in the example table, stdt_id is defined as an alphanumeric field. If it were defined as a numeric field, the appropriate syntax for the DCount function would be [stdt_id]=" & [stdt_id] & ". For a date field, the syntax would be [date_field]=#" & [date_field] & "#). 

The second element of the criteria field ([course]<= """ & [course] & """) performs the actual comparison to determine the sequence numbers for each student. For Robert Smith, the DCount function assigns the first course, ENGL102, a value of 1 because there is only one record for this student with a course value less than or equal to ENGL102, namely itself. Similarly, the DCount function assigns the second course, HIST101, a value of 2 because there are two records for this student with a course value of less than or equal to HIST101 (namely itself and ENGL102). Finally, the third course, PHIL201, is assigned a value of 3 because there are three records for this student with a course value of less than PHIL201.  

So when we run the query, the output will look like this:   

DomainAccess3.jpg

One possible application of this query is to use the sequence numbers to produce a file with a single record for each student, containing all the grades for that student. The query structure will look like this:

DomainAccess4.jpg

Each grade field (not fully visible in the above snapshot) looks like this: 

Grade1: Min(IIf([course_seq]=1,[Course] & "-" & [Grade]))
Grade2: Min(IIf([course_seq]=2,[Course] & "-" & [Grade]))
etc.

Note that we have clicked the “Totals” option in the menu bar to allow us to use aggregate functions (“Group By” and “Min” as specified in the “Total” row of the query), which will produce a single record for each student. 

The output of this query is as follows: 

DomainAccess5.jpg
Thus, for each student we now have a single record showing all of the courses they have taken, along with the grade received in that course. 

While not illustrated here, other domain functions exist, such as DSum. One caution – the domain functions are processed very slowly in Access, and may not be useful for larger files. The domain functions work most efficiently when reading from a table (as opposed to a query). If you are going to use the output from the domain function in a subsequent query, it is best to output the domain query into a table. I’ve used domain functions for tables of up to about 15,000 records, which takes several minutes to process.

 

 Comments

 
To add a comment, Sign In
Total Comments: 6
 
Dale posted on 1/16/2014 8:36 AM
Great tip! I'm not in Access that often, but this is a query I look forward to trying.
Lisa posted on 1/16/2014 9:16 AM
This looks like a very good time-saver! I use Access regularly but never considered using this kind of query. Like Dale, I will be trying it, too, AND I will be sharing it with my colleagues.
Terry posted on 1/16/2014 9:40 AM
This is a great tip. I've used Access for years but never really explored the domain functions. I can use this tip right away because some of our MIS tables are formatted exactly like this one.
Marlene posted on 1/16/2014 10:29 AM
This is very interesting and thank you for highlighting this function! It looks like this function works very similarly to the restructure commands I use in SPSS right now.
Laura posted on 1/17/2014 8:15 AM
DCOUNT is one of my favorite formulas however, I have only used it in Excel. I am very excited to learn that it works also in Access. I can't wait to put this to use!
Katie posted on 1/23/2014 3:35 PM
Thank you! I am going to share this with all my colleagues who use Access.