Query/SQL+VBA

[State Troopers]

Hi there,
I am in need of some assistance.
I will try my best to describe my situation, if you have any questions
please do not hesitate to ask.

Alright, I have a table like this:
Table name:tblMIX

SONG_ID WEEK_ID
3 Doors Down Away From The Sun 19
3 Doors Down Away From The Sun 22
3 Doors Down Away From The Sun 23
3 Doors Down Away From The Sun 25
3 Doors Down Away From The Sun 26
3 Doors Down Away From The Sun 27
54-40 Since When 25
Aerosmith Angel 27

I have a database which stores the songs played on a local radio station.
What I have right now is that every week on a tuesday it emails me the list
of all songs that have played for 1 week.
What I need to be able to do is for it to email me only the songs of the
current week(27...for this week) which have only played once.
For instance, using the above table:
Away from the sun would not email me, since it has played on more than 1 week.
54-40 Since When would NOT be emailed since it was played several weeks
go(even though it has only played once)
Aerosmith Angel would be emailed, since this is the first week it has played
and it is the current week of the calander year.

I have several questions as to how I can accomplish this.
Is it possible to get the current week value in an SQL statement?
If not, is it possible to "compare" twoo queries? I was thinking of using my
original query(which tells me how many weeks total the song has been playing)
and then compare that (only if the song has played once) to the query that
lists which weeks the song has played.

Basically the end query would determine if the song has only played once,
and if it was played in week 27 - it needs to meet both criteria.

thanks.
-State

 

[Stefan Hoffmann]

hi,

If not, is it possible to "compare" twoo queries? I was thinking of using my
original query(which tells me how many weeks total the song has been playing)
and then compare that (only if the song has played once) to the query that
lists which weeks the song has played.
Basically the end query would determine if the song has only played once,
and if it was played in week 27 - it needs to meet both criteria.

SELECT SONG_ID
FROM tblMIX
WHERE SONG_ID IN (
SELECT SONG_ID
FROM tblMIX
GROUP BY SONG_ID
HAVING Count(*) = 1
)
AND WEEK_ID = DatePart("ww", Now())

mfG
--> stefan <--

 

[State Troopers]

Brilliant.
Thanks.

hi,


SELECT SONG_ID
FROM tblMIX
WHERE SONG_ID IN (
SELECT SONG_ID
FROM tblMIX
GROUP BY SONG_ID
HAVING Count(*) = 1
)
AND WEEK_ID = DatePart("ww", Now())

mfG
--> stefan <--

 

[State Troopers]

Im getting an error:

Invalid Memo, OLE, or Hyperlink Object in subquery "SONG_ID"


?

Cheers
-State

 

[Stefan Hoffmann]

hi,

Im getting an error:
Invalid Memo, OLE, or Hyperlink Object in subquery "SONG_ID"
?

SONG_ID must not be of these types. Change the type of SONG_ID to Text.



mfG
--> stefan <--

 

[State Troopers]

Sweet deal.

Thanks again Stefan.

hi,


SONG_ID must not be of these types. Change the type of SONG_ID to Text.




mfG
--> stefan <--