Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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 (image description) to the left)