Strange problems in A2002 SP3

[Justin Koivisto]

I am trying to create a query to use as a report record source. Below is
what I want to do (this was tested and works with a MySQL web script):

SELECT
contacts.id,
contacts.email,
contacts.first_name,
contacts.last_name,
contacts.middle_name,
contacts.company,
contacts.address,
contacts.address2,
contacts.city,
contacts.state,
contacts.country,
contacts.zip,
contacts.phone,
contacts.website,
contacts.how_found,
contacts.specific_other,
contacts.comments,
contacts.submitted,
contacts.mail_list,
contacts.travel_dates,
interests.name
FROM contacts, interests
LEFT JOIN idx_interest ON idx_interest.contact_id = contacts.id
WHERE idx_interest.interest_id = interests.id

To create the query, I click on the new query button, select design
view, switch to SQL view, then paste this in. When I try to save the
query, I get a "Syntax error in JOIN operation" message. When I click
OK, Access highlights the "contacts" part of "SELECT contacts.id". I am
using Access 2002 (10.6501.6626) SP3.

Any help is greatly appreciated,

TIA

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Matthias Klaey]

On Wed, 08 Sep 2004 22:01:59 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

I am trying to create a query to use as a report record source. Below is
what I want to do (this was tested and works with a MySQL web script):

SELECT
contacts.id, [ ... more fields ]
interests.name
FROM contacts, interests
LEFT JOIN idx_interest ON idx_interest.contact_id = contacts.id
WHERE idx_interest.interest_id = interests.id

To create the query, I click on the new query button, select design
view, switch to SQL view, then paste this in. When I try to save the
query, I get a "Syntax error in JOIN operation" message. When I click
OK, Access highlights the "contacts" part of "SELECT contacts.id". I am
using Access 2002 (10.6501.6626) SP3.

Any help is greatly appreciated,

TIA

This is quite different from Access syntax. Here is how I would try to
do it (air code, not tested):

SELECT
contacts.id,
< ...more fields ...>,
interests.name
FROM contacts
LEFT JOIN interests ON contacts.id = interests.id;

HTH
Matthias Kläy
--
www.kcc.ch

[XMVP]

"Justin Koivisto" <sp**@koivi.com> wrote in message
news:rj*****************@news7.onvoy.net...

I am trying to create a query to use as a report record source. Below is
what I want to do (this was tested and works with a MySQL web script):

SELECT
contacts.id,
contacts.email,
contacts.first_name,
contacts.last_name,
contacts.middle_name,
contacts.company,
contacts.address,
contacts.address2,
contacts.city,
contacts.state,
contacts.country,
contacts.zip,
contacts.phone,
contacts.website,
contacts.how_found,
contacts.specific_other,
contacts.comments,
contacts.submitted,
contacts.mail_list,
contacts.travel_dates,
interests.name
FROM contacts, interests
LEFT JOIN idx_interest ON idx_interest.contact_id = contacts.id
WHERE idx_interest.interest_id = interests.id

To create the query, I click on the new query button, select design
view, switch to SQL view, then paste this in. When I try to save the
query, I get a "Syntax error in JOIN operation" message. When I click
OK, Access highlights the "contacts" part of "SELECT contacts.id". I am
using Access 2002 (10.6501.6626) SP3.

Any help is greatly appreciated,

TIA

This newsgroup is for _Access Morons_ not MySQL Morons.

[Justin Koivisto]

Matthias Klaey wrote:

On Wed, 08 Sep 2004 22:01:59 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

I am trying to create a query to use as a report record source. Below is
what I want to do (this was tested and works with a MySQL web script):

SELECT
contacts.id,

[ ... more fields ]

interests.name

FROM contacts, interests

LEFT JOIN idx_interest ON idx_interest.contact_id = contacts.id
WHERE idx_interest.interest_id = interests.id

