[Solved] Can't concatenate columns

Creating tables and queries
Post Reply
mbvisser
Posts: 7
Joined: Mon Apr 30, 2012 7:28 pm

[Solved] Can't concatenate columns

Post by mbvisser »

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
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Can't concatenate columns filled with the same listbox

Post by Sliderule »

You have designed a database that is NOT a relational database . . . but rather . . . like a FLAT spreadsheet. For example:
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.
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.

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)) 
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.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Can't concatenate columns filled with the same listbox

Post by UnklDonald418 »

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.
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
mbvisser
Posts: 7
Joined: Mon Apr 30, 2012 7:28 pm

Re: Can't concatenate columns [Solved]

Post by mbvisser »

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
Post Reply