SQL string Syntaxerror(Missing Operator) (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 28, 2001
Messages
26,999
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, 09:51
Joined
May 21, 2018
Messages
8,463
@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

Active member
Local time
Today, 06:51
Joined
Jun 12, 2009
Messages
545
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: 105
  • Query_001.JPG
    Query_001.JPG
    69.5 KB · Views: 103
  • Error_001.JPG
    Error_001.JPG
    24.3 KB · Views: 110

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Sep 12, 2006
Messages
15,613
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, 13:51
Joined
Sep 12, 2006
Messages
15,613
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

Active member
Local time
Today, 06:51
Joined
Jun 12, 2009
Messages
545
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, 09:51
Joined
May 21, 2018
Messages
8,463
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

Active member
Local time
Today, 06:51
Joined
Jun 12, 2009
Messages
545
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: 103

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:51
Joined
May 21, 2018
Messages
8,463
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

Active member
Local time
Today, 06:51
Joined
Jun 12, 2009
Messages
545
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: 124

silentwolf

Active member
Local time
Today, 06:51
Joined
Jun 12, 2009
Messages
545
Hope a zip file works here ok?
I will be away for a few hours so no rush ...

Cheers again!!
 

silentwolf

Active member
Local time
Today, 06:51
Joined
Jun 12, 2009
Messages
545
Hi guys,

sorry for not responding to this threat for such a long time but I was not able to work on it due to work.

However I am now able to get back into it and was working on it for a bit but still have some issues I can not seam to get working.

To get back into it a short reminder what the DB supose to do or what Data I do have and need to sort.

1. Every "Kontakt" (Contact) is mapped to a "Preisliste" (Pricelist) which are the contacts base Prices so to say.
2. So there can be on or many contacts be related to one parcticular pricelist
3. Every ArtikelStamm(BaseArticle) belongs to a ArtikelGruppe(ArticleGroup)
4. Each Contact has Rabatt(Discount) or not on a particular ArtikelGruppe(ArticleGroup)
5. Each ArtikelStamm(BaseArticle) belongs to a ArtikelGruppe(ArticleGroup)

The issue I still have is how to I get when I select a Contact who likes to purchase a Article to get the Price of that particular Article
with his "Pricelist" and his Discount of that particular Article

At the moment I did manage to get of each contact his corresponding Pricelist but not the discount for that Article.


I did include two queries and Data in a very simple and small matter.
Maybe someone could be so kind and have a look over it and help me with getting it up and running?


Many thanks and happy new year to all in tha forum!

Nice greetings
Albert
 

Attachments

  • AGMA_Test_DB1.zip
    38.8 KB · Views: 84
Last edited:

Users who are viewing this thread

Top Bottom