Combine two fields and tags

[IreneEng]

Hi dear comrades,

If I want to combine first name and last name, is it done in table or form?

How do I 'tag' a row: i.e. book ABCD deals with 'apples', 'wars', 'floor tiles', 'cooking', etc. Should I create 4 different fields in the table? What if a row has 20 things associated with? 20 fields in the table?

Thank you in advance
Irene

[UnklDonald418]

If I want to combine first name and last name, is it done in table or form?

You would use SQL to generate the full name and display it on a form control. For instance

Code: Select all

SELECT “FirstName” || ' ' || “LastName” AS “FullName” From “MyTable”
or
SELECT “LastName” || ', ' || “FirstName” AS “FullName” From “MyTable”

How do I 'tag' a row: i.e. book ABCD deals with 'apples', 'wars', 'floor tiles', 'cooking', etc.

You are describing a one-to-many relationship which is a core function of relational databases.

Should I create 4 different fields in the table? What if a row has 20 things associated with? 20 fields in the table?

No.
You would use a table for Books and another table for Things. By linking the tables together a single book could then be associated with 0 things or 1,000,000 things.
But I suspect that most items in the Things table could also be associated with more than one book, so you actually have a many-to-many relationship. That requires a third table where each record would contain a foreign key linking it to a particular book in the Books table and a foreign key linking it to a particular thing in the Things table.
It sounds like you would benefit from a tutorial on relational database design.
https://wiki.documentfoundation.org/ima ... torial.pdf

[IreneEng]

Thank you so much.

One problem, for me anyway: how to link two tables when the full names are combined in a form?

[Villeroy]

viewtopic.php?f=100&t=40444 is a small tutorial of mine with an example database. There are a lot more tutorials and examples in the Base section of this forum

The attached example demonstrates a list of persons, a list of animals (species not individuals) and a list of things.
Each person belongs to one animal (species). Animals belong to persons. This is a one-to-many (1-n) relation.
Each person belongs to many things. Each thing belongs to many persons. This is a many-to-many (m-n) relation. It requires an additional table mapping things to persons.
You can see the relations in the relations design window menu:Tools>Relations... The 1-side is always a primary key, the n-side is called a foreign key. The database does not allow to add a foreign key value on the n-side that does not exist as a primary key on the 1-side. You can not assign animal #99 to a person unless there is an animal with ID 99. Try out.

The whole thing is based on the technical, meaningless person-IDs, thing-IDs and animal-IDs. The birthdays, prices, colours, materials, weights and other attributes that may belong to persons, animals and things are completely out of scope. I did not append all these fields to the demo tables, just the names.
In the simple input forms you only see the names. You don't see a single ID value although everything is looked up by the unique ID numbers.
Representing related tables in forms is possible by utilising subforms, list boxes and table grids with list boxes.

By the way: There are also 1-1-relations where you would link the primary keys of 2 tables. Think of individual animals belonging to one individual person and vice versa.

This is roughly how facts from the real world are wrapped into relational databases.

[UnklDonald418]

When the default Embedded Base database model doesn't close properly the result can be loss of all your tables.
To avoid that angst you should consider using a Split database model. I find that the simplest way is to follow the procedure found at
viewtopic.php?f=83&t=61183

[IreneEng]

When the default Embedded Base database model doesn't close properly the result can be loss of all your tables.
To avoid that angst you should consider using a Split database model. I find that the simplest way is to follow the procedure found at
viewtopic.php?f=83&t=61183

I don't know if we're talking about the same thing: I just lost all my tables and data after restart the laptop

http://www.IreneEng.com/wp-content/uplo ... -07-28.png
http://www.IreneEng.com/wp-content/uplo ... 7-28-2.png
http://www.IreneEng.com/wp-content/uplo ... 7-28-1.png

[UnklDonald418]

I'm not entirely sure why, but Embedded databases seem to be more troublesome for laptop users.
Do you see your data if you run a query?

Code: Select all

SELECT * FROM “books”

and also

Code: Select all

SELECT * FROM “people”

If they don't return a list of all the records you entered then Yes that is what I am referring to.
In that case look in the directory shown when you select Tools->Options->OpenOffice->Paths->Backups to see if there is a recent backup.
If that fails you can try to recover your tables using the procedure shown here
viewtopic.php?f=83&t=17125

If both queries show your data then you may be running up against one of the idiosyncrasies of the Base Table designer GUI.
Either way I would create a new Split database.
If the data in your tables still exists or is recovered drag both tables from your Embedded database into the new Split database. You can also drag existing Queries, Forms and Reports to the Split database.
Unfortunately the Base Table designer GUI has even more issues with a Split database model as discussed in the link to [Wizard] Create a new 'split' HSQL 2.x database
It is better to Create and Alter Base tables using SQL commands rather than the GUI.

[Villeroy]

You can not change the field type from text to date because the already stored text is not convertible.
You can not delete the column because something keeps a finger on it.

Databases are very difficult to build up. You can not change these structures all the time without destroying them. Therefore I recomment to restart from scratch and build up a working structure with a minimum of test data.

[IreneEng]

Either way I would create a new Split database.
Unfortunately the Base Table designer GUI has even more issues with a Split database model as discussed in the link to [Wizard] Create a new 'split' HSQL 2.x database
It is better to Create and Alter Base tables using SQL commands rather than the GUI.

emmmm...What's split? I tried to follow

Instructions for use:

(1) Ensure global macro security in *Office is set to Medium (or Low):
Tools (*Office) > Options (Preferences) > *Office > Security > Macro Security > Medium

but can't even get over the first step ... http://www.IreneEng.com/wp-content/uplo ... -07-30.png

Wondering I should even use this since I don't know much about codes, etc.

[UnklDonald418]

emmmm...What's split?

When you open a Base database it reads everything into memory: Tables with the associated data, Queries, Forms and Reports.
When you Save or Close the database everything is written back to your storage device.
When using an Embedded database everything is stored in a single Zip file with the .obd extension. If something happens and the Zip process doesn't complete then the .obd file may become corrupt and Base may not be able to recover it.
When using a Split database some of the components such as the Tables with the associated data is no longer zipped into the .obd file, it stored in a separate .data file.

but can't even get over the first step

What happens when you click on Tools->Options?
If nothing happens open a new OpenOffice Writer file or Calc spreadsheet and try it there.