Solved Unable to update query

[Dreamweaver]

I'll add the drew file to my sites downloads so it can be edited

 

[Dreamweaver]

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]

That's why you use a subform.

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.......
Leif

 

[lodmark]

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

 

[Dreamweaver]

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.

 

[Dreamweaver]

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

 

[lodmark]

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?

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.

 

[Dreamweaver]

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.

 

[lodmark]

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

 

[Dreamweaver]

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.

 

[Dreamweaver]

Apollo will include a playlist creator, DJ 2020's playlist is tied to an event

 

[lodmark]

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

 

[Dreamweaver]

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

https://www.youtube.com/channel/UCgHifeCp9fe4s1qZMjdPXJw

P.S. I changed the style lol

 

[lodmark]

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

https://www.youtube.com/channel/UCgHifeCp9fe4s1qZMjdPXJw

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

 

[Dreamweaver]

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.

 

[bastanu]

If you don't want to use a subform as Pat suggested (probably the best approach) you can use the "concatenate" function to put togheter all the names in one record, you should do it in the form in an unbound control if the record source returns more than one (current) record.

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

http://forestbyte.com/vba-code-samples/ (JoinFromArray).

Cheers,
Vlad

 

[lodmark]

If you don't want to use a subform as Pat suggested (probably the best approach) you can use the "concatenate" function to put togheter all the names in one record, you should do it in the form in an unbound control if the record source returns more than one (current) record.

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

http://forestbyte.com/vba-code-samples/ (JoinFromArray).

Cheers,
Vlad

Thanks @bastanu
I have looked at that function and it works under certain conditions, like using a query because the two subjects in the where part must be in the same query or table.
Then it becomes a problem because I have to compare the names of the songs to find the artists and one song can be made by several artists without being a duet.
I have not yet found a way to pick out unique values here. Except when I create a junction table between Artist and Song (music).
But the work is progressing.
Leif

 

[Dreamweaver]

The only time I would use concentrated Values is:

1. The results Of A Search on the results screen "Used for visual Aid"
2. On A Report

 

[lodmark]

Why do you not want to use a subform? No code is required when you take advantage of Access

Hi @Pat Hartman
I do not know what gave you the impression that I do not want to use subforms.
The form I am struggling with contains a subform. It is the question that populates the subform that is the problem after I realized that I need to redo in my table relationships and create a junction table between artist and song (music).
My first post in this thread tries to explain this. The main form I work with (frm_record2) retrieves data from one query and then retrieves additional data to a subform with another query and it is this query that I can not add entries to with the new table and the new relationships.
You will find the database in my first post, feel free to look there.
Leif

 

[Dreamweaver]

What you need for your Recordings subform is whats called (I think I read that somewhere lol) is an auto lookup Query Like below

Which is what I use for my artists and recordings to show all recordings for the selected Artist

SELECT tblArtistsAndRecords.RecordID, tblArtistsAndRecords.ArtistID, tblRecords.Title, tblRecords.FormatID, tblRecords.Released, tblArtistsAndRecords.Main
FROM tblRecords INNER JOIN tblArtistsAndRecords ON tblRecords.RecordID = tblArtistsAndRecords.RecordID
ORDER BY tblRecords.Released;