Final Project Review

I certainly have learned more about Access 2007 and databases in general. I believe I will be using it on the job in the next few months as the main financial reporting tool for my department.

I realize that if I am going to be using Access for reports at work I will need to go over the things I’ve learned in this class in quite a bit more depth and detail. I had a hard time remembering tasks I had completed in prior chapters when it came time to prepare my final project. I’m sure it will come more easily when I use Access regularly.

Chapter 15 - Using Visual Basic in Access

Here's my first Visual Basic Sub Procedure in Access.

Here's what happens when I open the module click on Run Sub/User Form, select the sub procedure I want to run and click run. Obviously my sub procedure opens the G3170 table.

Here's a macro that does the same thing as the sub procedure in my module, only all I have to do is double click it in the Navigation Pane to open the G3170 table.

The macro makes a whole lot more sense to me and is easier to use.

Our text says that modules are a collection/organization of procedures which can be used for a specific purpose. I certainly need a set of related procedures to manipulate my inception to date data for printing out the monthly reports. However, the Access Visual Basic commands seem much less intuitive than the Excel Visual Basic commands I use. In addition, Access doesn't have a macro recorder similar to the one in Excel, which records keystrokes automatically.

I will most likely use Excel to manipulate the inception to date data and then import it into Access to make good use of its excellent report formating capabilities.

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.

Chapter 8 - Reports

Here's my Inception to Date Report. It shows budget, expenditures, encumbrances and remaining budget balance for each grant project for an individual principal investigator. The org number corresponds to an individual principal investigator, so I tried to show all the projects for each PI in one section and on one page. This way the PIs don't get each other's information, which tends to create problems.

It doesn't look like it but it took quite a bit of time to set this up in Access. I still need to show the line items in the following order, but I haven't figured out how to do that.

Personal Services
Travel
Contractual Services
Commodities
Equipment
Student Aid
Miscellaneous

Because the line item field/control has a text data type if I sort and sub-total by line item Access orders the line items alphabetically. Unfortunately this is not acceptable here at UAF. I may have to go all the way back to Excel and do all my sub-totalling before I import it into Access. You guys have any other ideas that might help?

I'd like to get a little more creative with the appearance of this report but I'm dealing with numbers and my users tend to be a bit stodgy with regard to presentation of financial results.

Chapter 7 - Creating Forms

Here's my form. If I were entering data into this grant inception to date cost analysis I'd need to include all of these fields. If I didn't know already which line items to enter for the account codes for each cost I could look it up in the table on the right.

I created the form this way because this is the basic organization of the university's accounting system. Org numbers tell you who is spending the money. Fund numbers tell you the project you're spending it on. Account codes tell you what kind of cost you're incurring. Account codes are organized into basic types of costs called line items. The reports I will be creating in Access will show grant costs by line item.

Before you can spend money you have to have an authorized budget. To spend money you either create an obligation to spend (encumbrance) or get a check cut directly (expenditure). The remaining balance is the amount of the budget left after you spend the money.

Truthfully I will not be entering data into this inception to date database. I will be downloading the data monthly from a query in the unversity relational database called Databrowser.

Talking SQL

Here's the SQL for a financial management report I run in the UAF relational database called Databrowser. I really wish I knew how to write or successfully alter these instructions. I'll be taking a course next week in how to write a "browser" but I'm sure it will be years, if at all, before I'm able to do something like this from scratch.

At one time I was familiar with writing Excel macros which seems similar to SQL in that you create commands telling the sfotware what to do with what specific data.

The Power of Queries

OK, Then:

I tried yesterday to give you print screens of all the queries we were supposed to discuss in our blogs with comments under each one. Today, when I tried to edit yesterday's blog the print screens showed up as the link Photo but when I tried to open them I couldn't get back to my comments. I got kicked out of Blogger. I guess attaching 8 different print screens was just too much!

Parameter Query

