[Solved] Can't concatenate columns
[Solved] Can't concatenate columns
Hello and thanks in advance for any kind of help you can give me. I have a database which contains movie data. One table holds the basic data of the movie and I created other tables that hold director data, genre data, language data and country data. The tables are connected with a movieID #. The directors table has 5 fields so a total of 5 directors per movie can be entered. The director fields are text boxes. The genre, language and country tables also have 5 fields but they are entered by using a listbox. Some movies have just one genre field filled while others all 5. When I make a query and I want to concatenate the different fields in one field this is no problem with the directors. The director table is called MovDirectorTable and the fields are MovID, MovDirec1, MovDirec2 etc. I display the directors in one field using "MovDirec1" || ' ' || "MovDirec2" || ' ' || "MovDirec3" etc. This works fine. The problem is that when I do the same with the genres, MovGenreTable with fields MovID, MovGenre1, MovGenre2 etc. I only get data in my field if all 5 fields are filled, anything less and nothing shows. This also happens with the other tables (language, country). The same with a combo box. Anybody any ideas, if this is not possible how can I change it so it will.
Last edited by mbvisser on Mon Mar 13, 2017 1:17 am, edited 1 time in total.
OpenOffice.org 4.1.3
AOO413m1 (Build:9783) - Rev. 1761381
OS Windows 10 Home 64-bit Version 1607
OS Build 14393.693
AOO413m1 (Build:9783) - Rev. 1761381
OS Windows 10 Home 64-bit Version 1607
OS Build 14393.693
Re: Can't concatenate columns filled with the same listbox
You have designed a database that is NOT a relational database . . . but rather . . . like a FLAT spreadsheet. For example:
Bad database design, the foundation of a database, will result in problems.
To prove this, use the COALESCE function to return string of the length of zero when the value is NULL
Additional comment: Writing a forum post as a long run-on paragraph is not helpful to interpret what you mean. Using line breaks and code markers would be more helpful.
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
I suspect the values for many of the columns a undefined . . . that is . . . NULL therefore the solution is to design relational database rather than a flat file database.mbvisser wrote:The director table is called MovDirectorTable and the fields are MovID, MovDirec1, MovDirec2 etc. The problem is that when I do the same with the genres, MovGenreTable with fields MovID, MovGenre1, MovGenre2 etc.
Bad database design, the foundation of a database, will result in problems.
To prove this, use the COALESCE function to return string of the length of zero when the value is NULL
Code: Select all
COALESCE("MovGenre1",SPACE(0)) || SPACE(1) || COALESCE("MovGenre2",SPACE(0)) || SPACE(1) || COALESCE("MovGenre3",SPACE(0)) || SPACE(1) || COALESCE("MovGenre4",SPACE(0)) || SPACE(1) || COALESCE("MovGenre5",SPACE(0))
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Volunteer
- Posts: 1544
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: Can't concatenate columns filled with the same listbox
As Sliderule advised many of the problems you mentioned could be resolved using relational rather than flat tables to store your data. The process know as normalization can be use to convert flat tables to conform to the relational database model.
There is a tutorial on relational database design that can be downloaded from
https://wiki.documentfoundation.org/ima ... torial.pdf
The process of table normalization is discussed in Chapter 2.
There is a tutorial on relational database design that can be downloaded from
https://wiki.documentfoundation.org/ima ... torial.pdf
The process of table normalization is discussed in Chapter 2.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Re: Can't concatenate columns [Solved]
Thanks Sliderule, just found your response to question "Concat with null string"
OpenOffice.org 4.1.3
AOO413m1 (Build:9783) - Rev. 1761381
OS Windows 10 Home 64-bit Version 1607
OS Build 14393.693
AOO413m1 (Build:9783) - Rev. 1761381
OS Windows 10 Home 64-bit Version 1607
OS Build 14393.693