Syntax Error

[TFCJamieFay]

Hi all,

Can anyone see why this query is hitting a syntax error (missing operator)? I've added as many square brackets as possible, but still there's a problem!

It seems to point at my joins, I know DISTINCT queries are a nightmare in Access, but it all looks OK to me. Any help would be great.

Cheers,

Jay

SELECT 
     [Activity].[Activity_Time] AS Session_Start_Time ,
     COUNT ( [qryVisitor].[Visitor_ID] ) AS Unique_Visitors ,
     COUNT ( [qrySession].[Session_Key] ) AS Sessions

FROM [Activity] 
     LEFT JOIN Lookup
          ON [Activity].[Session_Key] = [Lookup].[Session_Key]

     INNER JOIN ( SELECT DISTINCT [Session_Key], [Visitor_ID] FROM [Lookup] ) qryVisitor
          ON [Activity].[Session_Key] = [qryVisitor].[Session_Key]

     INNER JOIN ( SELECT DISTINCT [Session_Key] FROM [Activity] ) qrySession
          ON [Activity].[Session_Key] = [qrySession].[Session_Key]

GROUP BY 1

ORDER BY 1 ASC
;

 

[Joe4]

What is the logic for hours rolling? Always roll up to the next hour? Roll to the nearest hour?

 

[TFCJamieFay]

If I was writing a CASE statement, I would use the following:

CASE
WHEN Activity_Time <= 01:00:00 THEN '01:00'
WHEN Activity_Time <= 02:00:00 THEN '02:00'
WHEN Activity_Time <= 03:00:00 THEN '03:00'
etc...

Thanks Joe4

 

[Joe4]

OK, I think I got it to do all you want, including the Time rounding. I broke it into a series of three queries, as it gets a little complicated. You can always mess around with it to try to nest all three into one if you really like, though I really don't see the need to do so.

So, the first query rounds up the time and gets our distinct count of Session_Keys:

Query1

SELECT Time_Group.Rounded_Activity_Time, Count(Time_Group.Session_Key) AS Session_Key_Count
FROM (SELECT 
   DateAdd("s",0-Second([Activity_Time]),IIf(Minute([Activity_Time])>0,DateAdd("n",60-Minute([Activity_Time]),[Activity_Time]),[Activity_Time])) AS Rounded_Activity_Time, 
   Activity.Session_Key
FROM 
   Activity
GROUP BY 
   DateAdd("s",0-Second([Activity_Time]),IIf(Minute([Activity_Time])>0,DateAdd("n",60-Minute([Activity_Time]),[Activity_Time]),[Activity_Time])), Activity.Session_Key
)  AS Time_Group
GROUP BY Time_Group.Rounded_Activity_Time;

The second query rounds up the time and gets our distinct count of Visitor_IDs:

Query2

SELECT
   Time_Group.Rounded_Activity_Time,
   COUNT(Time_Group.Visitor_ID) as Visitor_ID_Count
FROM
(SELECT 
   DateAdd("s",0-Second([Activity_Time]),IIf(Minute([Activity_Time])>0,DateAdd("n",60-Minute([Activity_Time]),[Activity_Time]),[Activity_Time])) AS Rounded_Activity_Time, 
   Lookup.Visitor_ID
FROM 
   Activity
INNER JOIN
   Lookup
ON
   Activity.Session_Key=Lookup.Session_Key
GROUP BY 
   DateAdd("s",0-Second([Activity_Time]),IIf(Minute([Activity_Time])>0,DateAdd("n",60-Minute([Activity_Time]),[Activity_Time]),[Activity_Time])), 
   Lookup.Visitor_ID
) as Time_Group
GROUP BY
   Time_Group.Rounded_Activity_Time;

So, then we just need to link these two queries together to get out final results:

Query3

SELECT 
   Query1.Rounded_Activity_Time, 
   Query1.Session_Key_Count, 
   Query2.Visitor_ID_Count
FROM 
   Query1 
INNER JOIN 
   Query2 
ON 
   Query1.Rounded_Activity_Time = Query2.Rounded_Activity_Time;

 

[TFCJamieFay]

Thanks Joe4, that's perfect! Sorry it took so many attempts for you to get the info you needed from me, I appreciate your patience.

Thanks again,

Jamie