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.