[Solved] Starting from scratch

Discuss the database features
Post Reply
jbl1954
Posts: 25
Joined: Fri Jan 17, 2020 5:55 pm

[Solved] Starting from scratch

Post by jbl1954 »

Presently I use openoffice spreadsheets. I collect and record info on dragonflies and damselflies (smallest/simplest spreadsheets) to moth and butterflies which has families, subfamilies, names/scientific names, and individual sightings from several years. I'm not sure where to start. I've decided to begin with the simplest, so I've searched to find a good tutorial on how to create a database from an existing spreadsheet(s) but, nothing seems to help at this point. So I'm kind of lost/stuck at the moment.

The big reason for deciding to try a database is my spreadsheet is getting way to full with my moth/butterfly sightings. So I was hoping that the database might make entry and searching much easier. I also have links in the spreadsheets to take me to websites and also picture files. I have spreadsheets that total, rank and compare the individual family spreadsheets for the butterflies (8 total sheets = 5 family sheets, 3 totals and rank sheets, etc.) and moths (33 total sheets = 29 family sheets and 3 totals sheets and 1 example sheet). Each family sheet contains the subfamilies and moths/butterflies that belong to each subfamily as well as genus information for each one as well as a totals column for each moth/butterfly which shows how many sightings are in each moth which is captured from the sightings that are also on the family sheet and in some cases specific information about individual sightings (male, female, mating pair, caterpillar).

From what I've read on the forums databases are much better to use that the spreadsheet in my case. Any help/direction would be greatly appreciated. Thanks.
Last edited by Hagar Delest on Sun Jan 10, 2021 12:37 pm, edited 1 time in total.
Reason: tagged solved.
Apache OpenOffice 4.1.7 Windows
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Starting from scratch

Post by John_Ha »

You are making the correct decision to move to a database. Once the database is running it will be easy to import your spreadsheet data.

Check out the Base Tutorials and Database Examples

I think the LibreOffice documentation is better than the (obsolete) AOO manuals (and obsolete AOO software!).

Browse or search the forum for ideas.

It's a lot of work - try Googling for an existing system.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
jbl1954
Posts: 25
Joined: Fri Jan 17, 2020 5:55 pm

Re: Starting from scratch

Post by jbl1954 »

Thanks for the input : )
Apache OpenOffice 4.1.7 Windows
User avatar
DiGro
Posts: 173
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: [Solved] Starting from scratch

Post by DiGro »

Maybe this is also a good place to start

https://neowiki.neooffice.org/index.php ... Back_Again
____________
DiGro

AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
jbl1954
Posts: 25
Joined: Fri Jan 17, 2020 5:55 pm

Re: [Solved] Starting from scratch

Post by jbl1954 »

DiGro wrote:Maybe this is also a good place to start

https://neowiki.neooffice.org/index.php ... Back_Again
Many thanks. Reading the description it sounds just like it is exactly what I was looking for : )
Apache OpenOffice 4.1.7 Windows
jbl1954
Posts: 25
Joined: Fri Jan 17, 2020 5:55 pm

Re: [Solved] Starting from scratch

Post by jbl1954 »

I feel really stupid right now. I'm following the directions very carefully but, appear to be overlooking something. I've imported data from the spreadsheet and it appears to be fine. The problem I'm having is when I create a form it shows all existing 1167 records but, I can't enter any new records. I keep starting over and over by creating the database and I end up with the same result - I can't enter a new record(????) So this is where I'm stuck. Everyone seems to rave about using a database instead of spreadsheets but, I can't use it because I can't make any entries.
Apache OpenOffice 4.1.7 Windows
User avatar
DiGro
Posts: 173
Joined: Mon Oct 08, 2007 1:31 am
Location: Hoorn NH, The Netherlands

Re: [Solved] Starting from scratch

Post by DiGro »

That will be probably caused by an omitted field for a primary key.

