Add Field which finds data from another table

[adrien]

Hi there



I am struggling with what i believe must be a basic query.

I am having an ORDER table with the following field: Order_Date, Customer, Product, Quantity
I am having another table which contains discounted promotion. DISCOUNT Table has the following field: Customer, Product, Start_Date, End_Date

Now I would like to add a colomn in my ORDER table (which could be a fresh table created by query) indicating if my order was having a DISCOUNT or not.

I tried with creating a query with both table and creating a field called "DISCOUNTED"

DISCOUNTED : iif([ORDER]![Order_Date] BETWEEN [DISCOUNT]![Start_Date] AND [DISCOUNT]![End_Date];"yes";"no")

I linked my Product, Customer on both table but it gives me a table without all the orders. Out of 162,614 I am now having only 3,182
If I remove the relation between client and product on both table, I am having 6,992,875 row...

Guidance would be much appreciated.

Thanks

[June7]

Don't use INNER JOIN in the query, try LEFT or RIGHT (whichever is 'all records from Orders …').

An alternative is DLookup domain aggregate function.

[adrien]

Of course!!
Seems to work... Many thanks

[adrien]

I need to narrow my criteria.
I actually have DISCOUNT applied for Client or Group Clients.
I need to INNER JOIN LEFT for Product from both table but also for Client OR Group Client.
I tried this but it results in more rows than initially

SELECT Ajout_Groupe_Client_a_Commande.*, IIf([Ajout_Groupe_Client_a_Commande]![Date_Commande] Between [Actions]![Debut_Action] And [Actions]![Fin_Action],"yes","") AS [En Action]
FROM Ajout_Groupe_Client_a_Commande LEFT JOIN Actions ON ((Ajout_Groupe_Client_a_Commande.Autre_Code = Actions.Code_Client) OR (Ajout_Groupe_Client_a_Commande.Groupe_Client = Actions.Groupe_Client)) AND (Ajout_Groupe_Client_a_Commande.Article = Actions.Article);

I am getting 243274 rows while i have on Ajout_Groupe_Client_a_Commande 162614

Thanks for your support

[June7]

I have never used OR in a JOIN clause. Why are you? Any records meet both criteria of the OR?

[adrien]

Thanks June7

Here draft sample of my data

Order Table. (The discount fild is what I want to add with my query, based on DISCOUNT table )

ID	Order_Date	Client	Group_Client	Product	Qty	Discount
1	18.03.2019	xxx	GroupA	ProductA	3	yes
2	19.03.2019	yyy	GroupA	ProductB	5	yes
3	20.03.2019	zzz	GroupB	ProductA	7

DISCOUNT Table

ID	Start_Date	End_Date	Product	Client	Group_Client
	16.03.2019	20.03.2019	ProductA	xxx
	16.03.2019	20.03.2019	ProductB		GroupA
	16.03.2019	20.03.2019	ProductA	zzz

We can see that product B is discounted to the GroupA, which include client xxx and yyy but not zzz
I cannot used JOIN with ON for Products AND for Client AND for Group_Client. It must be either Client OR Group Client...

Any suggestion?

Best

[June7]

Forget about trying to join tables. What are the rules for determining whether order receives discount? How do you evaluate this in your head? Look at an order and what decision steps do you follow to make this determination?

[adrien]

Hey,
There is no rule to determine if my order received a discount or not.
It is more to show if my order had been issued with a discount or not. Based on my discount table...

[June7]

That makes NO sense to me. How does a record in discount table support there was discount applied to an order? There has to be rules. You are attempting to apply rules with table joins and IIf() in query.

The Discount table also makes no sense. Client xxx has Product A and GroupA in Order but there is no record in Discount for GroupA and ProductA for client xxx. So why do you say order ID 1 has discount?

[adrien]

Sorry for the confusion

My DISCOUNT Table is given and I am trying to see in my Order Table the rows where there were a discount applied.

The Discount table shows discount which applies for a product between given dates and to either a specific cutomer OR the group of customer...

Hence, I am seeing that it applies to the Client only OR the Group_Client


An alternative would be to modify my discount table, duplicate each row where the discount applies in a Client_Group and duplicate to for each customer in such group...
But how to make it happen automatically.


Thanks

[June7]

Is this right track:

1. look for client and product match within date range

if that fails then

2. look for product and group match within date range

However, client zzz is within date range and matches client and product yet you say it does not have discount. BETWEEN AND will be inclusive for the begin and end dates.

Consider:

SELECT DISTINCT Orders.ID, Orders.Client, Orders.Order_Date, Orders.Product, Orders.Group_Client, Orders.Qty, [Order_Date] Between [Start_Date] And [End_Date] And (([Orders].[Client]=[Discounts].[Client] And [Orders].[Product]=[Discounts].[Product]) Or ([Orders].[Product]=[Discounts].[Product] And [Orders].[Group_Client]=[Discounts].[Group_Client])) AS T
FROM Orders INNER JOIN Discounts ON Orders.Product = Discounts.Product
WHERE ((Not ([Order_Date] Between [Start_Date] And [End_Date] And (([Orders].[Client]=[Discounts].[Client] And [Orders].[Product]=[Discounts].[Product]) Or ([Orders].[Product]=[Discounts].[Product] And [Orders].[Group_Client]=[Discounts].[Group_Client]))) Is Null));

If this doesn't work with a larger dataset, only alternative I can suggest is VBA custom function.

[adrien]

Thanks for your answer June7
It actually does not work and I must now, my first query is not working anymore... wanted to do the work in two different steps (first client and then group_Client). but i am not able to do that also...
will keep you posted...