Database software and archives.

[Scott McIntosh]

Having been around this and several other forums and been researching myself for several years myself it has become apparent that some of you must use your own form of databasing software to file, search and locate your information. I am aware that for a lot of you that books and paper or written records are still the best system for you.

The reason I am asking is as mentioned I have quite a bit of information that I would like to file and store digitally and I wondered which kind of system you all had for this. I have had several ideas for this but none seem ideal for me. I am not that good with computers really but I can find my way around new software eventually.

So my question is this. What do you all use and what are its best functions. I would like to store mainly text based information but I would like to be able to set up titles and type or cut and paste the information for these titles in boxes or lines based on the titles. I would also like to be able to link pictures or pdf files to the main text file.

Which would be the best way to do all of this. Is there an easy and quick way of doing this or does it require a lot of keyboard bashing like I am at the moment.

Well over to you really. Just looking for a way to archive all my data to a digital source and be able to do all of the above.

Maybe some day I would to share and update within a group but that is a long way off.

Anyone know how to do this without involving a computer programmer.

[Bill Walker]

I use Microsoft Access, becuase it came with the professional Windows Office I use for work. My son the professional programmer says it is old fashioned and pretty lame, but even an old guy like me taught himself how to use it.

I have 40,000+ individual aircraft records in my Canadian database, and 200,000+ records in another database of other nationalities. I really appreciate being able to quickly search through all this.

some words of warning for any database:
Think about your data structure before you start entering. It can be a major pain to change the structure after you make a few thousand entries. Do some reading and research, or get sone help from somebody with experience.
Being consistant with data formats makes searching and sorting so much easier. Just one example - pick a date format and stick to it. Then you don't have to search for "3 June 1942", and "June 3rd, 1942", and "6-3-42", etc. when looking for a date. Same goes for ranks, abbrieviations, and other commonly used terms. I made a "cheat sheet" in the begining to force myself to be consistant.

[mhuxt]

Bill's words are very wise, especially re: ensuring consistency and understanding structure. For a proper database, you really only want to enter data once, although you can refer to it umpteen times. Purists might say you should have one table with days of the week, one with days of the month, one with months and another with years; if you want to enter date data, you do so on a form which refers to those tables. Same goes for Bill's cheat sheet of ranks, abbeviations etc. Access is very good at keeping all of this straight.

The more amateur approach is to use Excel. Many folks do so because it comes wth the home version of Office, they won't need to be keeping as many records as Bill (old Excel will only hold 65,000-odd lines, though I think native 64-bit Excel will now hold more) and because setting Access up can be such a bleeding pain.

My Mosquito claim and Mosquito losses databases are both in Excel - works fine for me with the following two caveats. 1) I didn:t do as Bill says and set it up properly in the first place. I had textual entries for losses, but I didnt from the start classify them well: Day Bomber Ops, BIV variant, daytime loss, Western Europe, 105 Squadron, etc. Had to retro-fit that, was a pain but now having done so allows me to do more analysis of the database as a whole. 2) I got myself into trouble when I started to add markings info - ended up with a single cell having multiple markings for a single aircraft, not good. Had to split that off and do a separate spreadsheet so I could much more easily keep track of the "many to many" relationship between aircraft and markings.

Only thing wrong with Bill's database is it doesn't have enough Mosquito information (grins).

[CZ_RAF]

Well I have my DB of Czechoslovak airmen, aircraft etc. also in Excel.
I have tried several databases at the beginning of 90s when I started to use computer but come to conclusion that Excel is best - I have not so many records so no problem with rows, from my point of view it is not so difficult to change the structure - merging and dividing cells, I can use there colors for different groups of records, formulas, statistics, so I am fully satisfied with the Excel. But it does not means that there is not other application which is better:)

Pavel

[Ross_McNeill]

Hi,

I use Access for my main database. Far more stable for large database than Excel and a superior pallet of search methods.

Ability to set different forms and reports for data entry and display gives me the ability to control how the cursor moves from field to field making data entry flow rather than bouncing back and forth through fields.

Over the years I've added linked images and external sources without needing to do major surgery on the database.

Maintenance tools allow for repairing indexes and main database as well as showing incomplete or duplicate entries.

Regards
Ross

[Scott McIntosh]

Thanks for your time chaps in answering my questions.

Well mainly and in the past I have used excel for mostly everything. At the moment I only have microsoft works spread sheet licence on my laptop and it is even more labour intensive than excel is. Cut and paste in spreadsheet is a pain with the text spreading over several boxes. I can however add columns whenever the need for another title is required.

Very wise words Bill but I think I have that covered as most if not all of my data is in a tried and tested form anyhow.

The most valuable boxes and the ones that need to be big are Crew notes, Aircraft notes and Notes. All the rest are in a very good form anyhow.

I think as Access is what most people used anyhow I will look into this first and see how I go.

Does anyone else have any ideas or any other software experiance.

[RecklessRat]

If you're struggling with Microsoft Works, I would suggest you try LibreOffice (OpenOffice). It's a fully-fledged office productivity suite similar to Microsoft Office, containing a word processing module, spreadsheets, presentations, and even a database. Best of all - it's free and can work with the Microsoft formats (.doc, .xls, etc. etc.)

<a href="http://www.libreoffice.org/">http://www.libreoffice.org/</a>

