Liboff base: hard time collecting info from table

[415flip]

Dear fellow members

I’ve a hard time making a list of birthdays sorted by month/day name of person.
I’ve put my family addressbook in a calc table and dragged it into base.

every family has one row, the columns are named:
family name / adres / postalcode / … / … / name01 / birthday_name01 / name02 / birthday_name02 / name03 / birthday_name03 / … / …

how can i make a list of a members of a family (if not empty) with a known birthday, and sort it by month and day.

I’m thinking of making a table, with:

name01(if not empty), birthday(if not empty and is January), family name,
name02(if not empty), birthday(if not empty and is January), family name,
name03(if not empty), birthday(if not empty and is January), family name,
name04(if not empty), birthday(if not empty and is January), family name,
…

…

…

name10(if not empty), birthday(if not empty and is December), family name,
name11(if not empty), birthday(if not empty and is December), family name,
name12(if not empty), birthday(if not empty and is December), family name,

Is this the way to go, or do you advice alternatives?

I use libreoffice 4.4.4

I hope you can give me some good advise.

greetings 415flip

[doug]

Yes, the way described is quite hard. Consider approaching from a different angle, with the tables as follows (these are actual SQL commands that can be entered in Tools → SQL):

CREATE TABLE "tblSurname" (
  "rkFamily" INTEGER GENERATED BY DEFAULT AS IDENTITY,
  "surname" VARCHAR(120)
)

CREATE TABLE "tblPerson" (
  "rkPerson" INTEGER GENERATED BY DEFAULT AS IDENTITY,
  "rkFamily" INTEGER,
  "PersonName" VARCHAR(240),
  "dtBirthday" DATE
)

You could create a FOREIGN KEY from tblPerson to tblSurname.

ALTER TABLE "tblPerson"
    ADD FOREIGN KEY ("rkFamily")
    REFERENCES "tblSurname" ("rkFamily")
    ON DELETE CASCADE ON UPDATE CASCADE

The tables will be visible when you click to tables and then View → Refresh Tables. The first is the list of families. The second is the list of people and birthdays.

Then, after adding your families and birthdays, you could run following query:

SELECT "s"."surname", "p"."PersonName", MONTHNAME("p"."dtBirthday") AS "strMonth", "p"."dtBirthday"
FROM "tblPerson" AS "p"
JOIN "tblSurname" AS "s" ON ("p"."rkFamily"="s"."rkFamily")
ORDER BY "p"."dtBirthday"

I would call that the “traditional” way of solving your problem. You could import your existing data using INSERT queries and UNION statements set forth below (or cutting and pasting in Calc).

With the setup you have, you will need to eliminate NULL values and consolidate the various columns. This would be done with a series of stacked queries glued together with UNION statements. That is, it would be

SELECT "name01" AS "nm", "birthday" AS "birthdayall" From `tbl1` WHERE  "name01" IS NOT NULL 
UNION 
SELECT "name02" AS "nm" , "birthday" AS "birthdayall" From `tbl1` WHERE  "name02" IS NOT NULL
UNION 
...

and so on until you have all of the columns. Then, at the very end of that query, or maybe an overarching query you need to ORDER BY "birthdayall". In other words, the query above probably would need to be enclosed as follows:

SELECT "nm", "birthdayall"
FROM 
(
  SELECT "name01" ...
  FROM "tbl1"
  WHERE ...
  UNION 
  ...
) AS "subq"
ORDER BY "birthdayall"

I have not tested the latter queries, but the principle is sound.

(if this answers your question, please accept the answer by clicking the check mark () to the left)

[415flip]

---

thanks a lott Doug,

your answer from a year ago solved my problem.
(in this year, I didn’t work on this project for at least 50weeks)

I lack ability too understand the first part, but after using the second part of your post i succeeded.
the subsequence in this part did the trick:

SELECT “nm”, “birthdayall”
FROM
(
SELECT “name01” …
FROM “tbl1”
WHERE …
UNION
…
) AS “subq”
ORDER BY “birthdayall”

do I have to mark this thread as SOLVED?

thanks, greetings 415flip