Thread: Difference between Access97 and PostgreSQL

Difference between Access97 and PostgreSQL

From

"Yan Bai"

Date:

30 January 2002, 22:05:27

Hello all,

My question may out of the range of this forum.
When I run the following query under postgreSQL and Access2000, it works 
well. But Access97 cannot accept it. I got the error message as 'syntax 
error near FROM', it seems like in Access97, there cannot have a subquery 
after 'FROM'

--------------------------------------------------
SELECT temp.rating, temp.avgage
FROM (SELECT s.rating, AVG(s.age) as avgage     FROM sailors s GROUP BY s.rating) as temp
--------------------------------------------------

I did check the help information from Access2000 about 'what's new in Access 
2000', since i want to find out what the difference make Access2000 accept 
this query, but no result :-(

You helps are very desirable. Thanks in advance.

Annie

_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

Re: Difference between Access97 and PostgreSQL

From

"Josh Berkus"

Date:

30 January 2002, 23:14:43

Annie,

> My question may out of the range of this forum.

Well, yes.  But, since I'm a nice guy, I'll answer your question eventhough it does not relate to Postgres ... if you
promisenot to do itagain!  In the future, send this question to a more appropriate list,such as PSQL-ODBC, where they
discussPaotgres & MS Access.
 

> When I run the following query under postgreSQL and Access2000, it
>  works well. But Access97 cannot accept it. I got the error message
>  as 'syntax error near FROM', it seems like in Access97, there cannot
>  have a subquery after 'FROM'
> 
> --------------------------------------------------
> SELECT temp.rating, temp.avgage
> FROM (SELECT s.rating, AVG(s.age) as avgage
>      FROM sailors s GROUP BY s.rating) as temp
> --------------------------------------------------
> 

You are exactly correct.  MS Access 97 does not support sub-queires inthe FROM clause.  THe only sub-queries supported
byAccess 97 aresub-queries in the WHERE clause.
 

All the more reason to use PostgreSQL!

-Josh



______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco

Re: Difference between Access97 and PostgreSQL

From

Roberto Mello

Date:

31 January 2002, 13:45:14

On Wed, Jan 30, 2002 at 11:54:56PM +0000, Yan Bai wrote:
> 
> --------------------------------------------------
> SELECT temp.rating, temp.avgage
> FROM (SELECT s.rating, AVG(s.age) as avgage
>      FROM sailors s GROUP BY s.rating) as temp

Have you tried this query without the subselect? It looks like it should
work.

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
It said "Insert disk #3", but only 2 will fit!

Re: Difference between Access97 and PostgreSQL

From

"Christopher Kings-Lynne"

Date:

31 January 2002, 22:46:23

> On Wed, Jan 30, 2002 at 11:54:56PM +0000, Yan Bai wrote:
> > 
> > --------------------------------------------------
> > SELECT temp.rating, temp.avgage
> > FROM (SELECT s.rating, AVG(s.age) as avgage
> >      FROM sailors s GROUP BY s.rating) as temp
> 
> Have you tried this query without the subselect? It looks like it should
> work.

What about this?

SELECT temp.rating, temp.avgage
FROM (SELECT s.rating, AVG(s.age) as avgage    FROM sailors s GROUP BY s.rating, s.age) as temp

Chris

Re: Difference between Access97 and PostgreSQL

From

"Henshall, Stuart - WCP"

Date:

01 February 2002, 08:29:20


> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: 31 January 2002 00:58
> To: Yan Bai; pgsql-sql@postgresql.org
> Subject: Re: Difference between Access97 and PostgreSQL
> 
> 
> Annie,
> 
> > My question may out of the range of this forum.
> 
> Well, yes.  But, since I'm a nice guy, I'll answer your question even
>  though it does not relate to Postgres ... if you promise not to do it
>  again!  In the future, send this question to a more appropriate list,
>  such as PSQL-ODBC, where they discuss Paotgres & MS Access.
> 
> > When I run the following query under postgreSQL and Access2000, it
> >  works well. But Access97 cannot accept it. I got the error message
> >  as 'syntax error near FROM', it seems like in Access97, 
> there cannot
> >  have a subquery after 'FROM'
> > 
> > --------------------------------------------------
> > SELECT temp.rating, temp.avgage
> > FROM (SELECT s.rating, AVG(s.age) as avgage
> >      FROM sailors s GROUP BY s.rating) as temp
> > --------------------------------------------------
> > 
> 
> You are exactly correct.  MS Access 97 does not support sub-queires in
>  the FROM clause.  THe only sub-queries supported by Access 97 are
>  sub-queries in the WHERE clause.

With this sort of problem (I also vaguely recall ahving a problem with a
union) I tend to make another query (as in an MS Access query that anyone
else would call a view) and use it in the from clause.
Cheers,
- Stuart