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?