Solved Query to filter out all that has.. (1 Viewer)

[Number11]

So I wonder if someone could help.
I have a query that brings all Orders between a date range I am looking for a way to filter out any cancelled orders or amended orders when on the same day the order was completed

Fields I have:
Customer_AC
Order Date
Order Number
Order Status
Ordered Item
Order Notes

The result would be if on the same day for the same customer an order was cancelled and another was complete only show the completed but if no completed show all other status?
Is that possible?

 

[arnelgp]

create a query from your table (customer_ac, order date, order status) whose status is "cancelled" or "amended"

create new query from your query (post#1) and the new query.

select query1.* , query2.[order status] frrom query1 left join query2 on query1.customer_ac = query2.customer_ac
and query1.[order date] = query2.[order date] where query2.[order status] is null;

 

[Number11]

Maybe its best to make a table and append the completed then Cancelled and then amended with some indexing to stop any thing being added when on the same date for the same customer order was competed?

 

[Number11]

create a query from your table (customer_ac, order date, order status) whose status is "cancelled" or "amended"

create new query from your query (post#1) and the new query.

select query1.* , query2.[order status] frrom query1 left join query2 on query1.customer_ac = query2.customer_ac
and query1.[order date] = query2.[order date] where query2.[order status] is null;

Ok so i created two queries as you suggested and then a 3rd with the "
select query1.* , query2.[order status] frrom query1 left join query2 on query1.customer_ac = query2.customer_ac
and query1.[order date] = query2.[order date] where query2.[order status] is null;

got zero results?

 

[arnelgp]

sorry, but on my test, it is returning records that have no "cancel" or "amendment"

 

[Number11]

sorry, but on my test, it is returning records that have no "cancel" or "amendment"

ill re-check the query, but i do want to see any orders cancelled or amended if not completed for same day same customer?

 

[Number11]

No still not working

Query has Criteria set on Order Status as "Cancelled or Amended"
Query 2 has no Criteria set

The new query

select query1.* ,query2.[Order Status] from query1 left join query2 on query1.customer_ac = query2.customer_ac
and query1.[Order Date] = query2.[Order Date] where query2.[Order Status] is null;

Results Zero?

 

[arnelgp]

Query has Criteria set on Order Status as "Cancelled or Amended"
Query 2 has no Criteria set

if "Query" is the query you have in post #1, then it's the other way around.
query2 will have a criteria while query, no criteria.

 

[Number11]

if "Query" is the query you have in post #1, then it's the other way around.
query2 will have a criteria while query, no criteria.

So Query 1 has no criterial now
and
Query 2 has the Criteria set on Order Status as "Cancelled or Amended"

results are now only showing customers with a completed order leaving out those customers who have orders showing as Cancelled and Amended and no completed?

 

[Number11]

So need to work out a way that the query will show all customers with order status Cancelled, Aborted & Completed. But if an order status is completed on the same day as cancelled or amended only show the completed?

 

[Number11]

so was thinking maybe the way around this is to have 1 query that copies all completed to a new table and then have a second query that will copy all cancelled and Amended and if in the new table a completed is found not to copy the cancelled and or amended if found for the same customer on the same date possible?

 

[arnelgp]

ok, back.

on your first Query (say, query1) add this Criteria in SQL view, if the status for Complete is not "complete", change it in the expression
below:

...Where IIf(DCount("1","query1","[customer_ac]='" & [customer_ac] & "' and [order date] = " & Format$([order date],"\#mm\/dd\/yyyy\#") & " and [order status] = 'complete'")>0 And [order status]="complete",True,IIf(DCount("1","query1","[customer_ac]='" & [customer_ac] & "' and [order date] = " & Format$([order date],"\#mm\/dd\/yyyy\#") & " and [order status] = 'complete'")=0,True,False))

 

[Number11]

ok, back.

on your first Query (say, query1) add this Criteria in SQL view, if the status for Complete is not "complete", change it in the expression
below:

...Where IIf(DCount("1","query1","[customer_ac]='" & [customer_ac] & "' and [order date] = " & Format$([order date],"\#mm\/dd\/yyyy\#") & " and [order status] = 'complete'")>0 And [order status]="complete",True,IIf(DCount("1","query1","[customer_ac]='" & [customer_ac] & "' and [order date] = " & Format$([order date],"\#mm\/dd\/yyyy\#") & " and [order status] = 'complete'")=0,True,False))

Ok sorry so i am not sure what you mean

I have created two queries as you stated:

So Query 1 has no criterial now
and
Query 2 has the Criteria set on Order Status as "Cancelled or Amended"

and then a 3rd query also as you stated...

select query1.* ,query2.[Order Status] from query1 left join query2 on query1.customer_ac = query2.customer_ac
and query1.[Order Date] = query2.[Order Date] where query2.[Order Status] is null;

do i add the new code to the query 1 or the 3rd query?

 

[arnelgp]

here is a sample i have a table (named query1). its structure is same as in post #1
of your query?
see qryFinal.

 

[Number11]

here is a sample i have a table (named query1). its structure is same as in post #1
of your query?
see qryFinal.

Getting

Data type mismatch in criteria expression. (Error 3464)​

 

[arnelgp]

you check the datatype of your table/query and compare it
to my table.
i am using Text customer_ac and other fields.

 

[Number11]

you check the datatype of your table/query and compare it
to my table.
i am using Text customer_ac and other fields.

Ok thanks Yes that was the issue, so i have just changed my Table from Number to Text on field "Customer_Ac" and its now all working (is there a way to get this working as a Number field - I wonder?)

 

[arnelgp]

change the Where clause of the query, by removing the single quote (') from [customer_ac]:

WHERE (((IIf(DCount("1","query1","[customer_ac]=" & [customer_ac] & " and [order date] = " & Format$([order date],"\#mm\/dd\/yyyy\#") & " and [order status] = 'complete'")>0 And [order status]="complete",True,IIf(DCount("1","query1","[customer_ac]=" & [customer_ac] & " and [order date] = " & Format$([order date],"\#mm\/dd\/yyyy\#") & " and [order status] = 'complete'")=0,True,False)))<>False))
ORDER BY query1.Customer_AC;

 

[Number11]

Thanks so much your amazing