select tablename.fieldname yet error 3079

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bas Cost Budde - 08 Nov 2004 09:13 GMT

This is my query:

SELECT DISTINCT relatie.relatieID
FROM Relatietype INNER JOIN relatie ON Relatietype.relatietypeID =
relatie.relatietypeID
WHERE (((Relatietype.omschrijving)='promotie') AND
((Relatietype.status)<>'weg'));

These are my tables (I left out some text fields)
Relatie(relatieID,naam,relatietypeID,opmerking,status,username,userdate)
RelatieType(relatietypeID,omschrijving,flags,status,username,userdate)

Reference exists on relatietypeID.

Access now complains that the field [status] can refer to more than one
table in the FROM clause. All right--that's why I put in the table name
[relatietype], right? I completely fail to see what is wrong.

Bas Cost Budde
Holland

If you want to use my email, the q should be a t

Reply to this Message

Allen Browne - 08 Nov 2004 11:36 GMT

Hello Bas. Haven't seen you around the groups recently. Good to hear from
you again.

Your query statement looks fine to me too. It looks like Access is confused.
If this is Access 2000 or later, my money would be on Name AutoCorrect as
the cause of the confusion. There's well over a dozen bugs listed at:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html
and we are still discovering more.

So, uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact.

If that does not solve the problem, please post back again.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> This is my query:
>
[quoted text clipped - 18 lines]
>
> If you want to use my email, the q should be a t

Reply to this Message

Bas Cost Budde - 08 Nov 2004 12:05 GMT

> Hello Bas. Haven't seen you around the groups recently. Good to hear from
> you again.
>
> Your query statement looks fine to me too. It looks like Access is confused.
> If this is Access 2000 or later, my money would be on Name AutoCorrect

It's 97. I was wondering wether I included enough information :-)

There IS AutoCorrect in my Tools menu, and that DOES solve the problem.
Not that I understand how or why...

"My car won't shift to 5th gear"
"Have you got a yellow suitcase in the back?"
"Why, yes."
"Try moving it to the left side of the compartiment"

...

Reply to this Message

David W. Fenton - 08 Nov 2004 23:18 GMT

>> Hello Bas. Haven't seen you around the groups recently. Good to
>> hear from you again.
[quoted text clipped - 12 lines]
> "Why, yes."
> "Try moving it to the left side of the compartiment"

My guess is that there was some other saved property referring to
[status], like a sort order or filter, that doesn't show up in SQL
view. My bet is that if you'd cut and pasted the raw SQL into a new
query it would have worked.

Signature

David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Reply to this Message

Bas Cost Budde - 09 Nov 2004 11:35 GMT

> My guess is that there was some other saved property referring to
> [status], like a sort order or filter, that doesn't show up in SQL
> view. My bet is that if you'd cut and pasted the raw SQL into a new
> query it would have worked.

Strong suspicion! I have indeed noticed that this causes problems now
and then.

Here, though, I have a query that I just created using CreateQuerydef,
and I can't find any property like that.

Copying and pasteing works. I use the query in an automated process,
though, so this is not a ready solution.

Please note that unchecking AutoCorrect worked, but only initially. I
seem to have changed something. Next time the query was created, it got
me the same error.

Reply to this Message