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
;

 

[TFCJamieFay]

Maybe I need to explain the situation a bit more...

I have two tables, Activity and Lookup. Activity contains a Date/Time field called Activity_Time and the Key Field is Session_Key (along with other fields not used in this query). I have another table, Lookup. This contains two fields, Session_Key and Visitor_ID. I'm trying to create a query that will output Activity_Time, a distinct count of Session_Key and a distinct count of Visitor_ID. Normally, i would write something like this:

SELECT
     Activity.Activity_Time , 
     COUNT ( DISTINCT Activity.Session_Key ) AS Sessions ,
     COUNT ( DISTINCT Lookup.Visitor_ID ) AS Visitors

FROM Activity
     INNER JOIN Lookup
          ON Activity.Session_Key = Lookup.Session_Key

GROUP BY Activity.Activity_Time ;

...but Access doesn't like DISTINCT in aggregate functions, so I've tried the various workarounds to be found online and in the built-in help, but I can't seem to get the sub queries working.

Any help would be great.

Many thanks,

Jamie

 

[Joe4]

I am finding it a bit confusing, as it appears that you are trying to do two contrasting things with your Activity table at the same time.
What might might more sense is to post a small example of what data in each of the two tables might look like, and what the expected result of that sample would look like.

 

[TFCJamieFay]

Hi Joe4,

I'm trying to count the number of unique/distinct Session_Key's and the number of unique/distinct Visitor_ID's by hour. The output should look like this...

Activity_Time||COUNT ( DISTINCT Session_Key )||COUNT ( DISTINCT Visitor_ID )
01:00||100||65
02:00||99||63
03:00||101||61
etc...

My Activity table has the Activity_Time and Session_Key fields, whilst Lookup has Session_Key and Visitor_ID. A Visitor_ID may be assigned to more than one Session_Key within the Lookup table. The Activity table contains all actions for the given Session_Key, which means there may be one action per Session_Key or twenty.

Any help would be great as I'm snookered!

Thanks,

Jamie

 

[Joe4]

But can we see some examples of that data you are starting with (not just the expected results)?
Are all your times exactly on the hour, or can they be anytime and you are trying to group them to the nearest hour?

 

[TFCJamieFay]

Hi Joe4,

Tables are as follows:

Lookup: This table has two fields, Session_Key (CHAR 42) and Visitor_ID (CHAR 23). Both fields are formatted as Text but contain numeric values. Sample Session_Key = "100039463657134388714568665803811153484965". Sample Visitor_ID = "10003946123713438871379"

Activity: This table has six fields, Session_Key, (CHAR 42), Activity_Type_ID (CHAR 1), Category_ID (CHAR 255), Product_ID CHAR 255), Activity_Time (Date dd/mm/yyyy hh:mm:ss), Activity_Amount (CHAR 255).

The times are down to the second, but I'm looking to get the output grouped by hour. I'm not too fused about getting this grouping done in the SQL, I did try a CASE statement, but it didn't work. I tried a SWITCH statement, but Access said it was too complicated. The most important thing for me is to count the number of unique/distinct Session_Key's and Visitor_ID's, grouped by Activity_Time. If the grouping is rolled up to the hour, great stuff, but if not, I'll just pivot the output in Excel or something.

The Activity table hold information around what visitors to our website have done. This means that each Session_Key may have more than one record, with Activity_Type, Category_ID, Product_ID and Activity_Amount likely to change in each record. The Activity_Time field effectively records the Session start time, so is the same for each activity within the Session (despite it's name, not my choice BTW).

If you need anything else, just ask.

Thanks for all your help,

Jamie

 

[Joe4]

I don't think you understand what I am asking you for. I am a very visual person. I would like to see a realistic example, meaning post a small sampling of each data table (use dummy data if it is sensitive), and then post what the expected results from that example should look like. This will show me exactly what you are starting with, and exactly where you are trying to get to.

Like the old saying goes, a picture says a thousand words!

 

[TFCJamieFay]

Hi Joe4,

Apologies, let's try again!

Lookup Table
Session_Key||Visitor_ID
100039463657134388714568665803811153484965||10003946123713438871379
100039463657134388714568665803811153484123||10003946123713438871379
100039463657134388714568665803811153483214||10003946123713438871132
100039463657134388714568665803811153483541||10003946123713438871215
etc...

Activity Table
Session_Key||Activity_Type_ID||Category_ID||Product_ID||Activity_Time||Activity_Amount
100039463657134388714568665803811153484965||5||STATIC PAGE||HOME||02/08/2012 06:58:17||-
100039463657134388714568665803811153484965||5||STATIC PAGE||HOME||02/08/2012 06:58:17||-
100039463657134388714568665803811153484965||5||CLOTHING||CLOTHING||02/08/2012 06:58:17||-
100039463657134388713798665803811153484965||5||CLOTHING||BLUE DRESS||02/08/2012 06:58:17||-
100039463657134388713798665803811153484965||5||CLOTHING||CLOTHING||02/08/2012 06:58:17||-
100080054600134390512261680594626716954102||5||STATIC PAGE||HOME||02/08/2012 11:58:40||-
100080054600134390512261680594626716954102||5||DRESSES||DRESSES||02/08/2012 11:58:40||-
100080054600134390512261680594626716954102||5||PLAYSUITS||PLAYSUITS||02/08/2012 11:58:40||-

So we can see that Visitor 10003946123713438871379 (Visitor_ID) visited my website and their Session_Key was 100039463657134388714568665803811153484965. During Session 100039463657134388714568665803811153484965, they performed various actions and that the Session started at 06:58:17 on 02/08/2012. Also in the Lookup table we can see that this same Visitor (Visitor_ID 10003946123713438871379), visited a second time. If this Session was within the hour, you would need to count the Visitor_ID once and both Session_Key's.

Many thanks,

Jamie

 

[Joe4]

OK, we are getting a little closer. Based on the example data for each of the tables you just posted, how exactly would you like the resulting query to look (in tabular form, just like you did with the tables)? Please base it specifically on the data you have posted, so I can see the exact correlation between the sample data and expected output.

 

[TFCJamieFay]

In a perfect world I would like the times rolled up to hours, so the output would look like this:

Activity_Time||COUNT(DISTINCT Session_Key)||COUNT(DISTINCT Visitor_ID)
01:00||100||65
02:00||150||80
03:00||250||110
04:00||500||300
05:00||1000||650
etc...

But using the sample data, without rolling the times into hours it would look like this:

Activity_Time||COUNT(DISTINCT Session_Key)||COUNT(DISTINCT Visitor_ID)
02/08/2012 06:58:17||1||1
02/08/2012 11:58:40||1||1
etc...

Thanks