Query for nonreturned order

[lizzywu]

Hi there,

This might be a simple question to you. But I am struggling with it.

I am trying to query for the orders which are not checked as return in my order tracking table. That is, if order 1 is made by customer 10001 on 1/3/2012, but the customer returns it on 1/10/2012, all order tracking records should not be selected. And I also want to restrict selection as any order made after 1/5/2012.

I attach the database here. Could you help me with it?

If you could provide me with a query setup, that would be great!

Thank you very much.

[bigroo]

Hi lizzywu,

Is this what you are looking for?

Thanks

[lizzywu]

No. What I'd like to have is the orders listed in my torder, which are made after 1/5/2012 and the orders which are returned should not show up either. This should give me the following list:

subject_id order_id order_content order_date
10003 3 clips 1/6/2012
10004 5 clips 1/9/2012
10006 8 paper 1/12/2012

Is it possible that you try it again for me? Really appreciate.

[bigroo]

Hi Lizzywu,

Ok, does this look right?

[lizzywu]

Thank you so much for your effort, Bigroo.

But since subject#10002 did return on 1/11/2012 as order#6 shows. Any order which is returned should not show up, so order#2 made by #10002 should not be in the results.

Could you please help me check again? Millions of thanks!

[lizzywu]

Also, another question is: how could I query for the maximum order# for each subject?

Thank you a lot.

[bigroo]

Hi Lizzywu,

Please review the DB, i added another field to torder named returned date, this way you have only one entry per order. Not too sure if this is the approach you wish!!

The query does show the correct results then.

As to you second question:

Also, another question is: how could I query for the maximum order# for each subject?

I dont follow what you wish to see? Do you wish total number of orders per subject?

Thanks

[lizzywu]

Thank you, Bigroo.

There was a field called order_date which indicates both order date and return date. The DB you attached gives the orders made after 1/5/2012. As subject#10002 returned on 1/11/2012 as order#6 shows, it should not show in the results.

Or, could you help me try querying for the orders made by each customer most recently?

Thanks a lot.

[orange]

I have AC2003 so can not look at your 2010 data base nor database attached as responses.
However,

a field called order_date which indicates both order date and return date

this is a poor set up. One field, one concept.

[lizzywu]

Orange, I understand the poor setup I made. But is it possible to query for the most recent order made by each customer if a customer can make more than one order? I tried to use last[order_date] and max[order_date]; neither of them worked.

Thank you.

[orange]

Something like this

SELECT Max(Order_Date) AS MaxOfOrder_Date
FROM YourTable
GROUP BY CustomerName;