To create the query, I click on the new query button, select design
view, switch to SQL view, then paste this in. When I try to save the
query, I get a "Syntax error in JOIN operation" message. When I click
OK, Access highlights the "contacts" part of "SELECT contacts.id". I am
using Access 2002 (10.6501.6626) SP3.

Any help is greatly appreciated,

TIA

This is quite different from Access syntax. Here is how I would try to
do it (air code, not tested):

SELECT
contacts.id,
< ...more fields ...>,
interests.name
FROM contacts
LEFT JOIN interests ON contacts.id = interests.id;

That wouldn't work, contacts.id is a person, interests.id is something
they may be interested in the idx_insterst table holds relations between
the two... Below is a quick example of how it is set up:

TABLE STRUCTURES:
contacts
--------
id int
name varchar
email varchar

interests
---------
id int
name varchar

idx_interest
------------
interest_id int
contact_id int

TABLE DATA
contacts
--------
1,justin,ju****@example.com
2,zach,za**@example.com
3,josh,jo**@example.com
4,jw,[e-mail]
5,joe,jo*@example.com

interests
---------
1,sql
2,php
3,design
4,sales
5,print
6,web
7,applications

idx_interest
------------
1,1
1,2
1,3
1,6
2,1
2,6
3,3
3,5
3,6
4,4
4,5

What I am after is:

+---------------------------------------------+
| name | email | interest |
+---------------------------------------------+
| justin | ju****@example.com | sql |
+---------------------------------------------+
| justin | ju****@example.com | php |
+---------------------------------------------+
| justin | ju****@example.com | design |
+---------------------------------------------+
| justin | ju****@example.com | web |
+---------------------------------------------+
| zach | za**@example.com | sql |
+---------------------------------------------+
| zach | za**@example.com | web |
+---------------------------------------------+
| josh | jo**@example.com | design |
+---------------------------------------------+
| josh | jo**@example.com | print |
+---------------------------------------------+
| josh | jo**@example.com | web |
+---------------------------------------------+
| jw | [e-mail] | sales |
+---------------------------------------------+
| jw | [e-mail] | print |
+---------------------------------------------+

Anyway, works fine with MySQL, just wondering where the JOIN sytax error
comes from...

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Justin Koivisto]

XMVP wrote:

This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.

That being said, I am a moron of the 5th degree since linux, apache,
php, mysql, windows and access are all involved in the project...

I'm sorry to hear that you are just as moronic as I since you don't know
what the problem is either. :\

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Matthias Klaey]

On Thu, 09 Sep 2004 13:31:53 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

Matthias Klaey wrote:

On Wed, 08 Sep 2004 22:01:59 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

I am trying to create a query to use as a report record source. Below is
what I want to do (this was tested and works with a MySQL web script):

SELECT
contacts.id,
[ ... more fields ]

interests.name

FROM contacts, interests

LEFT JOIN idx_interest ON idx_interest.contact_id = contacts.id
WHERE idx_interest.interest_id = interests.id

To create the query, I click on the new query button, select design
view, switch to SQL view, then paste this in. When I try to save the
query, I get a "Syntax error in JOIN operation" message. When I click
OK, Access highlights the "contacts" part of "SELECT contacts.id". I am
using Access 2002 (10.6501.6626) SP3.

Any help is greatly appreciated,

TIA

This is quite different from Access syntax. Here is how I would try to
do it (air code, not tested):

SELECT
contacts.id,
< ...more fields ...>,
interests.name
FROM contacts
LEFT JOIN interests ON contacts.id = interests.id;

That wouldn't work, contacts.id is a person, interests.id is something
they may be interested in the idx_insterst table holds relations between
the two... Below is a quick example of how it is set up:

TABLE STRUCTURES:
contacts
--------
id int
name varchar
email varchar

interests
---------
id int
name varchar

idx_interest
------------
interest_id int
contact_id int

TABLE DATA
contacts
--------
1,justin,ju****@example.com
2,zach,za**@example.com
3,josh,jo**@example.com
4,jw,[e-mail]
5,joe,jo*@example.com

