SQL Syntax Error - Assistance please

[BristolGarry]

Hi Folks - I am trying to create a query that will return the number of visitors we have each month, as well as the number of unique visitors in that same month. i.e. In April we had 135 visitors, 85 of which were unique (some visitors come in multiple times.) The SQL that I have in my query so far is returning the number of visitors per month, but I am not able to nail down the syntax for the "unique ID" part.



This is what I have at the moment:

SELECT DISTINCTROW Format$([Daily Visits Table].[Visit Date],'mmmm yyyy') AS [Visit Date By Month], Round(Sum([Daily Visits Table].TimeSpent)/60,2) AS [Sum Of TimeSpent], Count(*) AS [Count Of Daily Visits Table], (SELECT COUNT(*) AS IDCount
FROM ((SELECT DISTINCT ID FROM Daily Visits Table) AS DistinctID)) AS DistinctIDs
FROM [Daily Visits Table]
GROUP BY Format$([Daily Visits Table].[Visit Date],'mmmm yyyy'), (SELECT COUNT(*) AS IDCount
FROM ((SELECT DISTINCT ID FROM Daily Visits Table) AS DistinctID)), Year([Daily Visits Table].[Visit Date])*12+DatePart('m',[Daily Visits Table].[Visit Date])-1;

When I run this I am getting the error message: "Syntax error in query expression "

(SELECT COUNT(*) AS IDCount FROM ((SELECT DISTINCT ID FROM Daily Visits Table) AS DistinctID)),"

What am I doing wrong here?

Thanks!

BristolGarry

[ranman256]

If you use a query ,you won't get any syntax errors.

[Micron]

This is why we repeatedly inform people that you should NOT use special characters (except maybe for underscore) or have spaces in object names. You will get caught eventually.

[Daily Visits Table], not Daily Visits Table
What I don't get is, are you writing this out? That would explain it. If you're using the query builder, then the missing brackets are a puzzle. If you must write your own sql, then put it into sql view of the query builder when it doesn't work. The offending portion is usually highlighted. In this case, Access would likely fix it automatically.

[CJ_London]

In addition to the other comments, since your subquery is referencing the same table as the main query, you need to differentiate it by aliasing it otherwise it will look at the main table. Also looks like you have made it more complex than it needs to be

(SELECT COUNT(*) FROM (SELECT DISTINCT ID FROM [Daily Visits Table] AS Tmp)) AS DistinctID

What I don't get is, are you writing this out?

its a subquery - sql doesn't check the syntax until it is executed

not tested but you are not grouping by the distinctID subquery, instead by a calculation on that subquery. May work, may not

[Micron]

Never mind. I had my threads mixed up.