I've used Open/LibreOffice for many years. It's easy to use, familar to anyone who has used the older incarnations of Office, and remarkably powerful. The main drawbacks revolve around styles in Writer and macros/VBA in Calc, and there are a few fundamental differences in the way it handles some things, but that may or may not present a problem depending on what you're trying to do. It (And the modern versions of Excel) will handle a million rows and 1000 columns, however if you're working with something that big you probably ought to be using a database.

Back on the original topic, I think one of the easiest solutions for files is to create an orderly filing structure and stick to it. For example, there was a Typhoon serial mentioned on another thread which I thought rang a bell - MN236. Because I have things filed in a structured manner, it took me about 20 seconds to find the AM 78's and see that I had MN136; close, but no cigar. Obviously, the bigger your collection gets the less efficient this is. At work, we have a similar flat filing structure of folders and a snazzy Excel index with some clever hyperlinks to navigate through the information and link to the files. It works extremely well for what we want it to do, so long as everyone uses it correctly. You could go further and look at a DMS like Alfresco which is extremely powerful, but you really need to be a bit of a guru to get that working.

Hopefully this has been some help; good luck!

Jeff

[Scott McIntosh]

Well since I last posted I have looked at some online databases and these look ideal for creating and sharing information so that sub users can access and update records. Everything looks fantastic until you find out that the 1 million records entry they talk about is their most expensive product and that their free edition is basically a 500 entry trial period. Ok I know you only get what you pay for but I dont have that much money to throw at a service that only I would be using to start with.

Rat good advice and I have seen that office product before. Maybe need to take a look at it closer.

I have since the last post placed a bid on an office 2010 professional edition on the dreaded ebay. It is the full version and in sealed condition but I suspect it will be sold for far more than I can currently afford.

I will struggle on with works database for just now and see about exporting all the data later.

[Bill Walker]

The new 64 bit Excel can handle 100s of thousands of records. I have used Excel databases for smaller work related tasks (like publication lists with revision levels) and my main complaint is that is not really user friendly. If you are a regular Excel user you may not notice this, but starting from scratch could be hard. I should add that I've been doing technical calculations in Excel and Lotus 1-2-3 since the mid 1980s, and still find it hard to get around sometimes.

Access, on the other hand, does let you easily create custom forms and searchs, and is much easier when it comes to restricting formats of data being entered, as others have said. I also found several on-line Forums to get help from experienced Access users. There seems to be quite a few of them. I have solved a few problems at the Microsoft help pages as well. Also, Access can receive data from, and send data to, any of the other Office applications, including Word and Excel. My web pages come from the Access database into Word or Excel for tidying up, before Word turns them in to HTML.

Mhuxt, Access just took a few minutes to tell me I have 444 RCAF Mosquitos and 4,136 RAF Mosquitos in my databases. It's a start.

[mhuxt]

Hiya Bill,

Heheheh, was just pushing your buttons, hoping to get more of the RCAF Mosquito stuff on-line. The stuff about RCAF Mossies which appears on your site simply can't be found elsewhere.

Either way, ultimately a proper database (as in, not Excel) is the best way. All of my files grew up independently, but if I were to put them into Access it would be much more efficient and allow better development. Would have the serial numbers on one table, markings on another, fates on another, crews on another, could transcribe ORB entries to analyse number of sorties, flying hours, track airframes across squadrons, etc etc etc, all in one place.

I'll flick you a zipped copy of my Mossie losses db at the addy on your website Bill, in the hope it may be of some use to you. I don't currently have a flag in there re: "passed through the hands of a 4XX Squadron", but with my mad Excel-fu skillz it shouldn't take long.

Cheers,

Mark

[Errol Martyn]

I have been using Access for about 20 years now. I've only taken advantage of its most basic features but even with just these I was able to create almost the entire text and data for my 1400-page 'For Your Tomorrow' trilogy, leaving the transfer of information into WORD until shortly before publication. Not only could the data be sorted and interrogated at will but checking for errors and consistency while still in Access was far easier than leaving it all until WORD. One feature that drew me towards Access in the first instance was its Memo field option - essentially a basic (unlimited?) word processing field that can be searched but not sorted.

Errol

[Mikkel Plannthin]

When I decided to create a database for my information about 2005, my MS Office version did not include MS Access which I had worked with previously. Furthermore, I was looking for a DB programme which I could easily base a website on.

I chose MySQL for the database and phpmyadmin and several MySQL programmes for database administration. I created a website and a web-interface for the database using the PHP scripting language. It runs on an Apache server locally on my computer and on a webserver.

I had some experience with access beforehand, but I none using SQL and PHP. I found it quite easy to learn and very effective form my purpuse. MySQL comes in a community edition free of charge.

I do find Excel a very usefull toll for many things. The main reason for going further is to create a relational database. I can strongly support the remarks on planning the database. During the first phase, the planning, the main tool for me was pen and paper.

In addition to all this I also have a very planned file structure on my computer and I use the possiblity of tagging files on my computer often, e.g. if I have taken a photo of a document I tag the file with names etc. to make it searchable.

Regards

Mikkel

[alanshack]

Have you considered a free form database like .Info-select. I used it in a previous life with some success.especially when new factors cropped up and needed adding, I think the later forms are much improved.
Alan

[Scott McIntosh]

Considering the amount of money I would need to spend I am going to struggle through with Works Database and or Works Spreadsheet. I may in the future spend some money on access and use this but for now I need to learn the way of works Database.

Thanks for all the tips and advice some of which I will be taking when I have enough money.