The database has to have a primary key to enter new records and/or to modify/delete records.

Mostly this field does not exist in an spreadsheet because it has no use there.

You now have two possibilities : add a field Primary key to the database OR
add a field to the spreadsheet and import the data again.

If you decide to put it in the spreadsheet then you best name it ID, because the new database will recognize that as the Primary field. If not then you can assign it to that field.
You can simply fill the field ID with a numeric sequence e.g. 1...1167 (one number for each record).

You could also add the field ID with SQL.

QuazzieEvel wrote in 20111:
Try adding it manually--via SQL tool. (Tools -SQL... menu option.)
the syntax is :

Code: Select all

ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>]
        PRIMARY KEY (<column list>);

For a table named PropertySheet, the command could be something like this:

Code: Select all

ALTER TABLE "PropertySheet" ADD CONSTRAINT "PropertySheet_pk" PRIMARY KEY("ID")
The name can be any valid identifier name.

NOTE: You may need to refresh the tables (view-refresh tables) to view changes after running the command.
Obviously you have to change the name "PropertySheet" to the name of your own table
____________
DiGro

AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
jbl1954
Posts: 25
Joined: Fri Jan 17, 2020 5:55 pm

Re: [Solved] Starting from scratch

Post by jbl1954 »

"If you decide to put it in the spreadsheet then you best name it ID, because the new database will recognize that as the Primary field. If not then you can assign it to that field.
You can simply fill the field ID with a numeric sequence e.g. 1...1167 (one number for each record)." I did this and all the rows imported/transferred fine.


I think I got my own answer. When I look at the created database table (right click, edit) all the field properties are grayed out so I cannot make any changes. It appears that 'connecting to an existing database' via the spreadsheet is not a good option in my case. At this point I give up. There is just to much information that I want to transfer over. So long story short I'll stick with spreadsheets. It gives me exactly what I'm looking for. "If it ain't broke don't fix it'. Thanks for your help.
Apache OpenOffice 4.1.7 Windows
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Starting from scratch

Post by Villeroy »

I've done that many times. First of all, you need to create a valid, normalized database with column types based on the existing sheet data. Finally you paste raw data from your sheets into the finished database.
No, you can not edit spreadsheets in Base. You can connect them to Base in order to pretend some kind of database. This can be very useful for serial letters and simple list processing stuff. A well maintained spreadsheet may mimic a database without ever reaching the capabilities of a database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
jbl1954
Posts: 25
Joined: Fri Jan 17, 2020 5:55 pm

Re: [Solved] Starting from scratch

Post by jbl1954 »

I did what you said. I copied the spreadsheet and in the new spreadsheet I moved the information around into rows and columns. I then created the database and it looks fine. Bottom line I couldn't add any new information. Presently I can do pretty much what I want with the spreadsheet (sort, add, rank, compile, etc.) I have basically three sheets that are the result of that work. One shows me how many Families, subfamilies, butterflies, genus, and caterpillars and ranks for each family. It also has links to websites and file folders. One shows me every single butterfly (74) ranked by the sightings I've submitted, etc. I'm sure all this can be done with a database but, I've spent the last week trying to figure this out and realize that I'm happy with what I have. The OpenOffice site is great because most of the spreadsheet functions I learned here. So thanks. I tried looking at the tutorials but, what you showed me was the easiest and best I'd seen but, even that was hard because it was referring to "NeoWiki" (???) which appeared to not be exactly the same as OpenOffice with regards to the database.
Apache OpenOffice 4.1.7 Windows
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Starting from scratch

Post by Villeroy »

If you connect a spreadsheet to a Base document via File>New>Database... connect to existing database of type "Spreadsheet", then you do NOT create a new database. You pretend that your spreadsheet IS the database which is just fine for simple tasks. The used areas of the sheets and the defined database ranges look as if they were database tables but your data are still in the spreadsheet and you need a spreadsheet application to edit spreadsheet documents.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply