KARAOKE SCENE MAGAZINE ONLINE! - Delete Query in Access Public Forums Karaoke Discussions Karaoke Scene's Karaoke Forums Home | Contact Us | Site Map  

Karaoke Forums

Karaoke Scene Karaoke Forums

Karaoke Scene

   
  * Login
  * Register

  * FAQ
  * Search

Custom Search

Social Networks


premium-member

Offsite Links


It is currently Thu Mar 28, 2024 8:58 am

All times are UTC - 8 hours





Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Delete Query in Access
PostPosted: Fri Jul 21, 2017 6:45 am 
Offline
Super Poster
Super Poster

Joined: Fri Jun 03, 2011 8:11 am
Posts: 846
Location: Ocean County, Jersey Shore
Been Liked: 197 times
Karma. I'm trying to delete singers' names that have no history. Can someone help me write a query in Access that will accomplish this? I reached out to Mr. Bob and of course he refused to help me because of how I worded my question.

If I open karmadb.mdb in access, is there a query I can design and run to remove singers' names that don't have associated songs with them?

Yes there is.

Can you send me the update query please?

No. The question you asked was " is there a query I can design...", and yes there is a query that YOU can design.

Ya, thanks for the help, JO.

I'm stuck here:

DELETE Singers.singer
FROM Singerhistory INNER JOIN Singers ON Singers.Singer = Singerhistory.Singer
WHERE (((SingerHistory.singer) Is Null));

This doesn't work. I get an error message saying 'Specify the table containing the records you want to delete.' Which should be 'Singers'.

Anyone?

_________________
DJ Don


Top
 Profile Singer's Showcase Profile 
 
PostPosted: Fri Jul 21, 2017 7:58 am 
Offline
Super Poster
Super Poster

Joined: Sat Nov 01, 2014 8:41 am
Posts: 682
Been Liked: 259 times
Bob at his finest!

Here you go - This will delete all singers with no song history.



ALWAYS MAKE A COPY OF YOUR Karma.mdb before you run ANY queries!

DELETE Exists (SELECT *
FROM SingerHistory
WHERE SingerHistory.Singer = Singers.Singer), *
FROM Singers
WHERE (((Exists (SELECT *
FROM SingerHistory
WHERE SingerHistory.Singer = Singers.Singer))=False));


Top
 Profile Singer's Showcase Profile 
 
PostPosted: Fri Jul 21, 2017 8:25 am 
Offline
Super Poster
Super Poster

Joined: Fri Jun 03, 2011 8:11 am
Posts: 846
Location: Ocean County, Jersey Shore
Been Liked: 197 times
Thank you. That worked perfectly.

_________________
DJ Don


Top
 Profile Singer's Showcase Profile 
 
PostPosted: Fri Jul 21, 2017 9:01 am 
Offline
Super Poster
Super Poster

Joined: Sat Nov 01, 2014 8:41 am
Posts: 682
Been Liked: 259 times
Deleting the singers with no history is the easy part.

The hard part is finding and reassigning singer history songs to singers who have been entered many times with many different name spellings.

In Bob's world (where everyone and everything is perfect, and children gather rainbow dust) this would never be a problem but somehow in the real world it does happen (Especially when you are not the only KJ).

Example:

Toni
Tony
Toni T
Toni T
Toni Trumpee
Tony Trumpee
TT
Tony da Man

These could all be different people or they could all be the same singer.

Many times the variants will only have one or two songs and you can see that they have been sung at other times by someone with a very similar name.

You can simply change the name in the singer history to the "Real" singer's name tor these one or two songs.

Once the variant singer's one or 2 songs are reassigned, you can run the delete query I posted above and that variant singer will be gone since he no longer has any songs in the singer history.

The key point here is that the songs were sung and there should still be a record of that regardless of who sang it and regardless of whether I still own that song or the song has been pulled for legal reasons. The song WAS sung and I want a record of it so - leave my data alone Bob!

Why not just delete the variant guy's two songs from Singer History?

It's not just the Singer History . . . It's also the SONG History of how many times that particular song has been sung by ANYONE. It's a record of all the songs sung at your show each night.

There will be some variant singer songs that you just plain can't reconcile


For these I keep a singer with the name "UNKNOWN" in my Singer List. If I can't reconcile a song in the singer history to a singer, I reassign the song to the UNKNOWN singer.


Top
 Profile Singer's Showcase Profile 
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 8 hours


Who is online

Users browsing this forum: No registered users and 767 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Powered by phpBB® Forum Software © phpBB Group

Privacy Policy | Anti-Spam Policy | Acceptable Use Policy Copyright © Karaoke Scene Magazine
design & hosting by Cross Web Tech