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