Is it possible to combine multiple columns in a query

[ketl70]

I have started using database and calc so most of attempts are trial and error and searching the web so sorry if this question seems out of wack.
Here is my situation. I have a calc spread sheet and I also created a table setup with about 50 columns and several hundred rows. I have a column with a list of studies then in the same row I have listed the various sources that are contained in that study. What I would like to do is perform a query of a particular source and see a list of all studies that used the same source. I have two problems I do not want to list all 50 columns for each query so it possible to combine the columns and is it possible to use input so with each query I can enter the search term only once.

Example of my headings. I would like to combine the columns SOURCE 1 to SOURCE 45, to eliminate writing them each time in a query and also the criteria the same number of times. I tried using " LIKE" for this purpose but received error messages. I have used :KEYWORD that works with a single entry is it possible to use across multiple columns as well?

Just additional some rows may contain only a few sources where others may contain 10 or more. Not sure if that makes a difference.

I am using Libre office Version: 5.1.6.2 Build ID: 1:5.1.6~rc2-0ubuntu1~xenial3 OS linux mint

ID NAME # PAGES AUTHOR SOURCE 1 SOURCE 2 SOURCE 3…SOURCE 45

[Ratslinger]

Hello,

Because on the terminology used in the question, this answer is based on you using Base for the database and not Calc itself.

The answer to your question is simple. The problem is with the design of your database. You have bypassed the benefits of a relationship DB & simply created a flat file database - everything in one record. This could have been done in Calc itself.

All the “SOURCE” data should be in a separate table with a field to link back to the “AUTHOR” record in the primary table record:

      Author Table                                 Source Table

      ID                                             ID
      NAME                                           Value of Author ID
      # Pages                                        Source
      Author

Withe the "Value of Author ID’ in Source Table equal to the “ID” in the Author Table you can create a one-to-many relationship between Author & Source tables. Now you can one or as many as wanted “Sources” for each Author. This saves much space & provides the ability to easily perform the task you wish to accomplish. Your parameter for the query can select any information from any Author & Source where they are the same and various other conditions.

Just a quick thought on how to convert with SQL, First create a query which generates a record for each of the sources:

Select ID, "SOURCE1" from AUTHOR where "SOURCE1" IS NOT NULL
union
Select ID, "SOURCE2" from AUTHOR where "SOURCE2" IS NOT NULL
union
Select ID, "SOURCE3" from AUTHOR where "SOURCE3" IS NOT NULL
union
Select ID, "SOURCE4" from AUTHOR where "SOURCE4" IS NOT NULL

as so on for all the source columns.  Mostly copy & paste statements 45 times.

Then use the query results in an INSERT SQL statement to put the records into the new “Source table”. Create a new “Author” table based upon the pertinent info in the current "Author’ table again using an INSERT SQL statement. Finally, link the two with a one-to-many relationship. It may seem a bit much but better than re-entry.

This will give you much more flexibility in acquiring wanted data.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.