SQL string Syntaxerror(Missing Operator) (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:34
Joined
Feb 28, 2001
Messages
19,374
I see a complex set of relationships here but I THINK (and no, I won't put money down on a bet for this) is that you have one contrary relationship in that diagram. (Maybe two.) I remember when I had some seriously convoluted relationships in the past, this was one of the things that bit me in the butt really hard.

Your original query way back in post #1 SEEMS to be based on tbl_Kontakte as the thing to which everything else links, directly or indirectly. BUT for this kind of query, your dependencies need to be uniformly directed - and one (or two) of them are not.

Between tbl_ArtikelGruppenRabatt and tbl_ArtikelGruppe, your dependency (one/many) goes the wrong way when compared to other relationships. Also, tbl_Kontakte and tbl_Preislisten have the same problem.

Stated another way, you want to find all of the children of tbl_Kontakte from related table tbl_ArtikelGruppenRabatt - and this should be easy. But then you start relating to other tables and it APPEARS that tbl_ArtikelGruppenRabatt is a child of tbl_ArtikelGruppe, and the one/many (or if you prefer, the dependency) flows the wrong way.

I double-checked the original query and sure enough, both of the "wrong way" relationships are being exercised. Like I said earlier, I wouldn't put money on it, but I believe Access doesn't like that mixed direction of dependencies.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:34
Joined
May 21, 2018
Messages
4,763
@silentwolf,
I disagree with the @The_Doc_Man, and do not think anything is wrong with the direction of the relationship. It all looks correct to me. It is also very well done. You have a very solid naming convention, well identified primary and foreign keys, and every relation is from a primary key to an identified foreign key. Does this query work? If so, have you tried creating an outer join just from tbl_Kontakte to tbl_artikelgruppenrabbat? I think everything else can remain. If not try also making an outer join from tbl_Kontakte to tbl_Preislisten. Both join need to return all tbl_Kontakte.
If not can you post the real error? The German is fine.
 

silentwolf

New member
Local time
Today, 02:34
Joined
Jun 12, 2009
Messages
25
Hi guys,

thanks again for your input!
I did had a very instructor who had a look over the relationship and the model and he aproved it ;)

In the query I did add one more Criteria
Code:
WHERE (((tbl_Kontakte.Kont_ID)=123) AND ((tbl_ArtikelStamm.ArtS_ArtG_IDRef)=[tbl_ArtikelGruppe].[ArtG_ID]) AND ((tbl_ArtStammPreisliste.ArtSPreis_PreisL_IDRef)=[tbl_Kontakte].[Kont_PreisL_IDRef]));

Now only those records assosiated to the Contact Preisliste are showing. Which is what I want.

Attached is a query where I do get all Kontakts regardless if they do have any "ArtikelGruppenRabatt"
In plain english when they have no Discount in any group those Contacts only get the "Prieces" Assosiated in the "Preisliste"

I do get in the query attached all contacts regardless if they do have a discount or not and for those who have serveral discounts they show
x amount of time.

But how to put those two queries together?
And what field do I need to link if I do put those together?

Included the Error message.. The Error is all well and good it says there are already to many outer joins well ok but how to I do it so it does work?

Sorry for beeing a little helpless here!
 

Attachments

  • qry_ContactDiscountGroup.JPG
    qry_ContactDiscountGroup.JPG
    43.6 KB · Views: 6
  • Query_001.JPG
    Query_001.JPG
    69.5 KB · Views: 5
  • Error_001.JPG
    Error_001.JPG
    24.3 KB · Views: 6

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Sep 12, 2006
Messages
14,413
It may have been what @MajP suggested - the need for more spaces. I am very liberal with spaces when constructing SQL directly.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:34
Joined
Sep 12, 2006
Messages
14,413
Hi guys,

thanks again for your input!
I did had a very instructor who had a look over the relationship and the model and he aproved it ;)

