Firstly, Thank You for your reply. The information provided shows that my thought process here is not clear, and my inexperience with Base basics. I had hoped that by having an Artist Table with Artist ID and a Song Title Table with Song ID and Title, by placing the ID’s in a Junction Table this would match up the information. I thought by having this relationship in the Junction Table the ID’s from both Tables would autofill into the Junction Table. I ran a query (which I didn’t save) and the result came out as I had hoped, Artist ID, Artist, Song ID, Song Title. This because I manually typed the ID’s into the Junction Table. In the sample provided I only input info for the first 5 songs which belong to 10CC. Please excuse the Rookie error about the basics 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
If you already have an external table, wich contains Artist AND Song this can made usable and converted in a junction table with ids.
If your table with the songs is ordered by some criteria it may be possible to cheat a bit.
So, if you know the songs from Id 200 to 250 are from ABBA, it may be possible to set the artist-ids as a group.
Depends on the task you actually want to do…
Thanks for your reply I did make an earlier Table which has a seperate ID attributed to each artist and song. For instance ABBA may have 10 songs and the records show 0-9 all ABBA with all the song Titles. I couldn’t work out how to have ID 0, ABBA, song Titles 0-9 if that makes sense.
Do you still have that table? If so, please provide its format and possibly a sample.
Yes Ratslinger I still Have the Table mentioned above. https://drive.google.com/file/d/1rClziDAGD0MyqnhZao3VVS0NsAFdSK1j/view?usp=sharing
Is there a way to upload Sample Files straight to a post?
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
When posting there is a toolbar at the top of the post. There is an icon for Upload. See → This is the guide - How to use the Ask site?
Will have a look at your file.
Edit:
Quick look at file only shows 27 entries but your original post has over one thousand noted. Is this only a partial file?
Yes this is just a sample. The original has about 2,000 files
My test appears OK. Make sure you try this with a backup on hand (or two 
).
Using last sample and 70sMusic table, I creates tables for “Artist” and “Song”. Then used SQL to fill tables ( using Tools->SQL ).
Then used your “Junction” table from original post and another SQL statement to put the three tables together to be able to extract the ID fields for the junction table.
Saved the SQL Insert statements in Queries (cannot run from there). They are:
- ArtistInsert
- SongInsert
- JunctionInsert
Junction being done last. Again, do these from Tools->SQL.
70sMusic.odb (6.7 KB)
Thank You so much Ratslinger I sure do appreciate your helpful efforts. I now need to investigate with your sample how to make the form with the information I require. I’ll continue to play around with this and try to get the form design I require. Thanks again for your time and help.
Hi Ratslinger, Sorry for the bother. I tested your sample and everything worked as suggested. With confidence I decided to try your queries on the total database, and a few strange things happened.
The 70sMusic table is the same only with the full amount of songs. I copied the Junction Table with the Artist_ID and Song_ID. Created the Tables for Artist & Songs and all looked good. Copied the Queries into the query area copied the SQL queries and and ran them in the order you suggested. The Artist Query said executed successfully and the output showed all the Artists. The same for the Song Query executed successfully and the output showed all Songs. Did the same with the Junction Insert executed successfully, but 0 updated in the output. None of the Tables were updated.
I may have missed something but pretty sure I did as suggested. There were two extra queries not mentioned DistinctArtist (which appeared to be the same as ArtistInsert and the TestJoins wasn’t sure if I should run them? I did end up running Test Joins and it came up with this error message [1: Table not found in statement [SELECT “SID”, “AID” FROM ( SELECT “Song”.“ID” “SID”, “Song_Title”, “70sMusic”.“Artist”, “Artist”.“ID” “AID” FROM “Song”]
Hopefully you will know what the issue is. Many Thanks
Did you delete the existing records that were in it? Did you give it a different name - should be Junction.
My test again shows no problem:
I also ran the TestJoins query without issue. That and DistinctArtist were my testing leading up to the end product.
I don’t know what else I can provide unless you post your actual files which are problematic.
@Ringy9
I downloaded your database “70sMusic” and produced a solution.
I have not downloaded any of @Ratslingers uploads but as you have encountered issues I will make my solution available.
my solution uses a single query which is executed via menu:Tools>SQL.
your table “70sMusic” is the data source for 3 tables “tArtist”, “tSongs” and “tArtist_Song”.
--must be executed from menu:Tools>SQL
--creates 3 individual tables from the single table "70sMusic"
drop table "tArtist_Song" if exists;
drop table "tArtist" if exists;
drop table "tSongs" if exists;
select id, "Artist" into "tArtist"
from
(
select count(m2."Artist") id, m1."Artist"
from
(select distinct "Artist" from "70sMusic") m1
left 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 id, "Song_Title" into "tSongs"
from
(select id, "Song_Title" from "70sMusic");
alter table "tSongs" add primary key(id);
alter table "tSongs" alter id identity;
alter table "tSongs" alter "Song_Title" varchar(150) not null;
select a.id "ArtistID", b.id "SongID" into "tArtist_Song"
from (select * from "tArtist") a
join (select id, "Artist" from "70sMusic") b on a."Artist" = b. "Artist";
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;
A simple query to display artist and song:
–shows all artists and their songs
select a2."Artist", s."Song_Title" from "tArtist_Song" a1 join "tArtist" a2 on a1."ArtistID" = a2.ID join "tSongs" s on a1."SongID" = s.ID order by "Artist", "Song_Title"
Hi Ratslinger I will upload the full database as I can’t seem to get it to work. The Only thing I have noticed is that the SongInsert query appears to be Cut Off.(the word SELECT appears as CT) I tried to update to the complete word SELECT and save but it just goes back to CT. If you can please find the time to investigate further I would be truly grateful.
70’s_Music.odb (107.0 KB)
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.