Can you re-write SQL for Access

[Doogie]

I need the following code to work for MS Access. It is writen for a SQL
database. Does anybody know how to make it work. I am stumped? Specifically the
count(distinct tracker_id) as sessions. I get an error message of Syntax error
(missing operator) in query expression 'count(DISTINCT 'tracker_id')

<cfquery name="GetTotal" username="#dbusername#" password="#dbpassword#"
datasource="#datasourceR#">
select
count(*) as hits,
count(distinct tracker_id) as sessions
from #SiteTracker# with (#DBFlags#)
where Convert(datetime, datetimestamp) >= #DateFrom#
and Convert(datetime, datetimestamp) <= #DateTo#
</cfquery>

[mxstu]

I don't think Access supports COUNT(DISTINCT FieldName).

[MikerRoo]

mxstu is right. Access does not support Count(Distinct).
It also does not have the Convert() function and will probably barf on "with
(#DBFlags#)".

I can't write a full workaround because you did not provide enough details
about the access table. (Also, Is this a table that was ported from SQL or are
you using access as a front end to SQL server?)

The attached code should get you started.

-- MikeR



SELECT
SUM (iHitsTmp) AS iNumHits,
COUNT (tracker_id) AS iNumSessions
FROM
(
SELECT
SUM
(
SELECT COUNT (SiteTrk2.tracker_id)
FROM #SiteTracker# SiteTrk2
WHERE SiteTrk1.tracker_id = SiteTrk2.tracker_id
) AS iHitsTmp,
tracker_id
FROM
#SiteTracker# SiteTrk1
WHERE
<!---*** Insert VALID where clause here. ***--->
GROUP BY
tracker_id
)
AS PhantomTab
;

[Doogie]

You nailed it. The code worked. I have software called Active Tracker that,
before I acquired, was not aware it was for a SQL database and now I have had
no luck of getting anyone. All the emails just bounce back. I am trying to make
it work for MS Access. I keep running into SQL code I have never seen. I have a
SQL book, but nothing that tells me "use this for Access and this for SQL".

Any chance you could take a stab at the following. If I can get a few
examples, I think I can figure the rest out. I realize this is a lot to ask, so
I will completely understand if the answer is now. I appreciate your help in
advance. Maybe you could point me to a good book on Access SQL vs. SQL SQL?.

The table has the following columns:
[Field Name] [type] (size) Description
[ID] [int] Unique identifier of Hit
[DateTimeStamp] [datetime] Date stamp of Hit
[Referrer] [nvarchar] (128) Source URL
[Client_Address] [nvarchar] (128) IP address of client
[Client_Host] [nvarchar] (128) Name of host visited by user
[Client_Browser] [nvarchar] (128) Client's browser name
[Resource] [nvarchar] (128) Requested URL
[Tracker_Id] [int] Unique session ID

<!--- Get hits and sessions --->
<cfquery cachedwithin="#CreateTimeSpan(0,0,20,0)#" name="GetEntire"
username="#dbusername#" password="#dbpassword#" datasource="#datasourceR#">
select
count(*) as hits,
count(distinct tracker_id) as sessions,
Convert(varchar(11), Convert(datetime, datetimestamp),101) as datein
from #SiteTracker# with (#DBFlags#)
where Convert(datetime, datetimestamp) >= #DateFrom#
and Convert(datetime, datetimestamp) <= #DateTo#
group by Convert(varchar(11), Convert(datetime, datetimestamp),101)
order by Convert(varchar(11), Convert(datetime, datetimestamp),101)
</cfquery>

<!--- Get avg session length --->
<cfquery name="GetSessionLength" username="#dbusername#"
password="#dbpassword#" datasource="#datasourceR#">
select max(Convert(datetime, datetimestamp))-min(Convert(datetime,
datetimestamp)) as length
from #SiteTracker# with (#DBFlags#)
where Convert(datetime, datetimestamp) >= #DateFrom#
and Convert(datetime, datetimestamp) <= #DateTo#
group by tracker_id
</cfquery>

<!--- Get first "n" pages hits --->
<cfquery maxrows="#Val(Session.report2_top)#" name="GetPages"
username="#dbusername#" password="#dbpassword#" datasource="#datasourceR#">
select
count(*) as hits,
count(distinct tracker_id) as sessions,
Resource URL
from #SiteTracker# with (#DBFlags#)
where Convert(datetime, datetimestamp) >= #DateFrom#
and Convert(datetime, datetimestamp) <= #DateTo#
group by Resource
order by count(Resource) desc
</cfquery>

<!--- Get first "n" entry pages --->
<cfquery maxrows="#Val(Session.report3_top)#" username="#dbusername#"
password="#dbpassword#" datasource="#datasourceR#">
create table ##temp (
tracker_id int,
datetimestamp datetime,
Resource nvarchar(128)
)
</cfquery>
<cfquery maxrows="#Val(Session.report3_top)#" username="#dbusername#"
password="#dbpassword#" datasource="#datasourceR#">
insert ##temp
select tracker_id, min(datetimestamp), max(Resource)
from #SiteTracker# with (#DBFlags#)
where Convert(datetime, datetimestamp) >= #DateFrom#
and Convert(datetime, datetimestamp) <= #DateTo#
group by tracker_id
</cfquery>
<cfquery maxrows="#Val(Session.report3_top)#" name="GetPages"
username="#dbusername#" password="#dbpassword#" datasource="#datasourceR#">
select Resource as URL,
n = count(*)
from ##temp with (#DBFlags#)
group by Resource
order by n desc
</cfquery>
<cfquery maxrows="#Val(Session.report3_top)#" username="#dbusername#"
password="#dbpassword#" datasource="#datasourceR#">
drop table ##temp
</cfquery>

<!--- Get first "n" pages hits --->
<cftransaction>
<cfquery username="#dbusername#" password="#dbpassword#"
datasource="#datasourceR#">
create table ##weekday (
day int
)
<cfloop index="i" from="1" to="7">
insert into ##weekday values(#i#)
</cfloop>
</cfquery>
<cfquery name="GetPages" username="#dbusername#" password="#dbpassword#"
datasource="#datasourceR#">
select
##weekday.day as day,
count(id) as hits,
count(distinct tracker_id) as sessions
from #SiteTracker#, ##weekday with (#DBFlags#)
where Convert(datetime, datetimestamp) >= #DateFrom#
and Convert(datetime, datetimestamp) <= #DateTo#
and ##weekday.day *= DatePart(weekday, datetimestamp)
group by ##weekday.day
order by ##weekday.day
</cfquery>
<cfquery username="#dbusername#" password="#dbpassword#"
datasource="#datasourceR#">
drop table ##weekday
</cfquery>

[MikerRoo]

Well that is quite a project and maybe unnecessary. You can download and run
a limited edition of SQL Sever, "MSDE" for free.

It doesn't have the graphical tools and a few of SQL Server's shiny chrome
bits but it probably has everything you need.

More info at: [url]http://www.microsoft.com/sql/msde/default.asp[/url].

Anyway, the code you've attached uses count(distinct), convert(), and temp
tables -- all functions that are hard or impossible to duplicate in Access.

As for the differences, the above features plus autonumber, plus date/time
differences plus stored procedures are the most important changes from Access
to SQL Server.

The posted code is written explicitly for SQL server. It should be much
easier for you to figure it out in MSDE than to try and downgrade it to Access.

Regards,
-- MikeR

[Doogie]

Mike,

That is exactly what I needed to know. You have been a tremendous help!

Thank You,

David Moore

[MikerRoo]

You're welcome.
Good Luck!