How many of you have over 1000 items in your inbox? Or have over 20 unread messages you have no intention of ever reading? There is nothing more annoying in the world of business than people who don't use email efficiently. It's not a toy! Used correctly, email can be one of the most efficient forms of communication AND organization. It can also be the most time consuming and frustrating tools available. This article does a GREAT job of outlining 50 ways to use email more efficiently.
Friday, December 30, 2005
Tuesday, November 15, 2005
Free Microsoft Virtual Labs
SQL Server 2005 and Visual Studio 2005 have launched! To help developers get "ramped up" for the new features, Microsoft is offering a variety of free training opportunities. Most notibly, I think, are the virtual labs, where you can try out the software in a hosted environment - for free! I've done a few of the labs and think there is a ton of value in this type of training.
SQL Server 2005 Virtual Labs
SQL Server 2005 Virtual Labs
Monday, October 17, 2005
Access your files from anywhere
Ever been at work and needed a file from your home computer? Avvenu is a new service that allows you to have access to all your files from any computer with internet access. The only pre-requisite is that your home computer will need to be left on and logged onto the internet whenever you want to have access to your files. Best of all ... it's FREE!!
You might want to also check out another service I posted on a while back ... Logmein - you can access your computer from anywhere ... kind of like PC Anywhere used to work - and it's FREE as well.
You might want to also check out another service I posted on a while back ... Logmein - you can access your computer from anywhere ... kind of like PC Anywhere used to work - and it's FREE as well.
Access Day
If you have ever attended a user group meeting, you know how useful and informative they can be. The Boston area has a user group for Access that meets the third Wednesday of every month. Each meeting provides demonstations, tips & tricks, and troubleshooting for all levels of Access users. I have been contemplating starting something like this at VTEC, but would need to know that the attendance would be consistent to make it worth putting forth the effort in gathering speakers, putting together presentations, and scheduling and organizing the meetings. If this is something that interests you, please drop me an email or post here. Please give me some ideas of topics you'd like to see. Check out Access Day in Boston for some ideas (and useful links/examples).
Thursday, August 04, 2005
Access Add-ins
FMS Inc. has been providing high quality Access, SQL Server, and VB add-ins since 1992 and has recently updated all their tools to support Access 2003. I have personally used all their tools and highly recommend them. While these add-ins certainly aren't cheap, they will save you hours of development and provide much more functionality than the built-in controls.
Check out the tools here.
Check out the tools here.
Recover corrupt Access databases
Tony from aadconsulting.com has created a new freeware utility that helps recover corrupt Access databases. Access Database Mechanic uses the following three methods: compact & repair, decompile, and completely rebuild an .mdb. If you've ever had the sick feeling of trying to open a corrupt database, this tool will definitely be something worth hanging onto.
Tuesday, June 21, 2005
Treo 650
My job entails me being away from the office on a regular basis. I've been using an iPaq as my pda for several years. As technology has advanced over the years, I've been constantly looking for a SINGLE device that I could carry to replace my pda and my cell phone. Most of the reviews I have read on these devices are that they do ONE function (cell or pda) very well and provide the basic functionality of the other. Verizon recently release the Treo 650, so I thought I would give it a shot to see if it would be a useful replacement.
At first, I must admit, I was ready to throw this device out the window. However, after some growing pains and learning the quirks (MANY!!) of the Palm OS, I've decided to stick with it. I don't know that I would call it a SMARTphone but it has become an essential tool. While I much prefer the Pocket PC operating system, the Pocket PCs were MUCH too pricey for me to consider. It certainly isn't perfect, but having always-on access to the internet, email, calendar and contacts (which wirelesssly sync to Outlook - thank to free software from Verizon) has made the 650 invaluable.
At first, I must admit, I was ready to throw this device out the window. However, after some growing pains and learning the quirks (MANY!!) of the Palm OS, I've decided to stick with it. I don't know that I would call it a SMARTphone but it has become an essential tool. While I much prefer the Pocket PC operating system, the Pocket PCs were MUCH too pricey for me to consider. It certainly isn't perfect, but having always-on access to the internet, email, calendar and contacts (which wirelesssly sync to Outlook - thank to free software from Verizon) has made the 650 invaluable.
Choose Function
It's been a while since I've come across a new VBA function that I haven't used before AND find useful. The Choose function has similar functionality to the VLookup function in Excel. If you need to lookup a value but can't justify storing the values in an array, table, or lookup list, then the Choose function might be a good alternative.
Syntax
Choose(index, choice-1[, choice-2, ... [, choice-n]])
I recently used this in a database I was working with a database which tracks driver information for a trucking company. The driver classification was one of the fields in the database (not designed by me). I wanted to hide the classifications in code, so that some relatively savey Access user couldn't alter the 'lookup' field. So, I started to build my own lookup when I found the Choose function. I used it as the control source of one the driver classification text box on a form:
=Choose([class],"Experienced","Veteran","Suspended")
If the [class] had a value of 1, then the text box displayed "Experienced", 2 displayed "Veteran", and 3 displayed "Suspended."
I used the Choose function so that I could hide the three values in code and because I didn't want to add another table to the database to store just three values (which SHOULD never change). One other side note was that I knew that the [class] field was only being displayed on one form, otherwise I would have made my own Public function to display the results of the Choose function.
Syntax
Choose(index, choice-1[, choice-2, ... [, choice-n]])
I recently used this in a database I was working with a database which tracks driver information for a trucking company. The driver classification was one of the fields in the database (not designed by me). I wanted to hide the classifications in code, so that some relatively savey Access user couldn't alter the 'lookup' field. So, I started to build my own lookup when I found the Choose function. I used it as the control source of one the driver classification text box on a form:
=Choose([class],"Experienced","Veteran","Suspended")
If the [class] had a value of 1, then the text box displayed "Experienced", 2 displayed "Veteran", and 3 displayed "Suspended."
I used the Choose function so that I could hide the three values in code and because I didn't want to add another table to the database to store just three values (which SHOULD never change). One other side note was that I knew that the [class] field was only being displayed on one form, otherwise I would have made my own Public function to display the results of the Choose function.
Monday, April 18, 2005
Putting some ZIP back into Windows
There are several services that start automatically with Windows XP. Many of these services are never used and should be disabled to gain performance. Windows IT Pro has a great article that outlines the most common services that can be disabled.
Unneeded Services in Windows XP
Unneeded Services in Windows XP
Wednesday, March 09, 2005
Stumbleupon: Find interesting websites
Mozilla Update :: Extensions -- More Info: stumbleupon
I'm always looking for cool new websites that I can waste my time reading. The problem is, how do you find new sites? Unless one of your friends tells you about it or you read an article from another site about it.
Using one of Firefox's newest extensions I have found a very simple way of broadening my surfing world. Stumbleupon asks you a few questions about the types of sites you like to read, then randomly takes you to sites that match your categories. A rating system has been implemented to make more popular sites appear first. You can even mark sites you like and Stumbleupon will remember your favorites. Check it out!
I'm always looking for cool new websites that I can waste my time reading. The problem is, how do you find new sites? Unless one of your friends tells you about it or you read an article from another site about it.
Using one of Firefox's newest extensions I have found a very simple way of broadening my surfing world. Stumbleupon asks you a few questions about the types of sites you like to read, then randomly takes you to sites that match your categories. A rating system has been implemented to make more popular sites appear first. You can even mark sites you like and Stumbleupon will remember your favorites. Check it out!
Monday, February 28, 2005
Basing one combo box on another
This technique is very useful in applications where you want to allow your users to choose multiple data from combo boxes - when you want one combo box to display records based on what the user selected in the first combo box.
Microsoft has put together a very small sample database of how this can be accomplished.
Microsoft has put together a very small sample database of how this can be accomplished.
Friday, February 25, 2005
WinZip - Outlook Companion
Winzip has long been the standard tool for compressing files and folders. With Outlook Companion, Winzip is offering tighter integration with Outlook.
From Winzip's website: Zipping attachments saves transmission time, conserves space in Outlook mailboxes, and saves disk space for both the sender and recipient of the e-mail. The Companion can be configured to automatically zip your attachments, ask whether or not you want them zipped, or let you zip and attach files manually with just a few mouse clicks. Toolbar and menu items allow you to control the Companion's functionality on a message-by-message basis. In addition, sensitive attachments can be easily protected with the same built-in advanced AES encryption found in WinZip 9.0
Benefits
WinZip - Companion Product Information
From Winzip's website: Zipping attachments saves transmission time, conserves space in Outlook mailboxes, and saves disk space for both the sender and recipient of the e-mail. The Companion can be configured to automatically zip your attachments, ask whether or not you want them zipped, or let you zip and attach files manually with just a few mouse clicks. Toolbar and menu items allow you to control the Companion's functionality on a message-by-message basis. In addition, sensitive attachments can be easily protected with the same built-in advanced AES encryption found in WinZip 9.0
Benefits
- Easily zip attachments when sending Outlook e-mail messages
- Conserve space in your Outlook mailbox
- Save disk space
- Save transmission time
- Protect sensitive attachments with encryption
- Easy-to-use; just install it and it is ready to work for you
WinZip - Companion Product Information
Problem with record selection and left joins
Many report writers begin to explore table joins in Crystal Reports when they aren't getting the results they expect. While changing a join from an inner to an outer is sometimes the easy solution, it may not always yield the results you expect. Imagine you want all your customers in a report, whether they had transactions or not. Where they do have transactions, you are only interested in a selection of transactions
To illustrate with a simple example, let's look at a customer table and a transaction table of orders.
This one-to-many table relationship is common to many reports. This is a problem we have seen on many occasions. There are several solutions you might like to consider if your report has this problem. The default Inner Join will give us only those customers who have orders. To get ALL customers and their orders if they have any, you would use a Left Outer join. Under normal circumstances, the Order table fields will be NULL for those customers without any orders. And that is the foundation of the problem we are about to describe.
Usually, you don't want all the records in the transaction table. At a client site recently, we only wanted the transactions from a selected date range. But we still wanted to see all the customers on the reports. Because we are doing a selection on the date field in the orders table, we restricted our customer selection to only those customers who had orders in the selected dates. You might suggest that those customers without any orders will have NULL in the fields in the order table, so select the NULL values as well. Sure, that will pick up customers who have NEVER had any orders. But customers who have orders, but not in the date selection are still missing from the report. This is because the LEFT join is done in the database prior to record selection. We need a solution that shows all customers, and their orders in the date selection (if they have any). There are several solutions to this. Thanks to Tim Wilson for his help investigating these.
Brute Force Solution
Use a formula on all records After examining and trying some of the other methods, this was the solution we implemented. We expected a performance hit, but the report was fast and gave us the results we wanted.
As faster and faster processors are delivered it is time we used some of that extra power for more productive purposes than 3D helicopter games. We joined the two tables with a left join, but with no selection from the orders table.
Yes, all the transactions were loaded into the report and had to be processed. We calculated our “This Month” total using a summary of an If .. Then .. Else formula.
A Running Total Field would also work fine here. You might try and avoid a report that loads millions of records into one report. But Crystal can handle the volume without any problems. This solution performed well and worked with our CR 8.5 user.
Stored Procedure Solution
We looked at the previous problem from the database side. If Crystal wasn't part of the solution, how would we do this using SQL? The raw data tables won't do what we require, so we need an SQL stored procedure.
* We looked into using SQL to create the left join we needed, and it just didn't happen. If any of you SQL experts out there have a pure SQL solution for this, then let us know.
* If only summary data are required, a sub SELECT would work. But if you need the transactions as well, it is not enough.
* A UNION join also looked like it might provide a solution. With a stored procedure we could select the required transactions from the orders table. Then we could use Crystal Reports 9 or 10 to left join the stored procedure to the master table. But this solution is not available for CR8.5 or earlier and has a big performance hit.
Business View Solution
Business Views are a new feature in Crystal Reports 10. We created a SQL command with the date selection parameters inside a Business Foundation. Left join that to the customer table, and create the required Business Elements and Business Views.
This worked fine in our Crystal Reports 10 test report.
SELECT INTO Solution
One stored procedure solution we thought would work was to use a stored procedure with several steps. The first step did a SELECT INTO to create a temporary table with the only the records we needed, and then it left joined the temporary table to the master table. This would work with any version of Crystal Reports.
The negative side of this solution is that the report can only be run by one user at a time. A second user would reset the temporary table while the first was still using it.
Subreport Solution
Back into Crystal Reports with a main report from the master table, and a subreport for each transaction record to get the data we required. This did work correctly.
But the performance and selection of the transactions for each master record made this the slowest performing solution of any we implemented. Also, you could not do any grouping or primary record selection on fields in the transaction table.
Conclusion
Use the solution that best suits your needs. Each of the above has specific technology and performance limits. But we now have several ways to get that report with the data we want on it.
This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.
To illustrate with a simple example, let's look at a customer table and a transaction table of orders.
This one-to-many table relationship is common to many reports. This is a problem we have seen on many occasions. There are several solutions you might like to consider if your report has this problem. The default Inner Join will give us only those customers who have orders. To get ALL customers and their orders if they have any, you would use a Left Outer join. Under normal circumstances, the Order table fields will be NULL for those customers without any orders. And that is the foundation of the problem we are about to describe.
Usually, you don't want all the records in the transaction table. At a client site recently, we only wanted the transactions from a selected date range. But we still wanted to see all the customers on the reports. Because we are doing a selection on the date field in the orders table, we restricted our customer selection to only those customers who had orders in the selected dates. You might suggest that those customers without any orders will have NULL in the fields in the order table, so select the NULL values as well. Sure, that will pick up customers who have NEVER had any orders. But customers who have orders, but not in the date selection are still missing from the report. This is because the LEFT join is done in the database prior to record selection. We need a solution that shows all customers, and their orders in the date selection (if they have any). There are several solutions to this. Thanks to Tim Wilson for his help investigating these.
Brute Force Solution
Use a formula on all records After examining and trying some of the other methods, this was the solution we implemented. We expected a performance hit, but the report was fast and gave us the results we wanted.
As faster and faster processors are delivered it is time we used some of that extra power for more productive purposes than 3D helicopter games. We joined the two tables with a left join, but with no selection from the orders table.
Yes, all the transactions were loaded into the report and had to be processed. We calculated our “This Month” total using a summary of an If .. Then .. Else formula.
A Running Total Field would also work fine here. You might try and avoid a report that loads millions of records into one report. But Crystal can handle the volume without any problems. This solution performed well and worked with our CR 8.5 user.
Stored Procedure Solution
We looked at the previous problem from the database side. If Crystal wasn't part of the solution, how would we do this using SQL? The raw data tables won't do what we require, so we need an SQL stored procedure.
* We looked into using SQL to create the left join we needed, and it just didn't happen. If any of you SQL experts out there have a pure SQL solution for this, then let us know.
* If only summary data are required, a sub SELECT would work. But if you need the transactions as well, it is not enough.
* A UNION join also looked like it might provide a solution. With a stored procedure we could select the required transactions from the orders table. Then we could use Crystal Reports 9 or 10 to left join the stored procedure to the master table. But this solution is not available for CR8.5 or earlier and has a big performance hit.
Business View Solution
Business Views are a new feature in Crystal Reports 10. We created a SQL command with the date selection parameters inside a Business Foundation. Left join that to the customer table, and create the required Business Elements and Business Views.
This worked fine in our Crystal Reports 10 test report.
SELECT INTO Solution
One stored procedure solution we thought would work was to use a stored procedure with several steps. The first step did a SELECT INTO to create a temporary table with the only the records we needed, and then it left joined the temporary table to the master table. This would work with any version of Crystal Reports.
The negative side of this solution is that the report can only be run by one user at a time. A second user would reset the temporary table while the first was still using it.
Subreport Solution
Back into Crystal Reports with a main report from the master table, and a subreport for each transaction record to get the data we required. This did work correctly.
But the performance and selection of the transactions for each master record made this the slowest performing solution of any we implemented. Also, you could not do any grouping or primary record selection on fields in the transaction table.
Conclusion
Use the solution that best suits your needs. Each of the above has specific technology and performance limits. But we now have several ways to get that report with the data we want on it.
This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.
Thursday, February 24, 2005
Retrieving random records with a query
I've had several students in the past ask about getting random results from a query, so they could review/analyze random data from there tables. For instance, one student was working with a customer service database - tracking calls and solutions provided to customers. Once a week, one person was in charge of calling random customers to get feedback on how the customer service was handled and if the customer was satisfied with the service. The employee would open the database and manually look for random customers to call. This solution provides an easy way to build a query - which could then be formatted with a report - to automatically build a list of any number of customers randomly.
Type the following expression into an empty column in the field row of the query design view:
RandomValue: Rnd([field])
where [field] is any field in the table that contains numeric values - the field can be an AutoNumber, Number, or Date/Time field. Apply a sort order to the RandomValue field, ascending or descending. Use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want a number or percentage that isn't listed, simply enter the desired amount in the Top Values text box. When you run the query, you'll find that Access returns a random selection from the underlying record source.
Type the following expression into an empty column in the field row of the query design view:
RandomValue: Rnd([field])
where [field] is any field in the table that contains numeric values - the field can be an AutoNumber, Number, or Date/Time field. Apply a sort order to the RandomValue field, ascending or descending. Use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want a number or percentage that isn't listed, simply enter the desired amount in the Top Values text box. When you run the query, you'll find that Access returns a random selection from the underlying record source.
Wednesday, February 23, 2005
Outlook reminders sent to your mobile phone
I'm always forgetting stuff. Meetings, appointments, tasks, etc. I forget them all. I don't want to look like batman with a utility belt so instead of carrying a cell phone and pda, I wish Outlook could send a text message to my phone as a reminder if I'm away from my desk. This great little utility - although it's not free - makes a great add-on for Outlook.
OutlookSMS
OutlookSMS
Thursday, February 17, 2005
Add bullets to Access reports
Bullets are useful to draw the reader's attention to a particular item, displaying lists, or even separating address parts in the footer of a report (similar to most company letter head). You can add a bullet to a report in Access.
Open your report in design view.
Turn on the Number Lock.
Insert a label and type the text you want in the Caption property. When you want to insert a bullet:
Hold the alt key down and--
For a round bullet, type:
0149
For a hyphen bullet, type:
0150
Release the alt key.
Open your report in design view.
Turn on the Number Lock.
Insert a label and type the text you want in the Caption property. When you want to insert a bullet:
Hold the alt key down and--
For a round bullet, type:
0149
For a hyphen bullet, type:
0150
Release the alt key.
Wednesday, February 16, 2005
Crystal Reports XI Released
Business Objects has just released the next version of Crystal Reports 11 and Crystal Enterprise 11. Unlike the upgrade to version 10, the upgrade to version 11 has some significant improvements for report designers. Here are the ones I feel are most significant (follow this link to see all new features):
Dynamic Parameters:
Crystal parameters can finally generate the list of default values at runtime. The 'pick list' is no longer static.
Crystal Reports allows you to generate the 'pick list' directly from your data. You can specify a field in the report and Crystal will query the database at runtime for current values. You can even create a command (SQL statement) to generate the list.
Cascading Parameters:
You can now have one parameter prompt determine the list in another prompt. For instance, when trying to select an employee you could first select a department. Then Crystal would show you a list that only included employees from that department.
I think Business Objects was finally feeling the pressure from Microsoft's SQL Server Reporting Services. I expect this new rivalry to really benefit developers as the business intelligence tools gain more and more features.
Dynamic Parameters:
Crystal parameters can finally generate the list of default values at runtime. The 'pick list' is no longer static.
Crystal Reports allows you to generate the 'pick list' directly from your data. You can specify a field in the report and Crystal will query the database at runtime for current values. You can even create a command (SQL statement) to generate the list.
Cascading Parameters:
You can now have one parameter prompt determine the list in another prompt. For instance, when trying to select an employee you could first select a department. Then Crystal would show you a list that only included employees from that department.
I think Business Objects was finally feeling the pressure from Microsoft's SQL Server Reporting Services. I expect this new rivalry to really benefit developers as the business intelligence tools gain more and more features.
Microsoft Access Limitations
Many times in class students will ask when they should upgrade to a server product like SQL Server for their databases. Access does have limitations; this list can be useful when deciding whether to use Access or some other database.
Access table limitations:
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255
General Access limitations:
Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255
Access table limitations:
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface;
1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255
General Access limitations:
Microsoft Access database (.mdb) file size 2 gigabytes. However, because your database can include linked tables in other files, its total size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255
Friday, January 07, 2005
Technology News: Security: Microsoft Beta Seeks and Destroys Spyware
Technology News: Security: Microsoft Beta Seeks and Destroys Spyware
Looks like we can expect even more software from Microsoft in the near future to be dealing with malicious software. I expect this to be a huge step forward for Microsoft, simply because of the integration with the operating system.
Looks like we can expect even more software from Microsoft in the near future to be dealing with malicious software. I expect this to be a huge step forward for Microsoft, simply because of the integration with the operating system.
Thursday, January 06, 2005
Microsoft SpyWare Beta
Microsoft has entered the SpyWare removal business! If you have been looking for a good source for reliable spyware removal, I would suggest checking this out. I just tried it and was very impressed with the speed, but I haven't really had a chance to compare what it found/didn't find to other leading removal programs.
Microsoft Spyware Beta
Microsoft Spyware Beta
Subscribe to:
Posts (Atom)
Atom Feed (xml)