Solved Pricing Problem

asteropi

Member
Local time
Tomorrow, 01:47
Joined
Jun 2, 2024
Messages
122
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: 40
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.
 
The moment I do so, I can't add any products on my order. If I delete that function, it works fine.
The schema has a problem because the top right table relates to two tables creating a loop. The relationship should be top -> middle-> right and the direct relationship between top and right should be removed.

We can't see the query but a query of the three top tables should produce an updateable join and the three bottom tables should produce an updateable join. If your query puts those two queries together, that is where you are generating the problem because you are creating a Cartesian Product. The number of rows returned will be top row query * bottom row query and that will result in table data being duplicated. I have explained this with a very simple concept that is not business related. You have Students, their classes and their vehicles. So students->Classes makes sense and so does Students->vehicles but:
Students->Classes
Students->Vehicles is two parallel relationships and will result in duplicating data and therefore the query will be not updatable. the records would look like

Tom, Math, Scooter
Tom, English, Scooter
Tom, Math, Bicycle
Tom, English, Bicycle

Notice that tom's data appears 4 times but the other data appears twice each. The problem is that although students are related to both classes and vehicles, classes and vehicles are not related to each other so these are parallel hierarchies. You can create a query like this but it will always result in strange duplications and will not be updateable.

If you can get either query to return a single record, then the problem will go away. So, add criteria to only return Math records. You end up with only two records and no duplication.
Tom, Math, Scooter
Tom, Math, Bicycle
 
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