Karaoke Scene's Karaoke Forums
http://karaokescene.com/forums/

Delete Query in Access
http://karaokescene.com/forums/viewtopic.php?f=1&t=37539		 Page 1 of 1
Author:  djdon [ Fri Jul 21, 2017 6:45 am ]
Post subject:  Delete Query in Access
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?
Author:  dsm2000 [ Fri Jul 21, 2017 7:58 am ]
Post subject:  Re: Delete Query in Access
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));
Author:  djdon [ Fri Jul 21, 2017 8:25 am ]
Post subject:  Re: Delete Query in Access
Thank you. That worked perfectly.
Author:  dsm2000 [ Fri Jul 21, 2017 9:01 am ]
Post subject:  Re: Delete Query in Access
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.
Page 1 of 1 All times are UTC - 8 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/