Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgThis case study is intended to improve your database skills. If you are working with ooBase, the free Open Office database, the "do this" instructions should work perfectly for you. Even if you do not have ooBase (why not?), the case studies are full of general principles which will apply to any database work.
I hope you someday find that ooBase delights you as much as it has me. There's more about it in the main index to this material.
Remember that Open Office, including Base, is free! And it is gaining traction. It's not new. Big corporations and government bodies are adopting it as their standard office suite... and saving million$. Write your elected representatives today, ask them why they pay Microsoft for a wordprocessor.
This page is suppose to be "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read if your window is not too wide.
Page contents © TK Boyd, Sheepdog Software ®, 3/06.
A number of charities annually receive contributions from me. (Not least Wikipedia, my computer-philic friends!)
Before I started the database which is the subject of this case study, I had a sheet of paper with a row for each charity, a column for each year. I give to each charity once a year. The order of the charities on that list is not alphabetical, it just reflects the historical accidents of when I began giving to each.
I want to continue using that tally of my giving, but I also want a "copy" of the information in my computer. From time to time, I start a new "ink on paper" tally, and I also need an annual list of donations, preferably in alphabetical order by the charity's names
I'm going to solve this problem "badly" in the first instance. Some of the shortcomings of the method I'll use do not have too many too terrible consequences in the very small database that I am constructing.
The "worst" thing that I am going to do is to have multiple fields for "money given". There will be one for gift in 2006, gift in 2007, gift in 2008, etc.
This is "bad" on two counts.
a) It is not as easily extended as the better system which I hope to write up as a sequel to this case study. Let's say I set things up with fields for every year through 2009. In 2010, I will have to add extra fields. Not a huge chore... but one that can be avoided.
b) Unless I give to every charity every year, the table will have "holes" in it. A charity that is added to the list of those I give to in, say, 2007, will have empty fields for the earlier years. A charity that is dropped from the list of those I give to in, say, 2005 will have empty fields in the later years.
The "reward" that has tempted me to use this "bad" design is that the form for data entry will be quite easy to use. It will be almost like filling the information in on a spreadsheet.
I suspect that relative ease of generation of the report for a year's giving, under the two approaches to storing the data, will be "six of one/ half dozen of the other". That is to say that while some aspects of doing the report will be easy with the "badly set up" database that follows will be easier than doing the report from the well designed database, others will be hard.
In designing databases, always think about how well your design contributes to ensuring that your data is correct. The design discussed in this case study does have the virtue of closely matching the "ink on paper" records it depends upon, thus eschewing some possible sources of confusion. Perhaps I should have said earlier that I tend to avoid working directly on a computer. They let me down too often. I will sit with my checkbook, charity requests, and "ink on paper" tally. As I write checks, I will make an entry in the tally. Then I will enter the figures from the tally into the computer, and use it to look at various things and prepare the annual list of gifts. The database will really come into it's own when my current "ink on paper" tally becomes too full, too marred by alterations. I will then create a new sheet to be the starting point of the next "ink on paper" tally. It will have the past few years' (but not all prior years') gifts listed. Charities I've stopped supporting will be left off. New ones will have proper places in the list, instead of being squeezed in, or relegated to the bottom.
First I created a folder for the database and all associated documents. In this case, the folder is called PDB059, as it is my 59th database in a series that started when I was using Paradox. The folder name will probably not arise again in this tutorial, but be sure that you keep the things on your disk organized. If you can't find things, they are of no use to you! It also helps you keep back-ups successfully.
The database will have just one table: Gifts
It will have the following fields, following types and sizes. All of the "text" fields are of the "Text [varchar]" variety.
the G2005, G2005, G2005, etc fields are all of type integer. They are for my gifts of 2005, 2006, 2007, etc. I will always give a whole number of dollars, e.g. $30, never $29.95, so I might as well use the simple data type.
Sample data....
Let's say that my ink-on-paper tally looks like.....
CHARITY 2005 2006 ------- ---- ---- Frontier Nursing 100 120 Americares 100 120 Technoserve 120 Mayo Clinic 500 500 Pomfret School 100 120 Wikipedia 50 50 Amazon Medical Project 100 100
These are all good and worthy charities, by the way.
The data for the not-obvious fields of the database would be as follows. The "MySortOrder" values given simulate how Technoserve would have been slotted in if it was not in the list in 2005 when the first MySortOrder codes were allocated.
ChariID ChariShortName MySortOrder ------- -------------- ----------- frnu Frontier Nursing aa amca Americares bb tese Technoserve bg macl Mayo Clinic cc irc I'nat'l Rescue Cttee dd wiki Wikipedia ee amme Amazon Medical Project ff
(A little point: I named the Charity ID field "ChariID" to keep it short. If you give a long name to a field that will have short entries, you may be annoyed with yourself later when designing a tabular form which needs a wide column just for the heading! Ordinarily I would have given "MySortOrder" a shorter name, but as it was a less-than-obvious field, I thought I'd leave the name longer and more meaningful for you. Note my aversion to upper case letters. It harms readability (bad) but simplifies data entry for me, a poor typist. Note that there is a method in the madness of the assignment of ChariID codes. The rule will have to be bent if, say, I someday decided to contribute to the American Medical Association (simple code would be amme... same as Amazon Medical... not allowed because this is the table's primary key, each must be different. A different code would be required for the American Medical Association. I'd probably use ama in this case.)
Next, just to get things started, we'll create a crude form for entering some sample data. Use the wizard....
... and then enter the sample data, so we have something to "chew" on. You can put "taxFrntNurs" or something similar in the ChariTaxName field. It is there because I really am going to use this database, but you don't need to fuss with looking up, using the real official names of the charities.
Life is short. The "right" way to do this will probably take five times as long to learn as all the time you would spend doing it the "wrong" way. I will try to expand this case study in due course with explanations of how to do it the right way... but for now, here are some ideas for quick and dirty solutions....
We'll do a report for 2006.
Start designing a new query, using the design view.
Add the "Gifts" table to the design window from the "Add Table or Query" dialog box.
In the grid at the bottom of the design window, set things up as follows. (You may have to double click on ChariTaxName to "wake the system up". Once you've got that in as the field for the first column, you can use the listbox to obtain the names of the other fields.
Field: ChariTaxName G2005 Alias: Charity Donation Table: Gifts Gifts Sort: ascending Visible: y y Function: Criterion: IS NOT EMPTY
Wow! In preparing that for you, I encountered something weird even for a computer!
To put that IS NOT EMPTY criterion in, this is what you do....
Type EMPTY in the cell. Backspace to the start of the cell, and type, without spaces, ISNOT. Use backspace again, go back to where you must, and put the two spaces in. NOW you can run the query, and you will get a sensible result! (Putting the "IS NOT EMPTY" criterion in stops the query from including Techoserve, which didn't receive a donation in 2005. Why didn't I use the more easily entered ">0"?
a) I'm stubborn. Not very clever, but stubborn. (It's how I've learned a few things about the wretched machines.)
b) If nothing has been entered in a field, can you be sure that "nothing" will be judged to be more than zero? Probably... but I'd rather rely on the sure-to-work (if there's no bug) "IS NOT EMPTY".
A word about the "Alias" row. What you filled in there gets used for the query's column headers, and you will refer to those fields by those names if you, say, use a report to display the results of the query.
Save the query as TaxReport2005. It would probably "do" for a quick and dirty list of your gifts for the year, but it is very little work to go a step farther.
Now switch to the reports section of ooBase. Invoke "Use Wizard to Create Report"
Hmmm. Have I given you bad advice? We have a query called "TaxReport2005", and a report called "TaxReport2005". Only one is truly a report, but they are both part of generating the report you need for filing your taxes. Maybe it would have been better to call the query "QueryForTaxReport2005", but as the file extensions and display filtering already keep the queries and the code-for-generating reports separate, I suspect we can all live with my "bad" names.
Run the report. It should work pretty well straight out of the designer.
Things you can do to make it fancier:
Close the report, if you have it open. (Re-)open in design view. (Right click on the report name in the ooBase main project management window, select "Edit")
In the edit view, you can....
Look at the title bar. You are working on a clever ooWriter document. Don't imagine you can be too clever about what appears in the table where your data will appear. (It has some Latin gibberish standing in for your data for the moment.) But you can "do things" to how even that appears. And you can change the headers on the document, move the table to where you want it, etc, etc, just as if you were (because you are) working in the wordprocessor part of Open Office.
Look Out: Two bits of Bad News, one small, one big... maybe
Little Bad News: I couldn't find a way to run the report when I was though editing, apart from saving my edits, closing the edit window, invoking the report. Many other things, e.g. the query designer, have buttons which let you run the query straight from the designer. By the way: I wouldn't "cheat", and run a query or report from the ooBase main project management window without first closing any open designer windows. I've got into frozen machines that way.
Big Bad News: Want to have a total at the bottom of the page, telling you the total of the donations? I don't think you can get this with the ooBase Report Wizard. But! You can get it, with just a little effort that will be rewarded again and again if you upgrade your ooBase to 2.4 (if necessary) and use the excellent, fully integrating, Report Builder from Sun Microsystems. I've done a tutorial with installation instruction and an introduction.
Once you've installed ooBase 2.4 and added the Report Builder extension, then on the ooBase main project management window, when you have selected "Reports", you will have a "Create Report in Design View" option in the Tasks pane. Double click that, and the Report Builder opens.
Open the Report Navigator, click on the top item, "Report", and in the Data tab of the report's properties, specify Content Type: Table, Content: Gifts. A list of available fields will open up. You may think there is a glitch in the Report Designer... When you've selected "Gifts", you may not, at first, see the promised list of the fields of "Gifts" in the Add Field dialog. Fear not. All you have to do is leave the Content listbox, and the field names appear.
Drag ChariTaxName and G2005 to the "Detail" band of the report design. Resize, rename and relocate the fields that result, and make the Detail band no higher than it needs to be to accommodate the two data fields on a single line. Move the label fields (the ones with the text in them) to the Page Header band.
My report came out in alphabetical order, without any special effort on my part. This puzzled me at first, but I'm pretty sure the records were presented in that order because, in the absence of any other sorting command, the records appear as if sorted on the primary key, ChariID. As an experiment, I entered a new record with ChariID='fff' and ChariTaxName="Zebedee". Lo and behold, the charities were listed ALMOST in alphabetical order, an artifact of the similarities between their ChariIDs and ChariTaxNames... BUT: Zebedee appeared just after Amazon Medical (ChariID=amme) and before Frontier Nursing (ChariID=Frnu). If you want to be sure of the order, or specify that the report be printed according to the values in MySortOrder, you use Sorting and Grouping, or you base the report on a query, which has had sorting built into it.
Click on the button to call up the Sorting and Grouping dialog box. Add the relevant field name to the list. (To get rid of one, right click on the gray box to the left of the field name, select Delete.)
A new band will appear on the report. Drag its bottom edge up, until the band has no width. (Or use the properties dialog to specify that the header not be present.) Run the report, it will be ordered according to the field you specified.
Confession. While this report is better for all sorts of reasons, not least offering value totals (we'll get to that in a moment), Technoserve is listed, even though we donated nothing to it in 2005. Ah! Had we based the report on the same query as we used before, the Technoserve line would not have appeared. (And there is another way to solve the problem, even with the report based on the table. Go into the Data tab of the Report's properties, and you can specify a filter. This time instead of "IS NOT EMPTY", you set the filter to ("G2005" IS NOT NULL). Bah! But it does achieve the desired effect, and the form can even be given report headers and donation total reports, the same way as we will do in the query based form, in a moment. (This little discovery about four hours into putting this tutorial together! It even survives the sometimes-total-wrecking absence of a value in G2005 for Technoserve!))
=====
The above material took about an hour to generate... all was going quite well... and then I hit a major obstacle. I must have spent several hours to get to the bottom of a few things that you are going to learn how to navigate with just a little reading. My stubbornness at work again.
It is really easy to generate a report with a total showing how much was given to the charities. It is also very easy to set something up that "should" work... and doesn't.
This time we're going to set up a report based on the query. Do the following...
Once again start a report, and put the Charity and Donation data fields in the detail band, with their labels in the Page header band. Do not bring up the Sorting and Grouping dialog again. The query already specifies a sort order. If you have a sort band, it seems to completely flummox the totaling mechanism.
Before we deal with putting the total of donations on the page, we have to open up some new bands.
Right click somewhere in the design window, inside the area covered by the rulers, but not over any of the things you've placed in the report so far. The menu you get should include "Insert Report Header/Footer". Click on that. You should now have the following bands....
Set the Report Header band's "Visible" property to No.
Use the Add Field dialog box to add again the Donation field. Put this instance of the Donation field in the Report Footer band. This probably seems an odd thing to do, but it will be the way we create our "total of donations" output.
When you've added the field, you'll again see a label and a data field. Change the text in the label to "Total Donations" (You'll have to do a bit of clicking first, to get just the label selected.)
Then click on the data field. Change the entries on the Data properties tab to....
Data Field Type: Function Data Field: Donation Function: Accumulation Scope: Report
Save (I called it "ReportBuilderTax2005") and run the report!
======
I must admit a certain exhaustion at this point. It really shouldn't be so difficult... but databases are more difficult than other applications you may have used.
If you've had enough, stop reading here. The following verges on "rambling"... but if you are keen, you may glean some things that you may find interesting! I'm going to explore two little mysteries, annoyances, what-chu-ma-call-its....
If you try to generate a "total donations" figure from a list of records in which one has no value in the donation field, your total will only include the figures following the record with an empty donation field. I suppose this is almost reasonable... the poor computer is adding (accumulating) values into a running total as it generates the report. Perhaps you or I would just skip over an empty field, but the computer, poor literally minded beast that it is, tries to add a null field, and gets confused.
The other thing that I spent time on, without solving (yet) was the matter of having text in the Report header. It would be nice... and not too much to ask?... if the first page of a report looked like....
My Nice Report for 2005
Charity / Donation
Americares 100
Amazon Medical 100... etc, etc....
... and then, in my perfect world, page 2 would look like....
Charity / Donation IRC 100 Mayo Clinic 500... etc, etc....
In other words, we want "My Nice Report for 2005" once at the top of the first page, and "Charity / Donation" on every page, underneath the report header on the first page.
Well.... I tried lots of things.
If you make the Report Header just "My Nice Report for 2005", and the Page Header "Charity / Donation", then the first page comes out as....
Charity / Donation My Nice Report for 2005 Americares 100 Amazon Medical 100... etc, etc....
Yep, you got it... Page Header goes above Report Header.
I thought I was being clever when I tried making the Report Header all of the following....
My Nice Report for 2005
Charity / Donation
... and the Page Header "Charity / Donation", and then I tried to suppress the Page Header for the first page. Found two ways....
There's a Conditional Print property for the Page Header, and I'm sure that something like "(Print) IF PageNumber>1" that would do what I want... but I couldn't find the magic incantation.
There's also a property of the report that lets you specify that the Page Header should not appear with the report header, AND the Report Header has a "Force New Page" property which can be set to "None"... but I couldn't get any records printed on the first page, just the Report Header, if I said I didn't want the Page Header appearing with the Report Header.
Ah well... these little mysteries certainly keep time from hanging heavy on my hands, anyway. I hope the SOLVED mysteries were useful to you.
================
I do realize that by just stopping here, I have left a number of questions unanswered, aspects of the charity giving database case study not explored. I will try to come back to this and extend it. In the meantime I hope you'll see the cup as half full.
I stopped because I wanted to get what's above online for you. Generating the essay is one thing. Even though it was written with its future use in mind, going from the essay to a published web page was about another hour's work.
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE
If you liked this ooBase tutorial, see the main index for information other help from the same author.
If you would like to use material from this site, say on a CD, my page about permitted use may save you some time. As my pages do get updated from time to time, it would seem to me that you would serve your readers better by giving them links to my pages rather than copying them as they are at a point in time. But I realize that internet access limitations may make copying a page necessary.
Editor's email address. Suggestions welcomed! - - - Want a site hosted, or email? I like 1&1's services.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org
. . . . . P a g e . . . E n d s . . . . .