interests
---------
1,sql
2,php
3,design
4,sales
5,print
6,web
7,applications

idx_interest
------------
1,1
1,2
1,3
1,6
2,1
2,6
3,3
3,5
3,6
4,4
4,5

What I am after is:

+---------------------------------------------+
| name | email | interest |
+---------------------------------------------+
| justin | ju****@example.com | sql |
+---------------------------------------------+
| justin | ju****@example.com | php |
+---------------------------------------------+
| justin | ju****@example.com | design |
+---------------------------------------------+
| justin | ju****@example.com | web |
+---------------------------------------------+
| zach | za**@example.com | sql |
+---------------------------------------------+
| zach | za**@example.com | web |
+---------------------------------------------+
| josh | jo**@example.com | design |
+---------------------------------------------+
| josh | jo**@example.com | print |
+---------------------------------------------+
| josh | jo**@example.com | web |
+---------------------------------------------+
| jw | [e-mail] | sales |
+---------------------------------------------+
| jw | [e-mail] | print |
+---------------------------------------------+

Ok, I see. I tried it both in native Jet mode and ANSI-92 mode, your
SQL gives an "amiguous outer join" error in both cases.

The following works:

SELECT contacts.id, contacts.email, contacts.name, interests.name
FROM (contacts INNER JOIN idx_interest
ON contacts.id = idx_interest.contact_id)
LEFT JOIN interests
ON idx_interest.interest_id = interests.id;

Make that a LEFT JOIN instead of an INNER JOIN if you want to see all
the contacts, even if they have not interest.
Anyway, works fine with MySQL, just wondering where the JOIN sytax error
comes from...

These are the joys of portability ... :-)

HTH
Matthias Kläy
--
www.kcc.ch

[XMVP]

"Justin Koivisto" <sp**@koivi.com> wrote in message
news:b3*****************@news7.onvoy.net...

XMVP wrote:

This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.

That being said, I am a moron of the 5th degree since linux, apache,
php, mysql, windows and access are all involved in the project...

I'm sorry to hear that you are just as moronic as I since you don't know
what the problem is either. :\

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

No relation to Microsoft! Except for a few brain dead Microsoft MVPs who
wander into this newsgroup looking for newbies to fleece.

The morons here usually stick to Access and Jet. Sometimes they go off on
MS SQL Server--way out of their league!

The project you describe is a huge mess. You (or your company) will never
make any money. No matter how much junk software you cobble together, you
still have a pile of junk. Take it all to the town dump. (That's the
answer to your problem, in case you missed it.)

[Justin Koivisto]

Matthias Klaey wrote:

On Thu, 09 Sep 2004 13:31:53 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

Matthias Klaey wrote:

On Wed, 08 Sep 2004 22:01:59 GMT, Justin Koivisto <sp**@koivi.com>
wrote:

I am trying to create a query to use as a report record source. Below is
what I want to do (this was tested and works with a MySQL web script):

SELECT
contacts.id,

[ ... more fields ]

interests.name

FROM contacts, interests

LEFT JOIN idx_interest ON idx_interest.contact_id = contacts.id
WHERE idx_interest.interest_id = interests.id

To create the query, I click on the new query button, select design
view, switch to SQL view, then paste this in. When I try to save the
query, I get a "Syntax error in JOIN operation" message. When I click
OK, Access highlights the "contacts" part of "SELECT contacts.id". I am
using Access 2002 (10.6501.6626) SP3.

This is quite different from Access syntax. Here is how I would try to
do it (air code, not tested):

SELECT
contacts.id,
< ...more fields ...>,
interests.name
FROM contacts
LEFT JOIN interests ON contacts.id = interests.id;
That wouldn't work, contacts.id is a person, interests.id is something
they may be interested in the idx_insterst table holds relations between
the two... Below is a quick example of how it is set up:
<snip table and data structures>
Ok, I see. I tried it both in native Jet mode and ANSI-92 mode, your
SQL gives an "amiguous outer join" error in both cases.
Wonder why I didn't get that message and only "Syntax error in JOIN
operation"...
The following works:

