Query to report statistics 1

[Swi]

I have the following fields in an Access database:

ID
logtimestamp
logip
client
code
srcURL
destURL

What I would like to do is provide a report based on total hits and unique hits based on [logip] for each

 in the table from a range of dates.

I assume I should be using some sort of crosstab query but suggestions are welcome.

Thanks.

Swi

 

[Swi]

I have the following two queries set up but would like to combine:

SELECT code, Count(*) AS N
FROM (SELECT DISTINCT code, logip FROM log)  AS T
GROUP BY code;

SELECT code, Count(*) AS N
FROM log
GROUP BY code;

Thanks.

Swi

 

[PHV]

What about this ?

SELECT A.code, A.UniqueHits, B.TotalHits
FROM (SELECT code, Count(*) AS UniqueHits
FROM (SELECT DISTINCT code, logip FROM log) AS T
GROUP BY code
) AS A INNER JOIN (SELECT code, Count(*) AS TotalHits
FROM log GROUP BY code
) AS B ON A.code = B.code

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

Wow! Very nice.

Swi

 

[Swi]

Stupid question however where would I put a WHERE clause to specify a range of dates to filter by?

Thanks again for your help.

Swi

 

[PHV]

2 times:
... FROM log WHERE ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Swi]

Thanks, will test in the morning when I am at my development laptop!

Swi