2003 to 2007 Structured References
Working with formulas and functions has not always been the most intuitive process. In Microsoft Office Excel 2003, multiplying gross revenue by the profit margin might mean typing something like =B7*E14. In Microsoft Office Excel 2007, structured referencing enables you to use the column headers in table data to build your formulas. Instead of cryptic cell locations, you can simply multiply =[Gross Revenue]*[Margin].
To use structured referencing, you first need to format your data as a table.
1. Click Format as Table in the Styles section of the Home tab.
2. Select the My table has headers check box.
3. After you have a table, click the cell where you want to insert the formula, and then type the left bracket ([).
4. Select the column that you want to use from the list that appears, press the TAB key, and then close it with the right bracket (]).
5. Repeat as you build out your formula.
Access 2007 allows you to link to Outlook 2007 and use simple e-mail functionality to automatically update your Access database. There are numerous uses for this feature, but one immediate benefit that springs to mind is staff surveys. These can be tedious at times and this can be especially the case for the HR or Admin person who may have to manually collate the replies.
To use this easy wizard driven function you will need to have e:mail addresses within your Access table as shown below;
To get started you must open the ‘External Data’ option in Access 2007, then select ‘Collect Data’ followed by ‘Create e-mail.’
As long as you have e-mail addresses in your Access table a ‘Collect Data Through e-mail Messages’ wizard will open.
Depending on your requirements you only have a maximum of six steps to work through in this wizard.
You need to decide whether to just update existing records (that’s probably your choice for our survey example) or add new records. Of course adding new records may or may not be desirable depending on what data your table contains.
You have a choice with the data entry form itself, it can either be a HTML form or an InfoPath 2007 form. Don’t let this choice phase you, the whole process in wizard driven whichever way you go. InfoPath probably makes data entry slightly more user friendly but if your users do not have InfoPath then go with the HTML route.
Once you have defined which fields on the form should be filled in you then need to decide whether to totally automate the Access update (when your users reply to the e:mail) or allow yourself the luxury of manually updating your database when the replies come in. This manual route is still much quicker than re-keying the data and allows you control of when to update your database and whether to accept all replies. A number of other e:mail related choices are presented to you also such as whether to accept replies from people that you did not directly send the survey to i.e. if one of your recipients forwarded your survey to somebody not on your list.
You also get to customise the e-mail message as below;
You will also need to specify an Outlook 2007 folder to be used to capture all the replies that you receive.
Once you complete the final wizard steps your users will receive their e-mails along with the embedded reply form. When they reply your data collation work will essentially be done for you, an easy and speedy process all round, with a clear business benefit.
There are numerous other uses for this functionality, perhaps HR contact details, event management, status reports etc – essentially any time consuming task that involves somebody collating data responses from a group of people.
Please log in if you would like to leave a comment on this article
