Query tiny issue

[tamar hirshfeld]

PARAMETERS report_header Text ( 255 );
SELECT [test db ab behav].[USER NAME], [test db ab behav].[SERVER NAME], [test db ab behav].date, [db ab hi-low].user, [db ab hi-low].server, [db ab hi-low].low_Event_no, [db ab hi-low].hi_event_no, count(*) AS event_count
FROM [test db ab behav], [db ab hi-low]
WHERE [test db ab behav].[USER NAME]=[db ab hi-low].user And [test db ab behav].[SERVER NAME]=[db ab hi-low].server and [event_count] >= [db ab hi-low].low_Event_no
GROUP BY [test db ab behav].[USER NAME], [test db ab behav].[SERVER NAME], [test db ab behav].date, [db ab hi-low].user, [db ab hi-low].server, [db ab hi-low].low_Event_no, [db ab hi-low].hi_Event_no;

my query works when I take the red part out !!!! but I need it (-:

 

[plog]

Criteria on aggregate data needs to be in the HAVING clause, not the WHERE.

http://www.w3schools.com/sql/sql_having.asp

Also, I don't think you can reference the alias of the field (event_count) from the SELECT clause but have to use the function call itself (COUNT(*))

 

[tamar hirshfeld]

Plog i'm extremely thankfull ! Ill try it and let you know. Im quite slow since im a beginner. Tamari

 

[tamar hirshfeld]

I assume it's working. Again, thanks so much !!!!