I can see how important it might be to select only a small piece of information from a database that is huge. This can be done with either a filter or a query. I guess the benefit of a parameter query is that the next time you use it you can choose any criteria you want within the parameter fields. You don't have to set up a new filter to get different information.

Find Duplicate Query

I wish I had more experience with databases. I'd be better able to appreciate the amount of time these queries might save. if I were trying to look up orders by customers and I had more than one record for one customer I might have the orders in different places and not be able to see all the orders for just one customer. I'd want to find the customer which were duplicated right away.

The thing is I would be more likely to have the same customer under two slightly different spellings, in which case a query for a specific customer value wouldn't show the slightly different spelling. With a filter you might be able to see multiple but slightly different values for the same customer. Make sense?

Find Unmatched Records

I can see the value of this query in the example given at the top of page 149 in our text. If there were products that no customer had purchased obviously I'd want to get rid of them. However, in my application, it's hard for me to imagine a circumstance where I had information for a grant at one point in time but didn't have the same information at another point in time but I guess it could happen.

Bottom line is you can't filter for a value that's not there. You can only try to identify a value that's not in a table by comparing it to another table that has the value.

Create New Table Query

If I need a new table based on a select set of data it's much easier to create a query than to have to manually set up a filter for each field (column).

Delete Query

It's certainly faster to delete multiple values using a delete query than to select the records by hand individually. However, with a filter the records you want to delete aren't gone permanently. A delete query would work better in some situations. Filtering would work better in others.

Add Records Query

I can't think of a reason why I'd want to add more records to my grant database. I certainly wouldn't want any duplication of data. Also, if I want to add records to a table why couldn't I just coppy the records from one table to another?

Update Query

This appears to be similar to the edit find edit replace commands in Excel. It's slick.

Crosstab Query

I can see where this would be useful if you just had two criteria for which you wanted to run a function, one criteria for the horizontal axis and one criteria for the vertical axis. I'd like to see how we can use/summarize/examine several criteria at once in aa Access table, like the Excel pivot table. Is this possible, Sage?

Chapter 5 - Filtering Tables

Here's my Inception to Date Table sorted by org, fund and account code then filtered to show org numbers equal to or less than 3, and account codes equal to or less than 8999.

My principal investigators (org numbers) are employed in different departments. By filtering data by org number I can show all PIs within certain departments. I guess another way to do this would be to add a column or look-up table for department information.

By filtering data by account codes I can show each PI expenditures only (account codes less than or equal to 8999). This is good because typically PI's are not as interested in the revenue reported on their grants (account codes starting at 9000). They are much more interested in finding out whether they have overspent their expenditure budget.

This might be a disadvantage for that one extremely picky PI who really does want to see (and understands) his/her grant revenue.

Chapter 5 - Sorting Tables

Here's my Inception to Date Table sorted first by org number and then by fund number. I sorted this table first by org number because eventually I want to print reports for each principal investigator on my grants. Each principal investigator has his/her own org number. I then sorted the table by fund number because I will need to sub-total the budget, expenditures and encumbrances for each fund when I give the reports to each investigator.

I will eventually need a third sort by account number so that I can look-up the line item by account code and then sub-total the budget, expenditures and encumbrances by line item

Hopefully I will be able to add a column that calculates the remaining budget balance (budget minus expenditures minus encumbrances) once I learn how to do that in this course.

I'm not sure what the disadvantages would be to sorting the table information in this way.

Using Fields - Chapter 4

Boy, this week was tough!

First I couldn't figure out whether to set up the fields before I entered data or after. When I set up the fields before I entered data it didn't work as well.

Then, I couldn't get my indexed field to work right. If I edit the indexed field and then close it shouldn't the existing data be sorted by the index field? It doesn't seem to work for data I enter after I create the indexed field either. Isn't an "index" a criteria by which one sorts a table? What is "an invisible data structure that stores the sort order of a table based on the indexed field"? Why not just sort the table by the field using the pull down command, i.e. sort A to Z?

