I have a members table that’s populated. When first started I put everything n the table but it is very ;clunky’ and I’m sure not good practice. I have 11 groups that people can belong too (only one). I want to remove the groups from the Members table and put them into groups table linking by ID (primary to foreign). Now to populate the groups table then remove the groups from members. I’m not happy writing Macros yet but am trying to learn SQL so I would like to do it that way. Any advice would be appreciated (Libre Base Hsqldb embedded)
Thanks
Phil
-
Create a query.
SELECT DISTINCT "groups" FROM "tbl_members"
where “members” should be the name of your existing table. -
Save the query, right mouse click on the icon, copy.
-
Paste the query in the folder for tables. In wizard choose a new table name (“tbl_groups”)and also ID as field for new created primary key.
-
Switch to this table, open the table and have a look at “ID”. will be Integer, but won’t be Auto Value. Change this - will work in HSQLDB, not when using Firebird.
-
Open table “tbl_members” for editing, not for input data.
-
Add a field like “groups_ID” in “tbl_members”. Must be the same type as “tbl_groups”.“ID” → Integer.
-
Open Tools → SQL.
UPDATE "tbl_members" AS "a" SET "groups_ID" = (SELECT "ID" FROM "tbl_groups" WHERE "groups" = "a"."groups") -
Open “tbl_members” for editing. Now you could delete the field “groups”, because there is fille another field with “groups_ID”. Close table editing.
-
Switch to Tools → Relations and connect “tbl_groups”.“ID” with “tbl_members”.“groups_ID”. Save the relation.
If you won’t change anything in the defined connection you couldn’t delete a group, which is used in “tbl_members”.
1 Like