In the query I did add one more Criteria
Code:
WHERE (((tbl_Kontakte.Kont_ID)=123) AND ((tbl_ArtikelStamm.ArtS_ArtG_IDRef)=[tbl_ArtikelGruppe].[ArtG_ID]) AND ((tbl_ArtStammPreisliste.ArtSPreis_PreisL_IDRef)=[tbl_Kontakte].[Kont_PreisL_IDRef]));

Now only those records assosiated to the Contact Preisliste are showing. Which is what I want.

Attached is a query where I do get all Kontakts regardless if they do have any "ArtikelGruppenRabatt"
In plain english when they have no Discount in any group those Contacts only get the "Prieces" Assosiated in the "Preisliste"

I do get in the query attached all contacts regardless if they do have a discount or not and for those who have serveral discounts they show
x amount of time.

But how to put those two queries together?
And what field do I need to link if I do put those together?

Included the Error message.. The Error is all well and good it says there are already to many outer joins well ok but how to I do it so it does work?

Sorry for beeing a little helpless here!
One way is to extract the base data to a staging table, then start again from the staging table. Or even add a "select" field to the staging table, and set the select flag on the rows you need. You don't have to get everything in one query.
 

silentwolf

New member
Local time
Today, 02:34
Joined
Jun 12, 2009
Messages
25
Hi gemma-the-hausky,

One way is to extract the base data to a staging table, then start again from the staging table. Or even add a "select" field to the staging table, and set the select flag on the rows you need. You don't have to get everything in one query.
So you mean create a new table from a base query and use this new created table for a new query?
I tried with have it seperated into two "queries" but as I mentioned not really getting the right amount of records.

But I will kepp trying .. There must be a way of getting this working.. just still not sure how.

Thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:34
Joined
May 21, 2018
Messages
4,763
From what I see this query is truly very simple and straightforward. My guess whatever you are doing wrong is probably a very simple mistake. Saving the results to a temp table is IMO an unnecessary level of complexity and overkill that I really doubt you need. If I understand the error correctly, that is an ambiguous outer join error.
So what it is recommending is that you first save
qryContactDiscountGroup
now try using qryContactDiscountGroup in another query bringing in all the other tables to the right of tblArtikelGruppe.

So you will joint qryContactDiscountGroup to tbl_ArtikelStamb on
qryContactDiscountGroup.ArtG_ID = tbl_ArttikelStamm.arts_ArtG_IDRef

You could also build a another query from tbl_artikelStamm onwards. If that is good then join the two queries.

The other way to do this is that you "flow" your outer joins in one direction. That is the way I think of it. So if an arrow goes into ArtikelStamm then all the arrows go out of it. Therefore an arrow flows into tbl_artStammPreisliste and flows out into tbl_steursatz. The arrows continuously radiate outwards is the way I think of it.
 

silentwolf

New member
Local time
Today, 02:34
Joined
Jun 12, 2009
Messages
25
Hi thanks again for your help!

So I did the first option as you mentioned or at least how I understood it.

Again that newly created join I can not change to an outer join.

This query shows again no records "no Articles" where no discount is given.

I give up on this soon pff,... I am also sure it must be easy but not able to get it working ...
 

Attachments

  • qry_Joint_V001.JPG
    qry_Joint_V001.JPG
    51.3 KB · Views: 2

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:34
Joined
May 21, 2018
Messages
4,763
Can you post a sample database with only these tables? No forms, reports, or code. You can import into new database. You can remove fields not needed in the query if you want
 

silentwolf

New member
Local time
Today, 02:34
Joined
Jun 12, 2009
Messages
25
Hi MajP,

just a few data in it hope this is clear?

Made a few mistakes thats why the ID's are a not in order.

Many thanks to you!!
 

Attachments

  • AgmaTest.zip
    33.3 KB · Views: 4

silentwolf

New member
Local time
Today, 02:34
Joined
Jun 12, 2009
Messages
25
Hope a zip file works here ok?
I will be away for a few hours so no rush ...

Cheers again!!
 

Users who are viewing this thread

Top Bottom