This article will highlight my absolute favorite family of function in MS Access - the D functions. This includes DLookUp, DSum, DMax, etc. They are widely applicable and allow data researchers to pull in a specific piece of data without the use of extraneous subqueries and complicated joins.
The D functions are meant to add a dynamic level of data reference to Access objects. In a report, the DSum function can pull in a running sum. In a fillable form, DLookUp can bring in a cost value based on categories selected elsewhere in the form. In a query, DMax can help you in assigning an incrementing auto-number value.
It its simplest form, a D function looks something like:
DSum("[Field]","[Table]","[CriteriaField] = "&[Criteria])
The difficulty with the D functions is the syntax of the 3rd argument. It's widely versatile in that it can allow as many criteria as you can fit into the expression, but correctly typing a DSum function to filter for (1) a single customer with (2) a date prior to today and (3) a specific range of transaction types can prove difficult.
The first thing you need to know is the difference between string fields and number fields in your criteria. String fields need to be quoted. To accomplish this, the third argument needs quotes around the criteria AFTER the 2 parts are concatenated together.
Number filter:
"[CriteriaField] = "&[Criteria]
String filter:
"[CriteriaField] = '"&[Criteria]&"'"
In the string filter, you have to include a single quote within the double quotes, so that the system reads the filter something like this after reading your expression:
You see:
"[Customer] = '"&[Customer]&"'"
System reads:
[Customer] = 'Amazon'
Date values are best wrapped in #'s, so your 3rd argument may look like:
You see:
"[CriteriaField] <= #" & [Criteria] & "#"
System reads:
[CriteriaField] <= #1/1/2013#
As you build more complex D functions, you may find use in applying multiple criteria to your filter. Finding the perfect syntax here may take some finesse. I find it most helpful to start with a small data set in a form, and use the Filter property to build your syntax. To get from there to here, you just have to use & to sub in your field name, and put quotes in all the right places.
The most unique application I've found for the D functions is a running sum. Running sums are simple in Excel, since you can clearly reference cells by their row, but the same isn't true in Access. The D functions give the analyst a method for adding, averaging, or counting a set of values by a specific sort order. Here's a clean way to get your expression right on the first try:
1) Identify your filter fields. Do you want to sort by a date field, or an ID field? Do you want to sum all customers, or each customer individually? I'll use:
[TransDate]
[CustID]
2) List out your criteria for each field. Customer IDs may be easy. For your sorting field (usually a date), you have to decide whether you want an all-time sum, or just the current month, calendar year, or fiscal year. I'll use:
[TransDate] >= Jan 1 of Year([TransDate])
*[TransDate] <= [TransDate]
[CustID] = [CustID]
*(This is where the running sum comes into play. The DSum function calculates each row based on criteria found in the current row, so that the function will some applicable values UP TO the current value.)
3) Turn each of your criteria values in to individual D function filters. You can write and test each filter individually to keep from hunting down errors later. Mine turn into:
"[TransDate] >= #1/1/"&Year([TransDate])&"#"
"[TransDate] <= #"&[TransDate]&"#"
"[CustID] = "&[CustID]
4) Throw them together. Use the format (criteria 1) And (criteria 2) And (criteria 3). Note, this does not need the outer parentheses sometimes used in form filters. My final criteria argument is:
"([TransDate] >= #1/1/"&Year([TransDate])&"#) And ([TransDate] <= #"&[TransDate]&"#) And ([CustID] = "&[CustID]&")"
5) Choose your D function, and plug in your first 2 arguments. The first argument is the field name, the second is the table or query name where the function is looking for both the calculated field and the filter criteria. Note, you can use a query as the source of your data, but keep in mind the D function may requery the source for each line it calculates, so it should only come from a simple query. My final DSum function looks like:
DSum("[TotCost]","[OrdersTbl]","([TransDate] >= #1/1/"&Year([TransDate])&"#) And
([TransDate] <= #"&[TransDate]&"#) And ([CustID] =
"&[CustID]&")")
You may also hear about the custom T functions, which are an attempt to improve the speed of D functions. Try at your own risk, but this is a custom code that you will have to input into each database you manage. I'll discuss custom VBA codes another time, and once I get some experience with the T functions, I'll put up a review of them. Until then, here is the list of D functions available within Access:
DLookUp
DSum
DMax
DMin
DCount
DAverage
Happy querying,
Tyler
testing
Monday, March 11, 2013
Wednesday, February 13, 2013
VBA code - ConcatRelated by Allen Browne
First of all, Allen Browne is the saving grace of many database techs. Allen Browne seems to live a very quiet life these days, but he has some big database products and Access related books to his name. He is also one of about 70 people to be awarded Microsoft's Most Valuable Professional award in Access. His website, www.allenbrowne.com, has his personal bio as well as plenty of free VBA code to assist Access users. These VBA codes are life savers!
(If you're not familiar with custom functions, they are sets of VBA code saved to a specific database that can be called from anywhere within that database. Skip to the end for a brief how-to on using custom functions)
My first exposure to Allen Browne was through his ConcatRelated function. I had been dealing with a major data transfer to get 2 years of maintenance records from one db to another. The source database was built to be big and fast, and the new database was more focused and nimble. Because of this, the structures were quite different. One of several obstacles I had to overcome was the structure of the "comments" field.
In the source database, the comments were split into smaller records as they were saved. Each record contained no more than 250 characters worth of the comment and had a Sequence ID field to put them back together. This worked great for storage space, but concatenating the comments back together became a problem. The new new db was set up differently, using a Memo field with plenty of space for any comment that would need to be entered, so we had to put the comments back together again.
A major part of this obstacle was that the source db had a frontend program that we couldn't change, or even see. I had no idea what code was being used to bring the records together and no way of finding out, but if you looked at the comment through the program it was always concatenated back together.
First I tried using a set of queries to stack the comments row-wise, instead of column-wise. I gave myself kudos for the creativity, but this is definitely more spreadsheet thinking than database thinking. The process would have required me to output the query as crosstab, export to CSV, get rid of the commas (but only the ones between comment cells, not after the workorder ID), bring it back into excel as comma delimited, and finally get it back into an Access table, all without losing any data from the massive outputs and inputs. It was far from ideal.
Next, I tried to run a set of queries to concatenate each sequence 2 to sequence 1, then each sequence 3 to 1&2, and on until the longest comment (67 entries long) had been fully concatenated. I was starting to worry this was the only way.
Now, this was early on in my database career, so I wasn't yet privy to the vast resources available for database professionals. I hadn't heard of Allen Browne, I hadn't even stumbled on any of several helpful message boards directly focused toward MS Access help. A coworker showed me the ConcatRelated function and saved my project.
This function took a little effort to get into correct syntax (mostly on the third argument), but it was well worth the minutes spent. The ConcatRelated function is used like:
ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
where Argument 1 is the field to be concatenated, Argument 2 is the table where the field is located, and Argument 3 is the grouping or filtering criteria. Argument 4 is optional to specify a sort order (which I used), and Argument 5 is optional to specify what character(s) will be used to separate entries.
After my hours (probably days, actually) wasted trying to make my brute strength method work, I ended up with a simple query to give me the full result I needed and append it to a pre-designed table.
INSERT INTO [Mtr wocomment] ( WORKORDERID, COMMENTS )
SELECT [Mtr wocomment 3].WORKORDERID, ConcatRelated("[COMMENTS]","[Mtr wocomment 3]","[WORKORDERID] = '" & [WORKORDERID] & "'","[SEQID]","") AS COMMENTS
FROM [Mtr wocomment 3]
GROUP BY [Mtr wocomment 3].WORKORDERID;
The ConcatRelated function can be found on Allen Browne's website, at http://allenbrowne.com/func-concat.html.
Happy querying,
Tyler
To save a custom function to your database, go to your Create tab, and under Macro there's a drop-down menu where you can select Module. Any custom functions you find on the web should be ready to copy and paste from right here. You can compile the code from the Debug menu, then save the function using a name other than the name of the function! This is the most common error. If your module name matches your function name, you will get an error when you call the function. You could parse the code to find out what arguments to type into the function, but most will come with instructions. You will always call the function by using FunctionName(argument), with the function name and argument differing by function. Just check your instructions, and they should be able to guide you through the use of the function.
(If you're not familiar with custom functions, they are sets of VBA code saved to a specific database that can be called from anywhere within that database. Skip to the end for a brief how-to on using custom functions)
My first exposure to Allen Browne was through his ConcatRelated function. I had been dealing with a major data transfer to get 2 years of maintenance records from one db to another. The source database was built to be big and fast, and the new database was more focused and nimble. Because of this, the structures were quite different. One of several obstacles I had to overcome was the structure of the "comments" field.
In the source database, the comments were split into smaller records as they were saved. Each record contained no more than 250 characters worth of the comment and had a Sequence ID field to put them back together. This worked great for storage space, but concatenating the comments back together became a problem. The new new db was set up differently, using a Memo field with plenty of space for any comment that would need to be entered, so we had to put the comments back together again.
A major part of this obstacle was that the source db had a frontend program that we couldn't change, or even see. I had no idea what code was being used to bring the records together and no way of finding out, but if you looked at the comment through the program it was always concatenated back together.
First I tried using a set of queries to stack the comments row-wise, instead of column-wise. I gave myself kudos for the creativity, but this is definitely more spreadsheet thinking than database thinking. The process would have required me to output the query as crosstab, export to CSV, get rid of the commas (but only the ones between comment cells, not after the workorder ID), bring it back into excel as comma delimited, and finally get it back into an Access table, all without losing any data from the massive outputs and inputs. It was far from ideal.
Next, I tried to run a set of queries to concatenate each sequence 2 to sequence 1, then each sequence 3 to 1&2, and on until the longest comment (67 entries long) had been fully concatenated. I was starting to worry this was the only way.
Now, this was early on in my database career, so I wasn't yet privy to the vast resources available for database professionals. I hadn't heard of Allen Browne, I hadn't even stumbled on any of several helpful message boards directly focused toward MS Access help. A coworker showed me the ConcatRelated function and saved my project.
This function took a little effort to get into correct syntax (mostly on the third argument), but it was well worth the minutes spent. The ConcatRelated function is used like:
ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
where Argument 1 is the field to be concatenated, Argument 2 is the table where the field is located, and Argument 3 is the grouping or filtering criteria. Argument 4 is optional to specify a sort order (which I used), and Argument 5 is optional to specify what character(s) will be used to separate entries.
After my hours (probably days, actually) wasted trying to make my brute strength method work, I ended up with a simple query to give me the full result I needed and append it to a pre-designed table.
INSERT INTO [Mtr wocomment] ( WORKORDERID, COMMENTS )
SELECT [Mtr wocomment 3].WORKORDERID, ConcatRelated("[COMMENTS]","[Mtr wocomment 3]","[WORKORDERID] = '" & [WORKORDERID] & "'","[SEQID]","") AS COMMENTS
FROM [Mtr wocomment 3]
GROUP BY [Mtr wocomment 3].WORKORDERID;
The ConcatRelated function can be found on Allen Browne's website, at http://allenbrowne.com/func-concat.html.
Happy querying,
Tyler
To save a custom function to your database, go to your Create tab, and under Macro there's a drop-down menu where you can select Module. Any custom functions you find on the web should be ready to copy and paste from right here. You can compile the code from the Debug menu, then save the function using a name other than the name of the function! This is the most common error. If your module name matches your function name, you will get an error when you call the function. You could parse the code to find out what arguments to type into the function, but most will come with instructions. You will always call the function by using FunctionName(argument), with the function name and argument differing by function. Just check your instructions, and they should be able to guide you through the use of the function.
Thursday, February 7, 2013
Intro
My name is Tyler, and I'm an analyst, a database administrator, and a general data and technology specialist for a local water utility company.
As an analyst, I work with fairly small data sets to find trends and patterns. In water utilities, this can mean reviewing break rates on water pipes, customer water usage trends, or department efficiencies. There's no one-size-fits-all tool for analytics, so you'll see me using anything from Excel (most common) to Tableau or r. I'm finding new analytical resources weekly, so I'll share my favorites here.
As a database administrator, I handle a wide range of responsibilities. Some databases I've created from scratch, including table and user interface design. Others I monitor on a SQL server and consult with departments about their data entry techniques. Still others I handle reporting to go along with the analytics noted above. Most often this is done in Access simply for ease-of-access (sorry for the pun) when I don't have the full admin rights of our Tech Services department. I've found some great tools in Access during the last year, and since I'm fairly new to the world of database management, I'll share some of these tools in more basic language here.
Since I'm also one of the data junkies and techies in my department, I get called on for a lot of troubleshooting, laptop setup, and software recommendations. While these aren't the norm, I still find some great tools from time to time that I'll also share here.
Subscribe to be notified of new tools as I find them, or just look for me on your next Google search when you've hit a brick wall.
May your data be clear and concise,
Tyler
PS: Mine Datum is the term used for a point 1,000 meters below sea level, typically used as a rule of thumb for the deepest a mineshaft should run. The implied pun is entirely intended.
As an analyst, I work with fairly small data sets to find trends and patterns. In water utilities, this can mean reviewing break rates on water pipes, customer water usage trends, or department efficiencies. There's no one-size-fits-all tool for analytics, so you'll see me using anything from Excel (most common) to Tableau or r. I'm finding new analytical resources weekly, so I'll share my favorites here.
As a database administrator, I handle a wide range of responsibilities. Some databases I've created from scratch, including table and user interface design. Others I monitor on a SQL server and consult with departments about their data entry techniques. Still others I handle reporting to go along with the analytics noted above. Most often this is done in Access simply for ease-of-access (sorry for the pun) when I don't have the full admin rights of our Tech Services department. I've found some great tools in Access during the last year, and since I'm fairly new to the world of database management, I'll share some of these tools in more basic language here.
Since I'm also one of the data junkies and techies in my department, I get called on for a lot of troubleshooting, laptop setup, and software recommendations. While these aren't the norm, I still find some great tools from time to time that I'll also share here.
Subscribe to be notified of new tools as I find them, or just look for me on your next Google search when you've hit a brick wall.
May your data be clear and concise,
Tyler
PS: Mine Datum is the term used for a point 1,000 meters below sea level, typically used as a rule of thumb for the deepest a mineshaft should run. The implied pun is entirely intended.
Subscribe to:
Comments (Atom)