SELECT contacts.id, contacts.email, contacts.name, interests.name
FROM (contacts INNER JOIN idx_interest
ON contacts.id = idx_interest.contact_id)
LEFT JOIN interests
ON idx_interest.interest_id = interests.id;

Like a charm - THANKS!
Anyway, works fine with MySQL, just wondering where the JOIN sytax error
comes from...

These are the joys of portability ... :-)

It seems that's all I've been doing lately... we've got a bunch of
people who suddenly decide that they like the way Access prints out
mailing labels. That's really the only reason I have to port these darn
systems. I left the MS/VB development scene in about '96 - needless to
say, most of what I learned 8 years ago is worthless now. :\ I've only
recently gotten into the whole SQL thing, so I still struggle with some
JOIN and GROUP BY statements. Eventually I'll get the hang of it.

Thanks again for the help!

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Justin Koivisto]

XMVP wrote:

"Justin Koivisto" <sp**@koivi.com> wrote in message
news:b3*****************@news7.onvoy.net...

XMVP wrote:

This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.

That being said, I am a moron of the 5th degree since linux, apache,
php, mysql, windows and access are all involved in the project...

I'm sorry to hear that you are just as moronic as I since you don't know
what the problem is either. :\

No relation to Microsoft! Except for a few brain dead Microsoft MVPs who
wander into this newsgroup looking for newbies to fleece.

The morons here usually stick to Access and Jet. Sometimes they go off on
MS SQL Server--way out of their league!

The project you describe is a huge mess. You (or your company) will never
make any money. No matter how much junk software you cobble together, you
still have a pile of junk. Take it all to the town dump. (That's the
answer to your problem, in case you missed it.)

I'll be sure to relay the message - especially since you're up on all
the details of the project.

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Tony Toews]

Justin Koivisto <sp**@koivi.com> wrote:

XMVP wrote:

This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.

XMVP is a well known troll. We heartily wish he would go away. Please ignore him.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Justin Koivisto]

Tony Toews wrote:

Justin Koivisto <sp**@koivi.com> wrote:

XMVP wrote:

This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.

XMVP is a well known troll. We heartily wish he would go away. Please ignore him.

Thought as much.

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Bard of Billings]

"Justin Koivisto" <sp**@koivi.com> wrote in message
news:67*****************@news7.onvoy.net...

Tony Toews wrote:

Justin Koivisto <sp**@koivi.com> wrote:

XMVP wrote:

This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.
XMVP is a well known troll. We heartily wish he would go away.

Please ignore him.
Thought as much.

Tony's from Duluth too!

[Larry Linson]

"Anonymous Sender" <an*******@remailer.metacolo.com> wrote

XMVP, IBM today announced. . .
Directly quoted from the headers of the referenced post:
Comments: This message did not originate
from the Sender address above. It was re-
mailed automatically by anonymizing remailer
software.

Just another alias, or sockpuppet, of The Resident Troll, as is the "XMVP"
to whom this alias addresses the message.

[XMVP]

"Anonymous Sender" <an*******@remailer.metacolo.com> wrote in message
news:78******************************@remailer.met acolo.com...

In article <Ut********************@vnet-inc.com>
"XMVP" <ac***********@hotmail.com> wrote:

"Justin Koivisto" <sp**@koivi.com> wrote in message
news:b3*****************@news7.onvoy.net...

XMVP wrote:
> This newsgroup is for _Access Morons_ not MySQL Morons.

With responses like this, it's no wonder people think I'm crazy for
actually posting to a MS-related newsgroup.

That being said, I am a moron of the 5th degree since linux, apache,
php, mysql, windows and access are all involved in the project...

