Hi,
I have 2 queries, one showing returns with dates and the other showing sales with their sales dates (both have item number, customer name and sales person's name). For each entry in the return data, I want to find the matching sales date in the other query. I tried using the Dmax query with the criteria that (1) the item no, sales route and customer name are equal (2) the return date is greater than the sales date .... help!
My query is as follows:
SELECT Donotrun_Qry_returnsdata.Product_no, Donotrun_Qry_returnsdata.Delivery_quantity, Donotrun_Qry_returnsdata.Return_date, DMax([Delivery_date],[Donotrun_Qry_loaddata, [Donotrun_Qry_loaddata]![Code_sales]="[Donotrun_Qry_returnsdata]![Code_return]" And "[Donotrun_Qry_returnsdata]![Return_date]>[Donotrun_Qry_loaddata]![Delivery_date]") AS Expr1
FROM Donotrun_Qry_returnsdata INNER JOIN Donotrun_Qry_loaddata1 ON Donotrun_Qry_returnsdata.Code_return = Donotrun_Qry_loaddata1.Code_sales;
I have 2 queries, one showing returns with dates and the other showing sales with their sales dates (both have item number, customer name and sales person's name). For each entry in the return data, I want to find the matching sales date in the other query. I tried using the Dmax query with the criteria that (1) the item no, sales route and customer name are equal (2) the return date is greater than the sales date .... help!
My query is as follows:
SELECT Donotrun_Qry_returnsdata.Product_no, Donotrun_Qry_returnsdata.Delivery_quantity, Donotrun_Qry_returnsdata.Return_date, DMax([Delivery_date],[Donotrun_Qry_loaddata, [Donotrun_Qry_loaddata]![Code_sales]="[Donotrun_Qry_returnsdata]![Code_return]" And "[Donotrun_Qry_returnsdata]![Return_date]>[Donotrun_Qry_loaddata]![Delivery_date]") AS Expr1
FROM Donotrun_Qry_returnsdata INNER JOIN Donotrun_Qry_loaddata1 ON Donotrun_Qry_returnsdata.Code_return = Donotrun_Qry_loaddata1.Code_sales;