Create a Parameter Query to accept multiple values

Parameter queries are a quick way to create dynamic queries where the result changes depending on user input. If you are not familiar with parameter queries, you can review my article on Parameter Query Basics.

The following SQL statement is an example of  a parameter query.

SELECT Title, FirstName, LastName, Position
FROM Employees
WHERE Department=[Enter Department]

The above query will prompt the user to enter a department and the result will show all the employees working for that department. So, users are free to enter any department they are interested to see, each time they run the query. But what if the user wants to see all the employees for more than one department?

Problem

Typically, in a non-parameterized query, the SQL statement might look something like this.

SELECT Department, Title, FirstName, LastName, Position
FROM Employees
INNER JOIN Detpartments
ON Employees.DeptID=Departments.DeptID
WHERE Employees.DeptID In(1,3,5)

In the above example,  we switched to using the foreign key (DeptID), which would be a more likely scenario anyway and also to make demonstrating the technique being presented a bit simpler to explain. Also, please note the In() clause in the above example is a shorthand for the following criteria (meaning, it's functionally the same).

WHERE Employees.DeptID=1 OR Employees.DeptID=3 OR Employees.DeptID=5

So, our goal is to prompt the user to enter multiple DeptID using a parameter. One might be tempted to try the following as a query criteria.

WHERE DeptID In([Enter DeptID])

and expecting the user to simply enter 1,3,5 when prompted. Unfortunately, this seemingly straightforward approach will not work because Access will interpret the entire input as a String or Text. So, entering 1,3,5 does not mean 1 or 3 or 5; but rather, a string value of "1,3,5" - and we know there won't be any DeptID of "1,3,5" because DeptID is a numeric field. So, the query will result in an empty set because the intended criteria fails. Of course, the obvious solution is to use the following criteria.

WHERE DeptID=[Enter Dept1] OR DeptID=[Enter Dept2] OR DeptID=[Enter Dept3]

The above approach will work, but the user will be prompted to enter a single DeptID three times, which would be fine but what if the user wants to know more (or less) than three DeptID? As you can see, this approach is not very flexible.

Solution

Instead of using the above approach, we could try either of the following approaches.

WHERE InStr(";" & [Enter DeptID (separated by a semicolon ';')] & ";", ";" & DeptID & ";")>0

or

WHERE ";" & [Enter DeptID (separated by a semicolon ';')] & ";" Like "*;" & DeptID & ";*"

When the user runs the query and gets the prompt, we are expecting the user to enter something like 1;3;5, which in our criteria will be converted into ;1;3;5; (I prefer to use semicolons but using commas would work the same way).

The first approach uses the InStr() function to search the current record's DeptID, encapsulated within a pair of semicolons, within the user's input string from the parameter prompt. If a match is found, the result of the function should be a number greater than zero (0), which would make the criteria evaluate as True and include the current record in the query's result set.

The second approach simply uses the Like operator with a pair of wildcard characters to accomplish the same effect as the first approach. I prefer to use the second approach because it is simpler and avoids a function call, but either approach will work as intended.

Caveat

Unfortunately, this solution is not perfect. The first drawback is you'll have to "train" your users to enter their input using the exact format your query is expecting (e.g. 1,3,5 or 1;3;5 or 1 OR 3 OR 5). Otherwise, the query will not work if the user enters their input using any other format (e.g. user enters 1,3,5 but you expect 1;3;5). Adding the expected format in the parameter prompt, as in the example above, should help a little bit in reminding the user what format is expected.

The other drawback I would like to point out affects any parameter query, in general. As I mentioned in my article on Parameter Query Basics, there is really no way to validate the user's input when using a parameter query. Your query might be asking users to enter a DeptID in the parameter prompt but nothing will stop them from making a mistake and end up entering a DeptName instead. For this reason, it is considered "best practice" to use an unbound form to accept user input for your query parameters. Using a form will allow you more control over the user's input. You can easily validate their input when you use a form.

The last point I'll mention, if you do end up using parameters in your query, is to make sure you declare the data type for each parameter. This is more applicable when each parameter is a single value. To declare the parameter's data type, you can click on the Parameters button on the Design Ribbon to open the Query Parameters window.

To declare the parameter data types manually, you can add the PARAMETER clause at the beginning of your query's SQL statement. For example:

PARAMETERS [Enter DeptID] Long, [Enter Department] Text ( 255 );
SELECT ...
FROM ...
etc...

Please note, it is very important to end the PARAMETER clause with a semicolon, or you will get a syntax error. Also, the PARAMETER clause is typically required in Crosstab queries.

Conclusion

Parameter queries allow us to create semi-dynamic queries, which could help reduce the number of queries we need to create if all we need to change is the criteria condition of the query, based on user input. It is even possible to create a parameter query that accepts multiple values from the user. However, parameter queries have some limitations that it is highly recommended to use input forms whenever possible. Still, parameter queries are good as quick solutions for those times when the requirement is simple enough with very low chance of user input errors.

Normalization

This month, I decided to go back to basics. I know there are already plenty of articles regarding this subject. But since I am about to give a lecture about this topic to a small group of people this week, I thought I might as well post it here, so I can use it as a quick reference in the future.

What is Normalization?

Normalization is the process of designing a database table structure that organizes the data to minimize data redundancy and facilitate data integrity. The main objective of "normalizing" the data is to isolate the information so modifications to the data can be performed in just one table and then propagate throughout the database by means of related foreign keys.
There are defined "rules" to follow when going through the normalization process. When a database is properly normalized, it is considered to be in "normal form." For example, a database complying with the first rule is said to be in "First Normal Form (1NF)." Although there are several levels or rules for normalizing a database, most developers will attempt to comply only up to the "Third Normal Form (3NF)." 3NF is considered to be the highest level necessary for most database applications in use today.

First Normal Form (1NF)

A database must comply with the following rules to be considered 1NF:

1. Eliminate repeating groups in individual tables
2. Create a separate table for each set of related data
3. Identify each set of related data with a primary key

Example: The following table violates 1NF.

Second Normal Form (2NF)

A database must comply with the following rules to be considered 2NF:

1. All the rules for 1NF
2. Create separate tables for sets of values that apply to multiple records (duplicates)
3. Relate these tables with a foreign key

Example: The following table is in 1NF but violates 2NF:

Third Normal Form (3NF)

A database must comply with the following rules to be considered 3NF:

1. All the rules for 1NF and 2NF
2. Eliminate fields that do not depend on the key

Example: The following table is in 2NF but violates 3NF:

How can I remember all that?

Here are the main ideas to help you remember each normal form:

1. 1NF - Eliminate repeating groups (e.g. Price1, Price2, Price3)
2. 2NF - Eliminate duplicate or redundant data (e.g. multiple records with same values)
3. 3NF - Ensure all fields in the table are dependent on the primary key (e.g. city is dependent on the venue and not on the artist)

How do I fix this table structure?

The following tables show a properly normalized structure:

If you can think of a better example, please let me know. Thank you.

Original post date: January 26, 2016

Placeholder Records

I have always recommended against "placehoder" records (with possible exceptions), but someone in the forums recently asked me "why?" So, to explain my reasoning, I have decided to write an article about it.

What is a "placehoder" record?

First, we'll need to define what I consider as placeholder records to help define why I oppose them. To do that, I will try to use some concrete examples of what I consider as placeholder records (other developers may have a different definition of what is a placeholder record).

My definition of placeholder records are records created in advance and stored in the table to represent future records. In other words, these records are created in anticipation of possible records that may or may not be needed in the future. For example, let's say you have a database to track class attendance. If a class will have a total of ten sessions, you might be tempted to create ten records, in advance (at the time of enrollment, for example), per student to make it easier to mark if a student attended each session. They are considered placeholder records because you are creating records to represent future class sessions.

Other examples of potential placeholder records may be found  in booking databases (room and equipment rentals or theater shows to track daily occupancy or usage) or property management databases (apartment or house rentals to track monthly payments).

So, why are they bad?

Well, I probably wouldn't say they are bad but more like unnecessary. Records should only be created when they are needed and not before. For example, if we take the example of the class attendance database, each attendance record should only be created when that particular class session occur. And even then, I would also recommend that you only create records for those in attendance. There's really no need to create records for the absent students, because we can always determine who they are by comparing the list of students from those who attended the class. You can also create a query to show both attendees and absent students as the record source for your form or report.

If you need to produce sequential records for use in a crosstab query or a daily status report, you have the option of using a cartesian product query as presented in an earlier blog article: A practical use of a Cartesian query. That article demonstrates the use of a Tally table, which I also prefer over creating a table of sequential dates.

If you like the convenience of having placeholder records in the table for your data entry forms, that can also be addressed by other methods such as using a temporary table or a multi-select listbox. The user can select all the records to be created from the temporary table or listbox, and then VBA code can be used to actually add those records into the table.

Conclusion

So, to summarize, placeholder records could take up space in the table when it is not actually needed. They don't necessarily violate "normalization" rules, but it's considered best practice to avoid using placeholder records. Hopefully, I have described how the common benefits of creating placeholder records can be easily addressed without actually creating placeholder records.
The next time you hear me tell someone to avoid creating placeholder records, you now know my reasoning behind it. I would like to hear other perspectives on this topic, so please leave a comment to share your thoughts.

Thanks for reading!

Original post date: October 27, 2014

Text or Hyperlink

This is a topic of discussion that I have participated in the forums a number of times in the past and will probably continue to engage in the future. I have decided to write an article to summarize my opinions on this subject. Essentially, the dilemma is whether to use a Text or a Hyperlink field to store a "link" to a file or folder or website, etc.

First of all, let me clarify that I am not exclusively in one camp or the other. Rather, this article just attempts to bring up points and counterpoints regarding the advantages and disadvantages of using one or the other in your database.

Using a Hyperlink Field

When designing a table in Access, using a Hyperlink field may be the easiest choice when trying to store "link" data. For example, let's say you are creating a table for clients and you want to include a field that points to a network folder where you store all the documents relating to each particular client. Using a hyperlink field makes this easy because it comes with built-in functionalities that alleviates the developer from writing code to make it work. For example, by right-clicking on the hyperlink field and selecting Hyperlink > Edit Hyperlink, the user is presented with a dialog box for building the hyperlink. For our scenario, the user can navigate to the folder location and then click 'OK' to store the link to that folder in the table. Once the link is stored in the table, the user can simply click on the field to open a folder window to gain access to all the documents for that client.

Advantages

The main advantage of using a hyperlink field is its ease of use and creation. The hyperlink builder is very intuitive and Access does all the work for you. There's no need to write any code to open the folder window or email client or whatever the program the link requires. It just works!
Another huge advantage of using a hyperlink field is that it can store "any" link. That means you can have one field that can store links to a file or folder path, a website, an email address, an FTP site, etc. Access should reconize the protocol specified in the hyperlink and then execute the appropriate program to open the link. This again would be a big task for the developer to determine the appropriate action to take and program to execute based on the protocol embedded in the link.
The last advantage of using a hyperlink field is that you can "display" a short text in place of the actual link to help the user understand better what the link is pointing to. For example, instead of seeing the full path to the folder such as \\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals, you can simply display something like "Client Proposals." Clicking on the link will automatically take the user to the correct folder path.

Disadvantages

Unfortunately, that last advantage of the hyperlink field can also be considered as a disadvantage by other developers. For anyone not familiar with how hyperlink fields are stored in the table, it actually has three components separated by hash marks: the display text, the hyperlink address, and the hyperlink subaddress. So, in the above example, the hyperlink address is the full folder path, and the display text is "Client Proposals," which is all the users see when looking at the data.
The problem appears when the developer starts to use the value in the hyperlink field in code. For example, if we add an unbound textbox on a form bound to a table with the hyperlink field and use the following as its Control Source: =[HyperlinkFieldName], you will see the actual value stored in the table instead of just the hyperlink address, which is what we would normally want. For the above example, the unbound textbox will display the following value:

Client Proposals#\\ServerName\OfficeLocation\DepartmentNo\ClientName\Proposals#

Notice the hash tags separating the other components of the hyperlink field? If the user sees that, it could cause some confusion. Also, grabbing the hyperlink address using code by just referencing the field's value could cause unexpected results. For this reason, some developers prefer to use a text field instead.

Using a Text Field

As I have mentioned above, a hyperlink field may have many advantages for beginners but most experienced developers will try to avoid the pitfalls and additional maintenance that may be brought on by using it and opt to use a text field instead. A text field doesn't disguise what is stored in the table and it's easy enough to simulate a hyperlink using a text field.

Advantages

I already mentioned the main advantage of using a text field - what you see is what you get. What is being displayed in the field is exactly what is stored in the table, no surprises. Because of that, the developer is then free to manipulate the data using code. The simplest approach to simulate a hyperlink using a text field is by using the following code:

Application.FollowHyperlink HyperlinkFieldName

Disadvantages

This may not matter to seasoned developers but the first disadvantage of using a text field to simulate a hyperlink is the need to use code. As you can see above, it's not really hard to do that. However, if you can remember one of the advantages of a hyperlink field, if there's a situation where the "link" data stores various types of protocols, then the code to execute the hyperlink can become vastly complicated. For example, simply using the FollowHyperlink method when the link only contains the following data will not work because it is missing the proper protocol: thedbguy.blog.com. The developer may have to include additional logic to the code to assign and use the appropriate protocol for the link in question. These protocols might include types such as ftp, news, gopher, etc.

Conclusion and Disclaimer

Understanding that a hyperlink field really contains three components helps the developer get a better handle in manipulating the link data. But, if the purpose of the hyperlink field is just to store websites, email addresses, or file/folder paths, then using a text field will probably save you the frustration of dealing with the surprises that comes with a hyperlink field.

As I mentioned in the beginning, I am not really recommending to use one over the other but merely trying to bring up possible arguments that you may hear when you get into a discussion regarding this topic with others.

Oh, and by the way, if you were wondering how to grab just the hyperlink address from a hyperlink field as if it was a text field, you can try the following code:

Split(PlainText(HyperlinkFieldName),"#")(1)

As usual, any comments are welcome. Cheers!

Original post date: March 28, 2014

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will both update existing data in the table and add new records at the same time.

The Scenario

The member stated that he has a master table of data in Access and receives updated information every month in an Excel file. The Excel file contains the same information as the master table but with user changes to the data and possibly new information as well. The challenge is to update the Access table with the user changes and append the additional information as new records - at the same time.

The Usual Solution

The first step, usually, is to link to the Excel spreadsheet so we can access the data from within Access. We can then create an UPDATE query that joins the master table with the linked table to apply the latest changes to the data. However, this doesn't include the new information from the linked table.
As a second step, we might also create an APPEND query to grab all the new information from the linked table and add them to the master table. We would then need to execute both queries, one at a time, to complete the task of updating the master table.

The Combined Solution

But, since the challenge was to perform both the update and append actions in one query, we had to use a different technique. I can't say that this is a new technique because I'm sure it's been around for a while, and most of you probably already know it. I just thought of writing it down here as a possible reference for me in the future.
So, how do we execute an UPDATE and APPEND query at the same time? Well, we also start out by linking to the Excel spreadsheet to access the data. We then create one query that joins the master table to the linked table. However, in this case, we will use an OUTER JOIN to return all the records from the linked table.
As a result, the database engine will update existing data and append new records at the same time. Here's an example of how the query's SQL statement might look like:

UPDATE tblMaster
RIGHT JOIN tblExcel
ON tblMaster.FieldName=tblExcel.FieldName
SET tblMaster.FieldToUpdateOrAdd=tblExcel.FieldToUpdateOrAdd

Original post date: November 30, 2013

SimpleCSV()

I know there are already plenty of examples out there on how to concatenate records or child records into a delimited string, but I also see this request often enough in the forums that I wanted to have one place to point them to for a possible solution.

The following is just a simple function most users and developers can add to their database to quickly produce a delimited string of records they would like to combine. I purposely made it simple because all the bells and whistles, such as sorting the list or accepting listboxes as arguments, are already available on other websites, so there's really no need for me to reinvent the wheel for those.

To use this function, just pass a "SELECT" SQL statement and an optional delimiter to it. The power of this simple function is really in the creativity of your SQL statement. Please note there are no error handlers, so you'll have to add your own.

As always, I appreciate any comments you may have. Thank you.

Public Function SimpleCSV(strSQL As String, _
   Optional strDelim As String = ",") As String
'Returns a comma delimited string of all the records
' in the SELECT SQL statement
'Source: http://accessmvp.com/thedbguy
'v1.0 - 8/20/2013

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCSV As String

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Concatenate the first (and should be the only one)
' field from the SQL statement
With rs
 Do While Not .EOF
  strCSV = strCSV & strDelim & .Fields(0)
  .MoveNext
 Loop
 .Close
End With

'Remove the leading delimiter and return the result
SimpleCSV = Mid$(strCSV, Len(strDelim) + 1)

Set rs = Nothing
Set db = Nothing

End Function

Original post date: August 21, 2013

What's the opposite of DSum()?

A member recently posted a request in the forums for a function that does the opposite of DSum(). The intent was to have a query with a running total but using subtraction instead of addition. I never heard of anyone needing to do something like this before, so it was no surprise that I couldn't find a built-in function that does the opposite of DSum() or Sum() to offer the member.

I really don't know if anyone would ever need to do this again, but I just thought to share what I came up with in case it may be useful to someone else. The following function is very basic and does not include any error handling. Hopefully, you can modify it to suit your needs. I would happily accept any comments or recommendations to improve this function to better help those who may want to use it.

Public Function DDiff(strField As String, strDomain As String, _
    Optional strCriteria As String) As Variant

'accessmvp.com/thedbguy
'7/26/2013

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim varDiff As Variant

strSQL = "SELECT [" & strField & "] FROM [" & strDomain & "] "

If strCriteria > "" Then
    strSQL = strSQL & " WHERE " & strCriteria
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
    If Not (.BOF And .EOF) Then
        varDiff = .Fields(strField)
        .MoveNext
    End If
    Do While Not .EOF
        varDiff = varDiff - .Fields(strField)
        .MoveNext
    Loop
   .Close
End With

DDiff = varDiff

Set rst = Nothing
Set dbs = Nothing

End Function

To see the discussion that started it all, including a pure SQL solution, click here.

Original post date: July 27, 2013

A practical use of a Cartesian Query

As promised in my previous blog on Cartesian Query, I will now try to describe in detail one example of a practical use of a Cartesian Query.

As luck would have it, while I was preparing the materials for this blog, an UtterAccess member (Kamulegeya) posted a question in the forum that's a perfect fit for this topic. So, we will use his situation as the example for this blog.

In a nutshell, he wanted to create a report that displays the number of days a room was reserved or "booked" for a given month.

For instance, let's say we have the following data:

Room Reservations Table



And we want to have a query that looks like the following:

Dates rooms were occupied



Basically, we want to produce a record for each day that the room was reserved or occupied so we can easily count them. So, how do we do that? Enter, the Cartesian Query.

As we have learned before, a Cartesian Query involves more than one table. So, we'll need another table to join with "tblBookings" to produce the in-between dates for each room reservation.

For this technique, we'll create a table with the following structure:

Table of numbers



Note: Some developers refer to this table as a "Tally" table.

And we just need to populate it with sequential numbers starting with zero up to the largest number required for the result of the query, which in this case is the longest time a room could be reserved, as shown below:

Populate tblNumbers with sequential numbers



Now that we have our two tables, we just need to construct the Cartesian Query. The SQL statement we need looks like this:

SELECT tblBookings.BookingID, tblBookings.RoomID,
          DateAdd("d",[TheNumber],[CheckInDate]) AS DateOccupied
FROM tblBookings, tblNumbers
WHERE (((DateAdd("d",[TheNumber],[CheckInDate]))<[CheckOutDate]))
ORDER BY DateAdd("d",[TheNumber],[CheckInDate]);

If you run that query, you will get the result shown in the second image above. You can now use this query to produce the number of days/nights that the room was reserved for each  month either by using a Totals query or a Crosstab query.

Note: We can also use a table of dates and a Non-Equi Join to produce the same result but it's not nearly as flexible as using a Cartesian Query. In fact, you can re-use tblNumbers in other Cartesian Queries to suit various situations.

To see the actual discussion at UtterAccess on this topic, click here.





Original post date: February 3, 2013

Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query. Usually, we want the query to return only the matching records between the tables, in which case an “inner join” would be used.

Sometimes, the desired result is for the query to return all the records from one table with the matching records from the related table. In these cases, an “outer join” (left or right, Access does not support “full outer join”) would be the join of choice.
However, there will be times when none of these join types will return the desired result. In which case, the only thing left to do is to not use a join at all. That’s when you get a ”Cartesian Query.”

Basically, a Cartesian Query returns a set of data that include all the records from all the tables in the query with a slight difference, the number of records returned will be the cartesian product between the number of records from all the tables.
What exactly does that mean? To better illustrate, take a look at the following sample data.

Table 1

Table 2

Cartesian Query

As you can see, the query returned as many records in Table2 for each record in Table1 (3 x 3 = 9). Here’s the SQL statement used to create that query:

SELECT Table1.Field1, Table1.Field2, Table2.Field1, Table2.Field2
FROM Table1, Table2;

You will notice that there was no relationship join used between Table1 and Table2.

So, why would anyone want to use a Cartesian Query? Believe it or not, there are many valid reasons for using a Cartesian Query. For example, you can use a Cartesian Query to print a shipping label to indicate the box or pallet number within the number of boxes or pallets per shipment, or to display all possible network addresses within an IP range, or to calculate the fractional cost of a product over its life expectancy, or to generate a daily roster for student attendance or employee training or meeting.

One last note to remember when using a Cartesian Query: the resulting data set will not be updateable. So, the result of Cartesian Queries is usually used only as a data source for reports or other queries.

Hopefully, this post helped you become a little bit familiar with Cartesian Queries so that maybe next time when you need to generate records that are not normally stored in your table, you won’t be afraid to try it. Who knows, using a Cartesian Query could provide the solution you were looking for.

In my next post, I will show you one practical use of a Cartesian Query by generating all the calendar dates within a user defined range. See you next time…

Original post date: January 20, 2013