SQL help!

Pablo · Post by **Pablo** » Wed Mar 10, 2004 11:47 pm

I'm trying to restore the Play Counter (in case it has been changed) by looking at how many records are in the Played table with each given idSong. I'm able to use COUNT and GROUP BY to write a query of the ids next to how many times they were played. For instance,

Code: Select all

  SELECT Songs.ID, timesPlayed
  FROM Songs, [SELECT Played.idSong as SID, Count(Played.idSong) AS timesPlayed  FROM Played GROUP BY Played.idSong]. AS subq
  WHERE Songs.Id =subq.SID;

But I was unable to use this info to update the PlayCounter field in the Songs table.

Any ideas?

Pablo

Pablo · Post by **Pablo** » Thu Mar 11, 2004 7:36 am

I figured it out. JET SQL seems quite restrictive in the type of subqueries it allows, but I solved the problem by creating a temporary table to play the role of the subquery I needed.

Pablo

roylayer · Post by **roylayer** » Thu Mar 11, 2004 9:00 am

I was thinking it would be something like that. I am more familiar with Oracle SQL (I do Access queries through its GUI design mode.), so I couldn't really offer any suggestions. Glad you figured it out!

Post by **Lowlander** » Thu Mar 11, 2004 10:02 am

That is a good tip for Access programming. Create the queries in the Access interface (graphical) and show the code when you are done. This code you can use in your programs to access Access.