i should be working (but i'm fixing my computer).

So, as reported here, I built a little "report system" in MS Access because that is what is available on my company's server.

...

Unfortunately, I cannot get a sufficient cellular internet connection from "on location" to use the system I created.  Rats!

So, I needed to rebuild the system on my personal computer.  Which does not have Access.  Well, I do own a copy of Access -- but guess what, it is a Windows XP version, and we are now on Vista, aren't we?

Then I thought, "Why didn't I just use OpenOffice Base?  Because, if the company wants to access my database, it will be free for them to install OpenOffice and it is cross-platform, which is good, since I actually use Linux and not the crash-0-roo blue screen of death system.

Though, don't get me started.  It turns out that OpenOffice base, especially when augmented with the Sun report builder extension, is capable of taking out kde plasma on a regular basis.  And likewise unstable on Vista.

Which I could have lived with, but the report generator does not have text boxes that can "grow" and "shrink" to accommodate the memo field data -- which Access does have.  So, now I had a workable input system in OpenOffice and a better data output system in MS Access.  But, as is always true of proprietary systems, never the twain shall meet.  I could get data out of Access, but not back into it.

So, I spent some time trying to conceptualize options.  I have been using dbase/foxbase/foxpro DOS version for 20+ years.  I could re-do it in that system.  But, unfortunately printer drivers were pretty rudimentary in the versions I have.  As were lots of the other tools.  Not my preference.

I could easily do the project in MySQL via PHP -- except I don't want an unencrypted database residing on my third party server.  And, in addition, if I'm not getting an adequate internet connection, a remote server is once again out of the equation.  That meant I could install Apache/PHP/MySQL locally if I wanted such a solution.  But -- as opposed to being able to copy a single file from machine to machine to share my database, I'm less clear on how I might pass around the MySQL database.  And, I do not always use the same computer.

So, I investigated "Rekall" -- but Rekall didn't have the report sophistication I needed.  I looked at some other open source database packages.  I could not find a quick solution that did not require re-educating myself at enormous time loss.

Then I discovered that if I save my OpenOffice report to PDF, I can copy the text and all of the blank space disappears.  But, all of the fields end up on separate lines.  (My goal was to emulate a totally text-based email I have been producing previously).  The solution was to create query fields made up of multiple fields, though I ended up with a query that will only open in SQL mode and not in the query design tool.

In the meantime, I did in fact install Apache/PHP/MySQL onto my netbook.  I had been wary of doing this because I feared it would slow down the netbook operation -- but it does not seem to have taken a signficant performance hit as far as I can tell.

So, I will probably re-write my system in to PHP, but that will be a time consuming task, especially as it will involve writing the same input and report generation yet a third time.

Also, their are idosyncracies.  I HOPE that after all this, I have created a system that is more accurate and less time consuming than just doing the reports by hand in a text processor.  Some of the steps are:

(1) our main calendar is "I Got Notices".  I'm not really fond of its calendaring features, but that's what we use.  IGN will only export to MS Outlook and no other format.  I am not familiar with the IGN database and don't know if I could capture the data directly. (But see my later entries -- I redid the whole thing in PHP and I utilized the IGN output to Google Calendar ...)

(2) Outlook will output to CSV, but I could not find a way to append the Outlook CSV directly into OpenOffice.  So, I have to use an MS Access append query.  Outlook outputs the calendar to mdb, then MS Access appends it.

(3) Then, Ms Access will output to CSV.  I open this in OpenOffice Calc.  (For some reason, OpenOffice put all the data filters in Calc and not in Base.)

(4) Once open, I can drag the entire spreadsheet to my Base table icon, which opens a "Copy" dialog.  I change the name from "Table1" to the actual table name and use "append".  Data is now in my OpenOffice database.  Now, I can use my input form to add the real-time data and two different "reports" to generate the long and short version of my daily hearing report.

(5) Building the reports was difficult, because I wanted to combine fields.  Fortunately, OpenOffice automatically "TRIM"s the fields, so I don't get a bunch of annoying white space.  But the report building tool did not properly handle CONC (where one might say something like "date" ||  ' - ' ||  "time"  ||  ' - ' ||  "subject" (To yield "01/01/10 - 12:00 pm - Mr. Smith hearing").  So, to get these combinations, I had to build queries with the combinations as fields in the query and then build the report with those fields.

Also, every time I edited the query, I had to delete the text box and reinsert the new one -- it was not sufficient to simply update the data field in the control properties.

OTOH, combining the fields made more effective grouping, because I would get all of the output I wanted every time the group changed.  Whereas, when I did it with individual fields, only the changed data would print.

OTOOH, I had to have individual fields in the query as well as the combined fields because the grouping would only work if I stacked the groupings: Date>Time>Location (etc.)  Then, I only turned on the last group (Location) by making the header "present" and put the entire combined field string query into that header.

MS Access does a better job of adding default entries to empty fields, also.

So, ultimately, it took way too many different software programs and way too much manual intervention to achieve my desired result, but I THINK the result will be worth the trouble.

Or not.