|
 |
access_asp thread: SQL query error
Message #1 by "Peter Rooney" <peter@w...> on Wed, 16 Jan 2002 20:39:53
|
|
Hi,
I have built a simple forum application first level shows the subject
heading followed by number of replies, the second level shows the original
message plus replies in a tree structure usual type of thing.
What I wanted to do was to add a feature that showed the user new/unread
messages so I did this by creating a child table that logged the message
ID and user ID when they followed a link to read the message, now I almost
managed to get this to work (with help).
Could all you Access experts see why I am receiving the following erro:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Join expression not supported
THE QUERY
" SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
RootTopic," &_
" IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
ID, " &_
" SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
" SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
" FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
tblMemories.ParentId = Parent.ID)" &_
" LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
tblRead.UserId = 36 " &_
" GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
RootTopic," &_
" IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
ID "
I am using Access 2000, if you need more info please let me know
Many thanks
Peter
Message #2 by "Ganesh Danej" <gmdanej@h...> on Wed, 23 Jan 2002 07:03:29
|
|
> Hi,
>
> I have built a simple forum application first level shows the subject
> heading followed by number of replies, the second level shows the
original
> message plus replies in a tree structure usual type of thing.
>
> What I wanted to do was to add a feature that showed the user new/unread
> messages so I did this by creating a child table that logged the message
> ID and user ID when they followed a link to read the message, now I
almost
> managed to get this to work (with help).
>
> Could all you Access experts see why I am receiving the following erro:
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Join expression not supported
>
> THE QUERY
>
>
> " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> RootTopic," &_
> " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> ID, " &_
> " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> tblMemories.ParentId = Parent.ID)" &_
> " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> tblRead.UserId = 36 " &_
> " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> RootTopic," &_
> " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> ID "
>
>
>
> I am using Access 2000, if you need more info please let me know
>
> Many thanks
>
> Peter
Message #3 by Thomas Bellavia <TBellavia@V...> on Wed, 23 Jan 2002 10:26:46 -0500
|
|
JOINS are not supported in ACCESS...
-----Original Message-----
From: Ganesh Danej [mailto:gmdanej@h...]
Sent: Wednesday, January 23, 2002 2:03 AM
To: Access ASP
Subject: [access_asp] Re: SQL query error
> Hi,
>
> I have built a simple forum application first level shows the subject
> heading followed by number of replies, the second level shows the
original
> message plus replies in a tree structure usual type of thing.
>
> What I wanted to do was to add a feature that showed the user new/unread
> messages so I did this by creating a child table that logged the message
> ID and user ID when they followed a link to read the message, now I
almost
> managed to get this to work (with help).
>
> Could all you Access experts see why I am receiving the following erro:
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Microsoft Access Driver] Join expression not supported
>
> THE QUERY
>
>
> " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> RootTopic," &_
> " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> ID, " &_
> " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> tblMemories.ParentId = Parent.ID)" &_
> " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> tblRead.UserId = 36 " &_
> " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> RootTopic," &_
> " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> ID "
>
>
>
> I am using Access 2000, if you need more info please let me know
>
> Many thanks
>
> Peter
Message #4 by "John Kinane" <john@k...> on Wed, 23 Jan 2002 11:46:02 -0500
|
|
sorry, don't mean to chime in on this thread but this one jumped out at me....
I did not know Access didn't support joins....this may solve a number of problems I've been having.
Is there some sort of "complete" primer out there that could clearly define these differences?
Sure, I can just use MSDN but that only seems useful when you know what you're looking for...sort of
like looking up a word in the dictionary that you don't know how to spell....
thanks and sorry to butt in :-)
John
----- Original Message -----
From: "Thomas Bellavia" <TBellavia@V...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, January 23, 2002 10:26 AM
Subject: [access_asp] Re: SQL query error
> JOINS are not supported in ACCESS...
>
> -----Original Message-----
> From: Ganesh Danej [mailto:gmdanej@h...]
> Sent: Wednesday, January 23, 2002 2:03 AM
> To: Access ASP
> Subject: [access_asp] Re: SQL query error
>
> > Hi,
> >
> > I have built a simple forum application first level shows the subject
> > heading followed by number of replies, the second level shows the
> original
> > message plus replies in a tree structure usual type of thing.
> >
> > What I wanted to do was to add a feature that showed the user new/unread
> > messages so I did this by creating a child table that logged the message
> > ID and user ID when they followed a link to read the message, now I
> almost
> > managed to get this to work (with help).
> >
> > Could all you Access experts see why I am receiving the following erro:
> >
> > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > [Microsoft][ODBC Microsoft Access Driver] Join expression not supported
> >
> > THE QUERY
> >
> >
> > " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > RootTopic," &_
> > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > ID, " &_
> > " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> > " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> > " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> > tblMemories.ParentId = Parent.ID)" &_
> > " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> > tblRead.UserId = 36 " &_
> > " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > RootTopic," &_
> > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > ID "
> >
> >
> >
> > I am using Access 2000, if you need more info please let me know
> >
> > Many thanks
> >
> > Peter
>
>
>
Message #5 by Thomas Bellavia <TBellavia@V...> on Wed, 23 Jan 2002 11:58:47 -0500
|
|
My only reference with data access and Access is Wrox's ADO2.6 Programmers
Reference.
-----Original Message-----
From: John Kinane [mailto:john@k...]
Sent: Wednesday, January 23, 2002 11:46 AM
To: Access ASP
Subject: [access_asp] Re: SQL query error
sorry, don't mean to chime in on this thread but this one jumped out at
me....
I did not know Access didn't support joins....this may solve a number of
problems I've been having.
Is there some sort of "complete" primer out there that could clearly define
these differences?
Sure, I can just use MSDN but that only seems useful when you know what
you're looking for...sort of
like looking up a word in the dictionary that you don't know how to
spell....
thanks and sorry to butt in :-)
John
----- Original Message -----
From: "Thomas Bellavia" <TBellavia@V...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, January 23, 2002 10:26 AM
Subject: [access_asp] Re: SQL query error
> JOINS are not supported in ACCESS...
>
> -----Original Message-----
> From: Ganesh Danej [mailto:gmdanej@h...]
> Sent: Wednesday, January 23, 2002 2:03 AM
> To: Access ASP
> Subject: [access_asp] Re: SQL query error
>
> > Hi,
> >
> > I have built a simple forum application first level shows the subject
> > heading followed by number of replies, the second level shows the
> original
> > message plus replies in a tree structure usual type of thing.
> >
> > What I wanted to do was to add a feature that showed the user new/unread
> > messages so I did this by creating a child table that logged the message
> > ID and user ID when they followed a link to read the message, now I
> almost
> > managed to get this to work (with help).
> >
> > Could all you Access experts see why I am receiving the following erro:
> >
> > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > [Microsoft][ODBC Microsoft Access Driver] Join expression not supported
> >
> > THE QUERY
> >
> >
> > " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > RootTopic," &_
> > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > ID, " &_
> > " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> > " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> > " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> > tblMemories.ParentId = Parent.ID)" &_
> > " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> > tblRead.UserId = 36 " &_
> > " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > RootTopic," &_
> > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > ID "
> >
> >
> >
> > I am using Access 2000, if you need more info please let me know
> >
> > Many thanks
> >
> > Peter
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
>
Message #6 by "Peter Rooney" <peter.rooney@p...> on Wed, 23 Jan 2002 17:04:31 -0000
|
|
John,
Access can use JOINS and the I eventually found the solution:
szSQL = " SELECT IIF(tblMemories.root = 0, tblMemories.Topic, Parent.Topic)
as RootTopic, " &_
" IIF(tblMemories.root = 0, tblMemories.ID, tblMemories.root) as ID, " &_
" SUM(IIF(tblMemories.root = 0, 0, 1)) as Replies, " &_
" SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
" FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
tblMemories.root = Parent.ID) " &_
" LEFT OUTER JOIN tblRead on (tblMemories.ID = tblRead.ID and
(tblRead.UserId =" & USERID & " or tblRead.UserId is null)) " &_
" GROUP BY IIF(tblMemories.root = 0, tblMemories.Topic, Parent.Topic), " &_
" IIF(tblMemories.root = 0, tblMemories.ID, tblMemories.root) "
Lovely isn't it
-----Original Message-----
From: John Kinane [mailto:john@k...]
Sent: Wednesday, January 23, 2002 4:46 PM
To: Access ASP
Subject: [access_asp] Re: SQL query error
sorry, don't mean to chime in on this thread but this one jumped out at
me....
I did not know Access didn't support joins....this may solve a number of
problems I've been having.
Is there some sort of "complete" primer out there that could clearly define
these differences?
Sure, I can just use MSDN but that only seems useful when you know what
you're looking for...sort of
like looking up a word in the dictionary that you don't know how to
spell....
thanks and sorry to butt in :-)
John
----- Original Message -----
From: "Thomas Bellavia" <TBellavia@V...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, January 23, 2002 10:26 AM
Subject: [access_asp] Re: SQL query error
> JOINS are not supported in ACCESS...
>
> -----Original Message-----
> From: Ganesh Danej [mailto:gmdanej@h...]
> Sent: Wednesday, January 23, 2002 2:03 AM
> To: Access ASP
> Subject: [access_asp] Re: SQL query error
>
> > Hi,
> >
> > I have built a simple forum application first level shows the subject
> > heading followed by number of replies, the second level shows the
> original
> > message plus replies in a tree structure usual type of thing.
> >
> > What I wanted to do was to add a feature that showed the user new/unread
> > messages so I did this by creating a child table that logged the message
> > ID and user ID when they followed a link to read the message, now I
> almost
> > managed to get this to work (with help).
> >
> > Could all you Access experts see why I am receiving the following erro:
> >
> > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > [Microsoft][ODBC Microsoft Access Driver] Join expression not supported
> >
> > THE QUERY
> >
> >
> > " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > RootTopic," &_
> > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > ID, " &_
> > " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> > " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> > " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> > tblMemories.ParentId = Parent.ID)" &_
> > " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> > tblRead.UserId = 36 " &_
> > " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > RootTopic," &_
> > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > ID "
> >
> >
> >
> > I am using Access 2000, if you need more info please let me know
> >
> > Many thanks
> >
> > Peter
>
$subst('Email.Unsub').
>
$subst('Email.Unsub').
>
--
Virus scanned by edNET.
Message #7 by "John Kinane" <john@k...> on Wed, 23 Jan 2002 12:04:36 -0500
|
|
ahh, cool, thanks!
----- Original Message -----
From: "Thomas Bellavia" <TBellavia@V...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, January 23, 2002 11:58 AM
Subject: [access_asp] Re: SQL query error
> My only reference with data access and Access is Wrox's ADO2.6 Programmers
> Reference.
>
> -----Original Message-----
> From: John Kinane [mailto:john@k...]
> Sent: Wednesday, January 23, 2002 11:46 AM
> To: Access ASP
> Subject: [access_asp] Re: SQL query error
>
> sorry, don't mean to chime in on this thread but this one jumped out at
> me....
>
> I did not know Access didn't support joins....this may solve a number of
> problems I've been having.
>
> Is there some sort of "complete" primer out there that could clearly define
> these differences?
> Sure, I can just use MSDN but that only seems useful when you know what
> you're looking for...sort of
> like looking up a word in the dictionary that you don't know how to
> spell....
>
> thanks and sorry to butt in :-)
>
> John
>
> ----- Original Message -----
> From: "Thomas Bellavia" <TBellavia@V...>
> To: "Access ASP" <access_asp@p...>
> Sent: Wednesday, January 23, 2002 10:26 AM
> Subject: [access_asp] Re: SQL query error
>
>
> > JOINS are not supported in ACCESS...
> >
> > -----Original Message-----
> > From: Ganesh Danej [mailto:gmdanej@h...]
> > Sent: Wednesday, January 23, 2002 2:03 AM
> > To: Access ASP
> > Subject: [access_asp] Re: SQL query error
> >
> > > Hi,
> > >
> > > I have built a simple forum application first level shows the subject
> > > heading followed by number of replies, the second level shows the
> > original
> > > message plus replies in a tree structure usual type of thing.
> > >
> > > What I wanted to do was to add a feature that showed the user new/unread
> > > messages so I did this by creating a child table that logged the message
> > > ID and user ID when they followed a link to read the message, now I
> > almost
> > > managed to get this to work (with help).
> > >
> > > Could all you Access experts see why I am receiving the following erro:
> > >
> > > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > > [Microsoft][ODBC Microsoft Access Driver] Join expression not supported
> > >
> > > THE QUERY
> > >
> > >
> > > " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > > RootTopic," &_
> > > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > > ID, " &_
> > > " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> > > " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> > > " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> > > tblMemories.ParentId = Parent.ID)" &_
> > > " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> > > tblRead.UserId = 36 " &_
> > > " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic) as
> > > RootTopic," &_
> > > " IIF(tblMemories.ParentId = 0, tblMemories.ID, tblMemories.ParentId) as
> > > ID "
> > >
> > >
> > >
> > > I am using Access 2000, if you need more info please let me know
> > >
> > > Many thanks
> > >
> > > Peter
> >
> $subst('Email.Unsub').
> >
> $subst('Email.Unsub').
> >
>
>
>
>
Message #8 by <sathish297@y...> on Thu, 24 Jan 2002 19:25:08 +0530
|
|
Hi,
In access join works very well.
Try the below code with a temp. table.
SELECT *
FROM table1 INNER JOIN table2 ON table1 .sno = table2.sno;
With Regards,
R. Sathish Kumar.
sathish297@y...
----- Original Message -----
From: "John Kinane" <john@k...>
To: "Access ASP" <access_asp@p...>
Sent: Wednesday, January 23, 2002 10:34 PM
Subject: [access_asp] Re: SQL query error
> ahh, cool, thanks!
>
>
> ----- Original Message -----
> From: "Thomas Bellavia" <TBellavia@V...>
> To: "Access ASP" <access_asp@p...>
> Sent: Wednesday, January 23, 2002 11:58 AM
> Subject: [access_asp] Re: SQL query error
>
>
> > My only reference with data access and Access is Wrox's ADO2.6
Programmers
> > Reference.
> >
> > -----Original Message-----
> > From: John Kinane [mailto:john@k...]
> > Sent: Wednesday, January 23, 2002 11:46 AM
> > To: Access ASP
> > Subject: [access_asp] Re: SQL query error
> >
> > sorry, don't mean to chime in on this thread but this one jumped out at
> > me....
> >
> > I did not know Access didn't support joins....this may solve a number of
> > problems I've been having.
> >
> > Is there some sort of "complete" primer out there that could clearly
define
> > these differences?
> > Sure, I can just use MSDN but that only seems useful when you know what
> > you're looking for...sort of
> > like looking up a word in the dictionary that you don't know how to
> > spell....
> >
> > thanks and sorry to butt in :-)
> >
> > John
> >
> > ----- Original Message -----
> > From: "Thomas Bellavia" <TBellavia@V...>
> > To: "Access ASP" <access_asp@p...>
> > Sent: Wednesday, January 23, 2002 10:26 AM
> > Subject: [access_asp] Re: SQL query error
> >
> >
> > > JOINS are not supported in ACCESS...
> > >
> > > -----Original Message-----
> > > From: Ganesh Danej [mailto:gmdanej@h...]
> > > Sent: Wednesday, January 23, 2002 2:03 AM
> > > To: Access ASP
> > > Subject: [access_asp] Re: SQL query error
> > >
> > > > Hi,
> > > >
> > > > I have built a simple forum application first level shows the
subject
> > > > heading followed by number of replies, the second level shows the
> > > original
> > > > message plus replies in a tree structure usual type of thing.
> > > >
> > > > What I wanted to do was to add a feature that showed the user
new/unread
> > > > messages so I did this by creating a child table that logged the
message
> > > > ID and user ID when they followed a link to read the message, now I
> > > almost
> > > > managed to get this to work (with help).
> > > >
> > > > Could all you Access experts see why I am receiving the following
erro:
> > > >
> > > > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> > > > [Microsoft][ODBC Microsoft Access Driver] Join expression not
supported
> > > >
> > > > THE QUERY
> > > >
> > > >
> > > > " SELECT IIF(Parent.Topic is Null, tblMemories.Topic, Parent.Topic)
as
> > > > RootTopic," &_
> > > > " IIF(tblMemories.ParentId = 0, tblMemories.ID,
tblMemories.ParentId) as
> > > > ID, " &_
> > > > " SUM(IIF(tblMemories.ParentId = 0, 1, 0)) as Replies, " &_
> > > > " SUM(IIF(tblRead.ID is null, 1, 0)) as New " &_
> > > > " FROM (tblMemories LEFT OUTER JOIN tblMemories as Parent on
> > > > tblMemories.ParentId = Parent.ID)" &_
> > > > " LEFT OUTER JOIN tblRead on tblMemories.ID = tblRead.ID and
> > > > tblRead.UserId = 36 " &_
> > > > " GROUP BY IIF(Parent.Topic is Null, tblMemories.Topic,
Parent.Topic) as
> > > > RootTopic," &_
> > > > " IIF(tblMemories.ParentId = 0, tblMemories.ID,
tblMemories.ParentId) as
> > > > ID "
> > > >
> > > >
> > > >
> > > > I am using Access 2000, if you need more info please let me know
> > > >
> > > > Many thanks
> > > >
> > > > Peter
> > >
> > $subst('Email.Unsub').
> > >
> > $subst('Email.Unsub').
> > >
> >
> >
$subst('Email.Unsub').
> >
$subst('Email.Unsub').
> >
>
>
$subst('Email.Unsub').
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
|
|
|
View Cart
