Unable to update query (1 Viewer)

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
Whenever you have "many" of something, you need a subform. Use a subform to show the artists rather than a single combo. The artist field in the subform will be the combo you now have on the main form.

If you really need to see the word "duet", use a dcount() on the artists for the song. If the number is 2, it is a duet, 3 a quartet, etc.
I don't need to see the word "duet". I want to se who the two artists or more who sings the song.
I've tried using dcount() with the name of the song in the function but there are more song with the same name so dcount() didn't work.

Leif
 

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
The question first is whether the song is specific to an album or collection or whatever. If you have the ID for the song, can you trace it back to a specific origin? (I haven't looked at the diagrams that hard.) If so, then the next question is whether there is EVER a time where you would have two vocalists on a single identifiable song and NOT have either a duet or a named combo? And in the case of a named combo would you name the members individually anyway?

When I did my CD database some years back, I took the approach called entity-attribute-value (EAV) layout. My structure was more or less:

Recording (RecID, media format, album title, record label, serial number, year released, and a couple of other lesser details)

Selection (SelID, RecID as FK, OrdID as the position in the sequence of selections, title, duration in min:sec as a DATE field, a couple of other things) OrdID was 1, 2, 3, etc. representing 1st track, 2nd track, ...

Details (DetID, SelID as FK, Detcode i.e. what was represented, text value of same) This was the EAV table where <SelID> was the entity, <DetCode> was the attribute, and then the value was literal text.

DetailCodes( DetCode, name of thing being represented)

The detail codes were things like "Artist" "Group" "Composer" "Soloist" (used when "group" represented an orchestra) "Accompanist" "Cover Artist" (when I was feeling whimsical or the cover art was unusual), etc. The DetailCodes table included a "sort order" value that said put Artist, Group, or Orchestra first, Conductor second, Soloist third, etc. I had a hierarchy of presentation order.

To be honest, I didn't care that I had a duet, but I could have annotated that through the EAV table by having an attribute "DUET." That property would be present (with a blank value) if the song was a duet, but absent if it was a two-person group like "Hall & Oates." H&O billed themselves as a combo, not two independent singers in "duet" style. Case in counterpoint, Andy Bennett and K.D.Lang's album where they clearly were as a duet.

Note that this wasn't a "PURE" EAV because in a pure EAV, the title and duration that were on the "Selection" record would actually have been in the EAV table as two more attrbutes. But I made some placement decisions for report convenience.
I will look in to this and answer it soon.
Leif
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:43
Joined
Feb 19, 2002
Messages
31,175
I don't need to see the word "duet". I want to se who the two artists or more who sings the song.
That's why you use a subform.
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
I'll add the drew file to my sites downloads so it can be edited
Example.png
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
I don't need to see the word "duet". I want to se who the two artists or more who sings the song.
I Did post an image of the recordings screen that shows a subform with artists, It has a Contribution Field which is the AsID Field as in above image


This is the extention of the ERD I posted with reference to the track it is any earlier version
 

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
I will look in to this and answer it soon.
Leif
What I want to do with my database is to create a good overview of my vinyl records.
Since I use these in my radio programs, I also want to list which songs are included in the programs and be able to search the database for songs that I want to include in my future radio programs.
When I started making radio a little over two years ago, I flipped through my record trays and selected music for the programs. After a while I realized that it would have been good with some statistics. Then I started entering the songs in an Excel file. Song name, the artist and in which program was included in the file. It is this Excel file that is the basis of my database. There is no connection in the file to which vinyl record the song is on.
Now to your questions:
Each song has a unique ID and each artist / group also has a unique ID. Since in my Excel file there was a connection between the artist and the song, you could say that I can trace each song back to the artist, but not to the record.
In my record collection songs can be represented both on a single as on a LP and on a compilation record, etc. The song can also be done by other artists on various media.
I understand your idea of having the artists who do the duet as a couple in the artist table. But as I described it earlier in this thread, it will be a problem to see that particular song on the individual artist, which I think is more important. That's why I started entering the song twice, once to each artist.
Hall & Oates as you mention is a problem as they have done the most songs as a group, at least in my collection.
Thanks for all the help so far.
I wonder if I will be able to get what I want?
But how difficult can it be? It's just ones and zeros ...... ;)
Leif
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
I was a DJ For 10 years back in the 80's-90's My collection is some 70K Tracks collected over 30years I went from 7-12'' to CD's then finally on to my drives I store them on my onedrive account now.
Creating a playlist is simple
tblPlaylist
tblplaylistNames
tblPlayListAndName

The tblPlayListAndName is a many to many list just in case you want to reuse the list for another shows.
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
Here you go I only added two tables one for the show and second for the tracks to be played
I've also updated it for genre on the tracks
Example Playlist.png
 
Last edited:

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
Here you go I only added two tables one for the show and second for the tracks to be played
I've also updated it for genre on the tracks
View attachment 87687

Thanks @MickJav Looks a little bit like mine doesn't it?
Skärmklipp.PNG

But when i choose songs for the radio playlist how do I know if a song is a duet, like No More Tears (Enough Is Enough) with Donna Summer and Barbara Streisand or Guilty with Barbara and Barry Gibb?
There you see my problem.
Leif says goodnight.
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
No it's not like what I did study it and you will see where the differences are I'm not going to do it for you.
On my search screen I have a function that shows the types assigned to each artist look at kylie Minogue.
2020-12-21 (7).png
 

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
No it's not like what I did study it and you will see where the differences are I'm not going to do it for you.
On my search screen I have a function that shows the types assigned to each artist look at kylie Minogue.
View attachment 87691
Hi @MickJav
I meant that the part where you added for the programs was similar to mine, not the other tables and relationships.
I will definitely take a closer look at this. I'm beginning to get it. It would have been nice to look into your database.
I understand that the artists appear as it is shown in the image you added earlier with the song "Especially For You", but what does it look like if that song is in a playlist? It's similar to how it's displayed in my programs.
I would like to thank you for your patience as well.
Leif
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
The first program (DJ 2020) will be released In the next week or two, the music library (Apollo) will be released a month or so after as it's being taken from DJ 2020 and additional features added.
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
Apollo will include a playlist creator, DJ 2020's playlist is tied to an event
 

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
Apollo will include a playlist creator, DJ 2020's playlist is tied to an event
Hi again @MickJav
I'm trying to translate the ERD image (I now know what ERD means) that you shared here to my access database.
From what I understand, you also work in access, but I do not recognize the image.
What version of access do you have and what settings make the image look the way it does?
You can see what mine looks like in the thread.
My version of access is the one that comes with Microsoft 365 (file format 2016).

I have two questions on the ERD.
You have explained the table with AsID before and I understand why you have it.
But it does not refer to any other table, such as tblRecordingArtists.
Usually the artist that you enter in tblAs is already in your database and you risk double registering.
Is there any good reason why you did it this way instead?

Then I wonder about the connection between Records and Tracks.
A song can appear on several records. shouldn't you have a junction table between these?
Or are the songs registered several times in tblTracks? One for each occurrence on a record.

I am really looking forward to seeing your DJ2020 database.
Leif
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
There is a link bewteen tblas and tblArtistsAndRecords it's the contrubution combo in the recordings image

I use something I designed called the style manager where I have up to 10 styles which I can switch between at will.
All downloads from my site are members only but I think you created an account a while ago

Style Manager Download Page
I have since added two objects that use the style manager
Style Manager: Calendar
Style Manager: Calculator

Both of the above have standard versions

Hi again @MickJav

Then I wonder about the connection between Records and Tracks.
A song can appear on several records. shouldn't you have a junction table between these?
Or are the songs registered several times in tblTracks? One for each occurrence on a record.

I am really looking forward to seeing your DJ2020 database.
Leif
You are correct I have developed a way of doing it, I'm still testing the results thats why I'm holding the release of DJ 2020 But I kept it simple as I didn't think you would understand how it works or how to get it working, The way I did it was a simple way the complex way ends up like the image below
Heres: My company UTube channel I have just started
P.S. I changed the style lol
2020-12-22 (1).png
 
Last edited:

lodmark

Member
Local time
Today, 04:43
Joined
Jul 24, 2020
Messages
174
There is a link bewteen tblas and tblArtistsAndRecords it's the contrubution combo in the recordings image

I use something I designed called the style manager where I have up to 10 styles which I can switch between at will.
All downloads from my site are members only but I think you created an account a while ago

Style Manager Download Page
I have since added two objects that use the style manager
Style Manager: Calendar
Style Manager: Calculator

Both of the above have standard versions


You are correct I have developed a way of doing it, I'm still testing the results thats why I'm holding the release of DJ 2020 But I kept it simple as I didn't think you would understand how it works or how to get it working, The way I did it was a simple way the complex way ends up like the image below
Heres: My company UTube channel I have just started
P.S. I changed the style lol
View attachment 87704
Nice, more to look through.
My problem is probably more that I have not worked professionally with databases but have it more as a hobby and that I do not always understand the linguistics in the technical with databases. Then English is not my mother tongue, so there are also misunderstandings sometimes.
But I'm trying!
Leif
 

MickJav

Well-known member
Local time
Today, 03:43
Joined
Nov 28, 2005
Messages
2,431
I'm not a professional (I'm A Wannabe Pro lol) I still get stuck even after 25 years, you can learn a lot but looking at the stucture (Tables) Of others Databases.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:43
Joined
Feb 19, 2002
Messages
31,175
Yes, I know, but i am struggling with the query and how to display the result in my form.
I'm learning a lot o the way.......
Subforms are bound to their own query. The RecordSource of the main form should NEVER join to many-side tables. That will just cause the main form records to duplicate.
 

bastanu

Registered User.
Local time
Yesterday, 19:43
Joined
Apr 13, 2010
Messages
608

Users who are viewing this thread

Top Bottom