I'm sorry to hear that you are just as moronic as I since you don't know what the problem is either. :\

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

No relation to Microsoft! Except for a few brain dead Microsoft MVPs who wander into this newsgroup looking for newbies to fleece.

The morons here usually stick to Access and Jet. Sometimes they go off on MS SQL Server--way out of their league!

The project you describe is a huge mess. You (or your company) will never make any money. No matter how much junk software you cobble together, you still have a pile of junk. Take it all to the town dump. (That's the
answer to your problem, in case you missed it.)

[insert Tony Toews boilerplate here]

XMVP, IBM today announced some kinduv new-fangled Linux-oriented
database thingamabob. Do you know if it is intended to be an Access
killer? Thank you, and your directness is appreciated.

That would be DB2 Version 8.2, AKA "Stinger." But AFAIK, it ain't "open
farce." That'd be "Cloudscape," released earlier this month. DB2 has a
mom and pop version for $500 then it shoots to $25K for the enterprise
version. Cloudscape (cute name, huh?) is actually middleware meant to be
embedded in a project, usually some Java junkpile. It's supposedly a fully
functional little (2Mb) database. It's also open farce.

Maybe you saw where IBM was going to use the AMD dual-core? That's because
we're going to be running "real" operating systems next to junk operating
systems in the future. Intel will go multi-core next year and by 2006, 60%
of all Intel CPU's with be multi-core. So you can run your good stuff in
one core and your open farce stuff in another core.

Apache-DB2-Linux-MySQL--All junk for the "alternative people" which are the
LOST ONES like Justin here. They're just goofy and poor.

And besides all that junk is so far behind the database curve it's pathetic.
I've been testing betas of Visual Studio 2005 and SQL Server 2005 for about
a month now and it's like whoa dude--what a ride! The alternative people
with their open farce stuff can eat my dust!

[Arvin Meyers]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:es********************************@4ax.com...

Tony the Twinkie, pasting together
The words of a post that no one will hear.
No one comes near.
Look at him working, pulling his pud
In the night when there's nobody there.
What does he care?

All the Access homos,
Where do they all come from?
All the Access homos,
Where do they all belong?

[Justin Koivisto]

XMVP wrote:

Apache-DB2-Linux-MySQL--All junk for the "alternative people" which are the
LOST ONES like Justin here. They're just goofy and poor.

And besides all that junk is so far behind the database curve it's pathetic.
I've been testing betas of Visual Studio 2005 and SQL Server 2005 for about
a month now and it's like whoa dude--what a ride! The alternative people
with their open farce stuff can eat my dust!

....like Google, Amazon, eBay, Pepsi, Yahoo who all use PHP in their
websites and Apple - the base their OS X operating system is a
open-source BSD.

Check out how many "goofy and poor" we have:

http://www.securityspace.com/s_surve...408/index.html

--
Justin Koivisto - sp**@koivi.com
http://www.koivi.com

[Tony Toews]

>X-Abuse-Report: http://www.usenetabuse.com

The above posting was *NOT* by Arvin Meyers and is a forgery.

Please ignore the above posting and others which are somewhat bizarre.

Note to persons new to this newsgroup. One person, with many identities, is being
rather disruptive. If you see a bizarre posting it's probably the work of this
disruptive person..

Check the headers of the posting. If you see the following the posting likely can be
ignored. Of course, there will likely be other headers to be added to this list.

Comments: This message did not originate from the Sender address above.
It was remailed automatically by anonymizing remailer software.
or
X-Abuse-Report: ab***@teranews.com
or
Organization: Posted via Supernews, http://www.supernews.com
or
Organization: 100ProofNews.com - Unlimited Downloads - $8.95/Month
or
X-Complaints-To: ab***@vnet-inc.com
or
X-Abuse-Report: http://www.usenetabuse.com

You can also change your NewsReader program settings to ignore off-topic posts. See
http://www.hyphenologist.co.uk/killfile/ for more information.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm