Chapter 14 - Macros

Here's the first step of my simple Access macro. I was trying to open my Inception to Date (ITD) table and then sort by first org, then fund, and finally by account group. These actions are relatively simple to perform in datasheet view by selecting the first column heading, holding down the shift key and then moving the cursor to the second and third columns. While continuing to hold down the shift key you just click the down arrow on one of the selected columns and choose sort smallest to largest.

Here's the second step of the macro. The table opens just fine.

In the third and final step I was not able to figure out which Access macro command selects columns to sort. I was only able to sort by the first column using the macro.

There doesn't appear to be a macro recorder in Access like the macro recorder in Excel. Here's what the macro looks like in Visual Basic using the macro recorder in Excel.

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 11/25/2007 by University of Alaska
'
' Keyboard Shortcut: Ctrl+h
'
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

The macro I'd really like to create would take the VistaPlus inception to date report, add two columns on the left, copy down each of the grant numbers in column A, column down each of the fund numbers in column B, sort by org number in column C, delete any row without an org number, then sort by grant, fund, org and account code. The macro would then insert a column to the right of account codes, look up and return the account group, then delete the account codes. The inception to date data would then be ready to be used for the Vista Plus ITD Report in my Chapter 14 database.

Changing Data in Access Reports

Once you've spent all this time to get your report formats just right, you'll want to be able to use the report more than once for different sets of data at different times/dates. This is very important for my use of Access.

Here's what I found about changing the data used in a report through Access Help.

Change the record source of the report

First open your report in Design view.
Select the whole report by clicking on the uppermost lefthand box (just to the left of the ruler).
If the property sheet is not displayed, press F4 to display it. (Or you can right click the mouse and choose properties.)
In the drop-down list at the top of the property sheet, click Report.
In the property sheet, click the Data tab.
In the Record Source drop-down list, select the table or query that you want to use for a record source, or click to display the Query Builder.
When you go back to report view you'll see the new data in your report.

Chapter 13 - Avoiding Harmful Attacks

I’m fortunate to be working at the University of Alaska. Although my computer data is shared with many other users, I am fairly well protected from harmful attacks on my computer. We know all too well what devastating effects a worm or virus can have and how quickly it can spread.

My individual computer has multiple firewalls which block worms and viruses. My privacy setting for internet options was so high I was unable to post anything to Blogger when I first started this class. I also always have pop-up blocker turned on, which sometimes gets in the way of performing certain tasks of my job.

I never open any e-mails from people I do not personally know. If I accidentally open one of these e-mails by accident I immediately delete it, without opening any attachments.

UAF has software called Pointproof that places suspicious looking e-mails in quarantine for 14 days after which time they are automatically deleted. You wouldn’t believe the amount of absolute junk and porn this software detects, 172 for yesterday alone!

We also have a program called Spybot Search & Destroy which automatically scans my computer system every day for spyware and all other threats.

At UAF we often get warnings about phishing scams from our Procurement Office. There are so many employees here we often get reports and advice about specific phishing scams which have affected one of us. I never give any personal information to any e-mail request.

I also have access to a great OIT (Office of Information Technology) Support Center and on-site computer techs for my specific college if I ever have any problems or suspect that I’m in harm’s way.

Chapter 12 - Creating A Switchboard

Here's the switchboard I created for people in my department who don't know how to use Access very well (even less than me). Hopefully, they will be able to open the Inception to Date database and immediately choose which report they want to print. I didn't see instructions in the text for having the switchboard run automatically when the database is opened.

Chapter 11 - Importing and Exporting

I would like to use Access for my department’s inception to date reports because Access, unlike Excel, has so many wonderful reporting features. One would think this would be a fairly simple process, right?

The best source of my inception to date data is an SQL based program called QMENU. When I save the QMENU files as text documents I am not able to import them into Access because there doesn’t seem to be a set delimiter to the data. I am also not able to import them into Excel as text files. When I save the QMENU files as html documents I am able to import them into Excel, but not into Access. Access shows the html file as 11 different tables, none of which has the complete data I need. So I seem to be stuck with using Excel to manipulate the QMENU data.

Another of the obstacles I've encountered when trying to produce inception to date reports in Access is in the sub-totaling of data. I need three levels of sub-totaling for these reports, at the account group org and fund levels. The Microsoft Office pivot tables normally work extremely well for sub-totaling on multiple levels. However, because the pivot table command doesn't work well in Access I need to create pivot tables in Excel. Unfortunately, these Excel pivot tables do not export into Access well. The column headings which are text in Excel, i.e. Sum of BUDGET, show up as error messages when I try to create an Access link to the Excel pivot tables. So I wind up spending more time manipulating the data in Excel so that I can just copy it into Access.

This would be very time-consuming but an okay solution if I were just creating a one-time report. However, I was hoping to be able to somehow have my Access report link to the Excel/VistaPlus/QMENU data which changes weekly.

I was able to export the Access data back into Excel and Word successfully once I remembered to save the Access 2007 databases as Access 2003, because that's the version of the Excel and Word software I have on my computer now.

Chapter 10 - Working on the Web

WEB DATABASE INTEGRATION

Tim Berners-Lee invented the WorldWideWeb (www) in 1991. He was working on a project for a physics laboratory called CERN in Switzerland and recognized the need for collaboration between software engineers to produce common software with the use of shared hypertext. Now people can access a web or internet which links millions of data pages, websites, chat rooms, and search engines. You name it, it can be found on the web.

As a relative newcomer I can immediately see the advantage of integrating one’s database with the web. Billions of people can access your information if you and they so desire.

In addition, the ability to access this information is split-second. Users don’t have to write a letter or wait for a phone call to be connected and then have to re-directed to the appropriate party. We can communicate with the website online and get answers immediately. This enhanced communication also improves the quality of the information available on the web.

Another obvious advantage to having a web integrated database is the ability to communicate updates and changes as soon as they are made. This is essential for a database that changes frequently.

The less obvious advantage to me was the outcome of my ability to interact with this data. I knew I could buy things on the web. From a different perspective, I now see that my interaction with the data, even if it’s only e-commerce, increases the database, or wealth of knowledge available. Just from a single transaction, the website owner can get information from me about product preferences and target market locations. This information can be used to develop new and different products which in turn increase the amount of data available.

I can share ideas, brainstorm and collaborate with anyone on the net. As stated by Berners-Lee in a Business Week article dated October 22, 2004, “The original goal was that the Web should be a sort of play space.” By sharing ideas or data people can create new ways of thinking, new processes or products. “The challenge is to make the innovative power of a group greater than the power of one person.” Even in a small Microsoft Access class way out in the wilds of Alaska sharing information helps us all.

In conclusion, integrating a database with the web improves:
1) Access to the data;
2) Speed of access;
3) Communication of data changes;
4) The quality of the data, and;
5) The quantity of the data itself.

Chapter 9 Report Again

Hey! I forgot to tell you. I was finally able to get the line items in the order I needed them in the report. I used Sage's suggestion for creating a numbered account group and sorted the data by account group without including the account group in the report. It worked like a charm. Thanks, Sage!

Chapter 9 - Reports

OK You Guys!

Here's my latest Inception to Date Report in Print Preview. I like the way the report separates the information by org number. That way I can give the relevant information to each principal investigator for all of their own grants. I also like the way the fund totals and org totals are outlined in red. That way the totals are hard to miss.

Here's the chart I made in Excel comparing budgeted line items to actual expenditures for the Research Institute as a whole. I probably won't ever use this chart in this particular application but it could be useful in a summary report to the research institute director or college dean.

I haven't include a form because I will never be entering this data. I will always be downloading the data from another source, in this case VistaPlus.