Hi,
I am trying to create a web page that outputs a list of data from a join on 2 tables.
The Tables:
PANEL - A list of possible panel numbers
PANELID - Primary key,
EVENTS - List of events that occur
EVENTID - Unique autonumber for each event
PANELID - posted from PANEL table
EVENTCODE - what type of event it is
ADATE - Date and Time the event was posted.
What I am trying to achieve:
I need to create a list of ALL panel IDs, regardless of whether they have any entries in the EVENTS table, but when they do, to display the max(ADATE) and event code - i.e. the last known date. If there are no events, the fields for ADATE and EVENTID should be blank.
What I have:
I am using the following query:
[CODE]SELECT DISTINCT (PANELID) AS UPANELID, Max(EVENTS.ADATE) AS LASTCONTACT, EVENTS.EVENTID
FROM PANEL LEFT JOIN EVENTSON PANEL.PANELID = EVENTS.PANELID
GROUP BY EVENTS.EVENTID, PANEL.PANELID;[/CODE]
The result is something like:
[CODE]
UPANELID LASTCONTACT EVENTID
10001 04/09/2006 14:55:56 555
10003
10007 04/09/2006 09:41:21 555
10008 04/09/2006 09:44:47 555
10012
10014 04/09/2006 14:43:37 555
10015
10016
10017[/CODE]
Now this works perfectly in the query tab of MS Access. But when I try and use it in my .asp page using the ADODB object, I get an unspecified server error.
I know it is the query that is the problem, because if I swap it for a simpler normal join, with out the LEFT, it works fine.
Can anyone shed any light on why it would not work or if there is part of the query that is not compatible with ADODB
Many thanks
QUERY written in MS Access does not work with adodb
Tariq Mehmood
Is the exception generated when you execute the query or when you open the database
Disposalist