ASP Classic Query that needs to display more information

first of all

create an excel

put your tables here
put some test data
and ask for the result that you are looking for + description for result

so we can help you...

events				
event_ID	event_name	event_code	event_start	event_finish
12		Australian GP	F1		5/05/2017	7/05/2017
13		Canadian GP		F1		12/05/2017	14/05/2017
codes				
code_ID	code_name	code_short		
1		Formula 1	F1

Select allOpen in new window

What I want as a result:
F1(used as image code) Formula 1 Australian GP - 5/05/2017 to 7/05/2017
F1(used as image code) Formula 1 Canadian GP - 12/05/2017 to 14/05/2017

The original query counts how many times a particular event has been viewed by clients visiting the site and orders them most popular downwards, I would like to keep this, however show the top 10 with more information

where is event_log here?

try this...

select e.*, c.code_ID, c.code_name
  from events e inner join codes c on e.event_code=c.code_short
 order by e.event_code, e.event_start, e.event_finish

Select allOpen in new window

and you need another query to get the count

select event_code, count(1) event_count
  from events e
 group by event_code
 order by event_code 

Select allOpen in new window

then you loop qry2 and inside it, you filter qry1 by "event_code=""" & qry2("event_code") & """"

log_event is in the site_log table, this is where is counts how popular an event is

The above querys are not what I am after...

I just want more information out of the existing query

not really tested on this and can't really remember what we have done previously.

perhaps you can customize something like this: (knowing this is Access and below query is not tested)

SELECT TOP 10 COUNT(a.log_event),
a.log_event, 
b.event_name,
b.event_start,
b.event_finish,
b.event_IATA,
b.code_name,
b.code_short

FROM site_log AS a 
INNER JOIN 
(select e.*, c.code_name, c.code_short from events as e left join codes as c on e.event_code = c.code_short) as
events AS b ON a.log_event=b.event_ID 

WHERE b.brand_ID='"&SubDomain("brand_options.brand_ID")&"' AND a.log_event<>NULL AND a.log_date BETWEEN #"&month2&"# AND #"&month1&"# AND b.event_start>NOW() 

GROUP BY
a.log_event, 
b.event_name,
b.event_start,
b.event_finish,
b.event_IATA,
b.code_name,
b.code_short

HAVING COUNT(a.log_event)>0
ORDER BY COUNT(a.log_event) DESC

Select allOpen in new window

I have tried to play with this, however just made a mess :(

That's because you have to decide if you wish to group by these (which will influence your count heavily) or select first, last, min or max values.

/gustav

Thanks for that Ryan, I think we are on the right track

this is the current error:

Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

Select allOpen in new window

On the Query Line

try this:

SELECT TOP 10 COUNT(a.log_event) as cnt,
a.log_event, 
b.event_name,
b.event_start,
b.event_finish,
b.event_IATA,
b.code_name,
b.code_short

FROM site_log AS a 
INNER JOIN 
(select e.*, c.code_name, c.code_short from events as e left join codes as c on e.event_code = c.code_short) as
 b ON a.log_event=b.event_ID 

WHERE b.brand_ID='"&SubDomain("brand_options.brand_ID")&"' AND a.log_event<>NULL AND a.log_date BETWEEN #"&month2&"# AND #"&month1&"# AND b.event_start>NOW() 

GROUP BY
a.log_event, 
b.event_name,
b.event_start,
b.event_finish,
b.event_IATA,
b.code_name,
b.code_short

HAVING COUNT(a.log_event)>0
ORDER BY COUNT(a.log_event) DESC

Select allOpen in new window

In the join, you have labelled codes AS c, should we rename the b.code_short to c.code_short ?

you have labelled codes AS c, should we rename the b.code_short to c.code_short ?

not exactly... since it's a sub query and aliased as b.

INNER JOIN
(select e.*, c.code_name, c.code_short from events as e left join codes as c on e.event_code = c.code_short) as
 b ON a.log_event=b.event_ID

(check the second bold)

Ah I see, thought I would ask :)

I think I have something wrong... sorry

where I have codes.code_short, should be events.event_code

Sorry dude!

Yes sorry about that, I tried similar to you and yours and got this error both times:

Microsoft JET Database Engine error '80004005'

The specified field 'b.event_code' could refer to more than one table listed in the FROM clause of your SQL statement.

Select allOpen in new window

Bit confused by it thou since there is only 1 event_code over all tables

Microsoft JET Database Engine error '80004005'

The specified field 'b.event_code' could refer to more than one table listed in the FROM clause of your SQL statement.

can you post your current script? the latest comment's code was tested locally before it was posted.

SELECT TOP 10 COUNT(a.log_event) AS cnt,a.log_event,b.event_name,b.event_start,b.event_finish,b.event_IATA,b.code_name,b.event_code FROM site_log AS a INNER JOIN (SELECT e.*,c.code_name,c.code_short,e.event_code FROM events AS e LEFT JOIN codes AS c ON e.event_code=c.code_short) AS b ON a.log_event=b.event_ID WHERE b.brand_ID='"&SubDomain("brand_options.brand_ID")&"' AND a.log_event<>NULL AND a.log_date BETWEEN #"&month2&"# AND #"&month1&"# AND b.event_start>NOW() GROUP BY a.log_event,b.event_name,b.event_start,b.event_finish,b.event_IATA,b.code_name,b.event_code HAVING COUNT(a.log_event)>0 ORDER BY COUNT(a.log_event) DESC

Select allOpen in new window

Cheers mate! spot on and works a treat! :)