Access compatible query does not work in asp

[XavierX]

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:

SELECT DISTINCT (PANELID) AS UPANELID, Max(EVENTS.ADATE) AS LASTCONTACT, EVENTS.EVENTID
FROM PANEL LEFT JOIN EVENTS ON PANEL.PANELID = EVENTS.PANELID
GROUP BY EVENTS.EVENTCODE, PANEL.PANELID;

The result is something like:

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

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

[MMcCarthy]

See if this makes a difference:

SELECT DISTINCT (PANEL.PANELID) AS UPANELID, Max(EVENTS.ADAT E) AS LASTCONTACT, EVENTS.EVENTID
FROM PANEL LEFT JOIN EVENTS ON PANEL.PANELID = EVENTS.PANELID
GROUP BY EVENTS.EVENTCOD E, PANEL.PANELID;

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:

SELECT DISTINCT (PANELID) AS UPANELID, Max(EVENTS.ADATE) AS LASTCONTACT, EVENTS.EVENTID
FROM PANEL LEFT JOIN EVENTS ON PANEL.PANELID = EVENTS.PANELID
GROUP BY EVENTS.EVENTCODE, PANEL.PANELID;

The result is something like:

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

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

[XavierX]

Sorry, nope that still has an error, and it also ignores the distinct in the access window and returns the PANELID multiple times

[MMcCarthy]

Try using this order, by the way if a PANELID can have more than one EVENTID the PANELID will return multiple values.

SELECT PANEL.PANELID AS UPANELID, EVENTS.EVENTID, Max(EVENTS.ADAT E) AS LASTCONTACT
FROM PANEL LEFT JOIN EVENTS ON PANEL.PANELID = EVENTS.PANELID
GROUP BY PANEL.PANELID, EVENTS.EVENTCOD E;

Sorry, nope that still has an error, and it also ignores the distinct in the access window and returns the PANELID multiple times