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.