You have multiple issues. For one ID’s in tables were not auto increment. There were other issues and did not bother with all. Instead of taking time to resolve, use the SQL from @cpb above. My quick test shows that works.
Thank you for your Kindness. I will do as suggested. Your input has been invaluable.
Thank you cpb for your input. Your solution has worked for me and is very helpful
@Ringy9,
Please NOTE:
I created a table of unique artists.
if 2 artists perform the same song then song ID is unique but “Song_Title” is duplicated because I took the IDs from your table “70sMuxsic”.
song titles should not be duplicated so please use the code below, it’s been modified and “Song_Title” is now unique which means that even if multiple performers sing the same song it will only appear once in the table “tSong”.
I have a CD database which contains 808 tracks of which 5 have duplicate titles.
there are 3 tables “tArtist”, “tAlbum” and “tTracks”. “tAlbum” has the foreign key “ArtistID” and “tTracks” has the foreign key “AlbumID”. input is simple using 3 table controls.
sometimes it’s ok to bend the rules! (a handful of duplicates).
if I used a junction table to link each artist with one of 803 songs then inputting fresh data would be very painful because I would first need to check if the title exists and if not then insert a new record.
a junction table may not be the most practical option, it’s just a thought.
--must be executed from menu:Tools>SQL
--creates 3 individual tables from the single table "70sMusic"
--artists and songs are both unique i.e. no duplicates in tables "tArtist" or "tSongs"
drop table "tArtist_Song" if exists;
drop table "tArtist" if exists;
drop table "tSongs" if exists;
select count(m2."Artist") id, m1."Artist" into "tArtist"
from
(select distinct "Artist" from "70sMusic") m1
join
(select distinct "Artist" from "70sMusic") m2
on m1."Artist" >= m2."Artist"
group by m1."Artist"
;
alter table "tArtist" alter id set not null;
alter table "tArtist" add primary key(id);
alter table "tArtist" alter id identity;
alter table "tArtist" alter "Artist" varchar(100) not null;
select count(s2."Song_Title") id, s1."Song_Title" into "tSongs"
from
(select distinct "Song_Title" from "70sMusic") s1
join
(select distinct "Song_Title" from "70sMusic") s2
on s1."Song_Title" >= s2."Song_Title"
group by s1."Song_Title"
;
alter table "tSongs" add primary key(id);
alter table "tSongs" alter id identity;
alter table "tSongs" alter "Song_Title" varchar(150) not null;
select distinct a.id "ArtistID", s.id "SongID" into "tArtist_Song"
from
(select "Artist", "Song_Title" from "70sMusic") m
join "tArtist" a on m."Artist" = a."Artist"
join "tSongs" s on m."Song_Title" = s."Song_Title"
;
alter table "tArtist_Song" add primary key("ArtistID", "SongID");
alter table "tArtist_Song" add foreign key ("ArtistID") references "tArtist" (id) on delete cascade on update cascade;
alter table "tArtist_Song" add foreign key ("SongID") references "tSongs" (id) on delete cascade on update cascade;Thank You again so much. This database has nearly 2000 70’s songs and I’m adding all the time, some of which are as you say duplicate song titles. I am just starting to work on the forms side of things, and I need to figure out how to make additions to the database in an easier manner. If you haven’t guessed already this is my first venture in creating a database and it’s turning out quite a large learning curve 
Play with simple dummy databases having a few one-to-many relations and some many-to-many relation.
The best tool to start with is a sheet of paper where you sketch lists of columns representing the tables and how they should be related to each other (something like the relations window).
In Base, I always start with a blank database where each column has the right type and where relations are not set in the beginning. An empty database structure is much easier to modify than one with test data because the data can be contradictive to new rules. Before adding new columns to a table, it is best practice to remove all test data, modify the structure and fill in new test data into the new structure.
If you are confident that your tables and columns are suitable to take all the data, remove dummy data and establish relations. Relations enforce referencial integrity (reject title IDs in a playlist where there is no such song title ID in the list of songs). Relations also improve lookup performance. While changing the database structure and while working with dummy data, relations can be in your way.
Integer Auto-IDs are the correct primary keys for the vast majority of lists. Base never creates auto-IDs when you create new tables by copy&paste. This is why I always create a new empty table first (with auto-ID) and then fill in any data. Junction tables should have a compound primary key if you want to avoid duplicate assignments (each article occurs only once on the same invoice).
How to fill in test data? Calc is my favourite tool to generate random numbers, dates, times, booleans and dummy strings for testing. A Calc range copied into an existing database table always gives proper results IF (and only if) the rules of your database structure are fulfilled by the pasted data.
How to fill existing “real world” data from Calc into a blank database structure? In order to avoid failing or incomplete import, do the following tests in the spreadsheet and edit your data on sheet until they fulfill all the rules of your database structure:
=MAX(LEN(column)) [entered with Ctrl+Shift+Enter as array formula] returns the max. length of a text column for the right size of a VARCHAR field.
=COUNTBLANK(column) counts blanks. There should be no blanks in a mandatory column.
=COUNTA(column) counts any values text or number.
=COUNT(column) counts numbers
=COUNTA(column)=COUNT(column) TRUE if there are only numbers.
=ROWS(column)=COUNT(column) TRUE if there are only numbers with no blanks.
=MIN/MAX(column) min and max values of a column to determine the right numeric type.
=N(MATCH(value;column;0)) returns 0 if value does not occur in some other table’s column (test for referencial integrity).
“column” should be a reference like A$2:A$1001 if you have column labels in row #1 and 1000 records below the labels.
=COUNTIF(value;same_column)>1 returns TRUE if there are duplicates in the same column.
Date/Time numbers need to be formatted accordingly. ISO date/times are allowed to be text. Text value “2021-09-17 23:45:59” fits properly into a database column of type TIMESTAMP, but not text value “9/7/2021 11:45 am”
Column types should be as small as possible and as large as necessary.
Thank you for the sage advice Villeroy, I will certainly take heed of this for the future.
@cpb Given that I have used your method for creation of my database, and your last thought about the Junction table, do you think a searchable and editable form can be produced from this Method? I have just made a simple form from the Artist Table and a subform from a simple query with the two ID’s and the Artists and songs. That form is fine for me, with the exception it doesn’t allow me to update. Please if you could recommend a solution.
@Ringy9 the database we created is the correct model for purpose and adheres to the principles of relational database design.
the model I hinted at does not conform to the principles of relational database design but is a little more user friendly.
the two models are not compatible, their structures differ.
it is of course possible to create a database which reflects either model but I think it’s best to first see how you manage with what you have.
I am uploading the database which we constructed by extracting data from your table “70sMusic”. I have added one form.
NOTE: both “tArtist” and “tSongs” contain more records than their respective forms can display so make sure to move their record pointers to or beyond last record before using (hit ‘+’ at bottom of grid).
you can add, edit or delete records to/from the tables “tArtist” or “tSongs”.
after adding, editing or deleting records to/from the table “tSongs” make sure to hit the button ‘After Add-Edit…’ which refreshes the junction form.
you can also add, delete records to/from the junction table “tArtist_Song”.
junction table:
the junction table is a sub-form of the Artists form, this acts as a filter so that only those songs assigned to the currently selected artist are visible in the table control.
you add a song via a list box in the table control.
i recommend using the mouse with the list box in this single record table control.
double click the list box.
to search the list press ‘F’ and the list scrolls to the first or next title which begins with ‘F’.
press ‘S’ then quickly press ‘T’ and the list scrolls to the first title which begins with ‘ST’.
it will soon become second nature.
as you scroll through the list the selected item appears next to the edit icon.
hit TAB or ENTER to save.
if you do not wish to save then hit the UNDO button, pressing ESCAPE may not remove the selected content and can be disconcerting.
Music1.odb (297.3 KB)
@cpb Oh Thanks so much. I have just added a further 44 records using your form which is so user friendly and intuitive. Firstly I see you have created some SQL queries is that where the form comprises of, or have you also added some design features. Secondly do you think it is worthwhile or even necessary to add a command that also updates the original 70sMusic Table. I am truly grateful for your efforts and I can certainly do what I need to do as is. Thank you kindly
@Ringy9 it’s good to hear that you are able to get along with our creation. 
the 70sMusic Table is now redundant.
delete the queries qlArtist (never used) and qCreate (now redundant).
qArtistSongs shows every artist and all of their songs.
ql_Song is the data-source for our single list box.
qArtistSong_f is the data-source for the inner sub-form sArtist_Song.
the table tArtist_Song contains 2 fields ArtistID and SongID both of which are integers.
using the query in preference to the table enables us to sort the songs in alphabetical order.
you can explore the form structure by hitting ‘F5’ (opens form navigator) when in edit mode.
even though this is a very simple database its development still requires a degree of knowledge/application/planning which many fail to achieve.
most of the wizards in Base are next to useless, buggy and restrictive.
make sure to make frequent backups of your embedded database because sooner or later it will fail with possible total data loss.
if/when you feel comfortable with what you have it would be wise to split the database and upgrade your version of hsqldb.jar which is around 14 years old.
I think that this thread is now dead, good luck and continue to persevere.
EDIT:
‘F5’ (opens form navigator)
SO SORRY @Ringy9 I got this wrong you have to hit the icon on the toolbar.
1 Like
@Ringy9,
I am unable to edit my previous comment hence this reply.
I found the time to look over the database we built and noticed a glaring error in the query qArtistSong_f
please replace with:
SELECT * FROM “tArtist_Song” ORDER BY ( SELECT “Song_Title” FROM “tSongs” WHERE “ID” = “tArtist_Song”.“SongID” )
@cpb Copied Music 1 and renamed Music 2. Deleted Query qArtistSong_f and replaced with new code. Ran Query in Tools>SQL and got error message.
When I went into the Form itself also recieved an error message
I will Attach new Music 2 database
Music2.odb (307.0 KB)
Please note the doubled “double-quotes”.
It seems you have copied the statement from here, but the quotes were replaced by typographic quotes. So Base thinks this characters belong to the name and puts another pair around.
So the query fails, because there is no table/column name with inner quotes.
Remove the inner quotes in the command after pasting (or rename your tables and columns, but I would not recommend this…)
@Wanderer Thanks for that Info, it appears to be working normally. I did check each query, but the double, double quotes eluded me.
@Ringy9
we use Tools>SQL in order to define(CREATE, ALTER, DROP etc.) or manipulate(INSERT, UPDATE, DELETE etc.) the back-end database, HSQLDB 1.8.0.10 embedded in our case.
the Base main window has 4 icons which represent database objects Tables, Queries, Forms and Reports.
TABLES (and VIEWS) are back-end (actual database) objects, saved and stored as such.
Queries, Forms and Reports are front-end objects and are stored within the odb.
all you have to do is:
- copy the new code.
- right click the query “qArtistSong_f” and select ‘Edit in SQL View’.
- hit (Ctrl + A). hit Delete.
- paste the new code and save.
- save the odb.
https://forum.openoffice.org/en/forum/viewtopic.php?f=100&t=40444
The recipe is always the same:
Main form: one of the two lists.
Subform: the junction table with a listbox in a table control where the listbox comprises the items of the other list.
Thank you for the link and of course your reply. I will look further into your information regarding a listbox and table control in a subform. I have not come across the listbox in my reading so far, but will be sure to do so. Thanks again.
Version: 7.1.5.2 (x64) / LibreOffice Community
Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5
CPU threads: 12; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-AU (en_AU); UI: en-US
Calc: threaded
The example document attached to the above linked topic has a many-to-many relation between things and persons and a one-to-many relation between animals and persons. The forms let you access all things related to a person, all persons related to a thing, the one animal that is related to a person and the persons that are related to an animal species.