Also, I can create a memo text with rich text for one record but not for all records within a field. How do you set up a rich text format that applies to all records?

The easiest fields to create for me were dates, numbers and the total calculation record (row), probably because I deal with numbers all the time and find them less intimidating. I'm hoping we'll learn how to create fields that make calculations based on existing values in a table. I'm also hoping to learn how to use a look-up function to return values in a table based on other values in the table (like the Excel Vlook-up function).

I'd like to attach my actual database here but I'm not sure how to do that. Would a screenshot do?

Not My First Tables

Sage, is this what you wanted?

Ok I know you can't see this. I finally got it to appear here by saving the screen print in Paint as a jpeg file not a bitmap image. You all probably knew that already but I didn't. When I played with the image attributes in Paint I could make it larger but not without losing some of the columns. Help, O Saged One!

Refining My Final Project

The purpose and scope of my database hasn't changed, but I'm realizing it's a big project for a beginner in Access.

I will be using at least four tables, an inception to date data table, a fund list, an org list and a line item list. The inception to date table will contain a fund number, an org number, an account number, an account title, and columns for budget, expenditures and encumbrances (which are type of accounting transactions). The fund list will tell me the name (title) of the fund number in the inception to date table. The org list will tell me the name (title) of the org number in the inception to date table. The line item table I will use to sub-total specific account numbers in the inception to date table.

I'm envisioning a report by org and fund which sub-totals budget, expenditures and encumbrances by line item and calculates the remaining budget balance. I'm hoping I can create a macro in Access which prints this data automatically for every fund/org combination.

My Final Database Project


My employer has hundreds of grants from federal, state and private agencies. Each grant has one or more funds. Each fund has one or more orgs. Each org has one or more line items. Each line item has one or more account codes.

What I need are inception to date cost reports for each org within a certain level or grouping of orgs. I need the org reports to be sub-totaled by line item. I need these reports to be generated and printed automatically each month.

Right now I can only get inception to date reports which have costs sub-totaled by org and by line item by entering first a grant number, then clicking on or entering a fund number, entering the word true and then waiting for a report (sometimes a very long time) in QMENU. This can take up to two days since my employer has hundreds of grants with multiple orgs.

Another alternative through VistaPlus is to enter the org level I want in the inception to date screens which are updated weekly. The problem is that the reports are totaled by fund which has all the available orgs mixed up within the report. Another problem is that the fund number appears only at the top of the page. Another problem is that this report extracts hundreds of pages of data. I can’t just download this report in Excel and sort by org and sub-total by line item. I’d have to copy down the fund number to every row of the report which would take more work than the option in QMENU. See attached screenshot.

Here’s what I am hoping to do with the help of Access.

Download the VistaPlus information by org level into an Access table.
Somehow populate the table with the fund number and line item description.
Sort by org and sub-total by line item.
Print automatically every org/fund’s information in a certain form format.

Present Knowledge of Databases?

I’d say my knowledge is pretty superficial. I’d like to get more comfortable with databases and know how to use them efficiently. I can modify the browsers in Databrowser but I really don’t understand how to create a browser query. I have no experience at all with Access.

Why Use a Database?

Wikipedia defines database as “a structured collection of records or data stored in a computer.” The “structural description of the type of facts held in that database . . . is known as a schema.” “A schema describes the objects that are represented in the database and the relationships between them.” A relational database represents all the information in the database in the form of multiple related tables.

At UAF we have a program called Databrowser, which appears to fit the Wikipedia description of a relational database. There are many browsers available for general use at the university but the specific browser I need, which is a grant inception to date browser limited to certain departmental org numbers is not.

I want to use the data in reports to which I do have access to create a report in a format to which I currently don’t have access.

Why Am I Here?

I’m an accountant manipulating lots of financial data every day. People count on me to provide information in a format that is easy to understand. At UAF there are many reports containing the specific data I need, however, none of these reports give me this data in a format that fits the needs of my department. I’m hoping that Access can provide the solution to this dilemma.