Solved Pricing Problem

asteropi

Member
Local time
Today, 14:49
Joined
Jun 2, 2024
Messages
144
So I have 2 sets of prices (retail and wholesale) and 2 sets of customers (retail and wholesale)

I created an OrderPrice query so that it will recognise the type of customer and immediately draw the proper set of prices. So far so good

Then this query I load on another query that has all the order details (OrderDetailQ)
The moment I do so, I can't add any products on my order. If I delete that function, it works fine.

What could possibly be the problem??

2.JPG
3.JPG


1.JPG
 
use Left Join and not Inner Join on your query.
right click on the "relationship line" and choose 2nd or 3rd option.
 
I tried that and I get this error
and I don't know how to do what it asks.

1724231508064.png

use Left Join and not Inner Join on your query.
right click on the "relationship line" and choose 2nd or 3rd option.
 
try it again until all the arrows are like in the picture:

1.JPG
 

Attachments

  • 1.JPG
    1.JPG
    167.2 KB · Views: 146
Please try to post a sample file so that we can directly experience what is happening and then be able to help you.
 
I will try to explain from a more theoretical viewpoint because I can't read Greek. The error message uses the word "ambiguous" and that tells you a LOT.

A JOIN merely aligns a table's records based on a relationship between one or more fields in each table. In your case, each relationship is based on a single field from each table, so that part of the problem is easy.

To build a query, Access has to decide which keys to use to align the tables in the implied JOIN. A query is valid for making updates to its underlying tables if and only if there is no ambiguity in deciding which records must be updated. If you have multiple relationships leading from the parent to the targeted child table, that ambiguity suggests that there is more than one way to logically specify the targeted record.

Let's look at the specific problem you showed us. Your relationship diagram has three tables near the top border. Let's call them the Left, Center, and Right tables because I apologize that I can't reproduce the Greek names here. You have two more tables that we can call Lower-Left and Lower-Right. They DO NOT enter into this discussion because they have no ambiguity that I can see.

I'm going to use the term "logic path" to describe the query's answer to this question: "Which fields shall I use to align the tables with each other based on their relationship?" You have a logic path from the Left table to the Right table that bypasses the Center table i.e. a direct Left-to-Right JOIN. You have ANOTHER path that goes from Left through Center and then to Right. They use different keys for their respective relationships. The direct path uses the PK of the Left table to the 1st field of the Right table. The indirect path uses the PK of the Center table to the 2nd field of the Left and Right tables.

The ambiguity is that potentially you would get two different sets of records in a JOIN involving all three tables. One set comes when you directly JOIN the Left-to-Right tables. The other set of records comes when you JOIN Left-to-Center-to-Right, and that is potentially different because they use different keys. Access hates that ambiguity so that blocks updates on the query and any form that USES the query.

It doesn't even matter if, by chance or design, you would get the same sets of records in both cases. Access is telling you that it can't decide until it actually does the query - but the error comes during the query's pre-analysis phase to see how to do the query in the first place. In other words, it is telling you that it can't build the query without first building the query, a pre-condition of its own existence. This is because the first step in query analysis is to identify the source records - but Access is not sure how to do that.

The solution is to sever one of the two linkages from the Center table. Ask yourself which of the two links is the dominant link - the one that REALLY matters - and drop the other relationship. It LOOKS like the 2nd field in the Left and Right tables are the same name as the PK of the middle table. If they are the same field then decide which field - Left table or Right table - drives that particular JOIN. Keep that relationship and drop the other one.

I hope that is clear enough.
 
Ok so I recreated the whole database in English (minus a few greek labels)
And I realised I was taking a detour, when I could just solve my problem with an IIF command.

There is some problem with the math (math is not mathing lol), but I solved that too with another IIF command.
In any case, thank you guys.
I need to figure out how to give different sequential orders when they come from wholesale or retail. Is there any tutorial about that?
 

Users who are viewing this thread

Back
Top Bottom