This is killing me, and I know it's not that difficult.
I have a table called tmp_Order_Lines (an order history file).
This contains all of the order lines for all orders placed by all clients for all products.
Two fields of interest are ID_Code (original, huh?) and Enterdt.
ID_Code identifies a client's customer number and the product ordered.
Enterdt is the Date when an order was placed.
Here is an example:
ID_Code Enterdt
101_abc 08/01/2006
101_abc 07/01/2006
101_abc 06/01/2006
101_abc 05/01/2006
101_xyz 07/15/2006
101_xyz 06/15/2006
101_xyz 05/15/2006
102_abc 08/01/2006
102_abc 06/15/2006
Now that I've thoroughly bored you with my simple examples, here is my quandry:
I need to return the top 2 results by Enterdt for each ID_Code (grouped by ID_Code and sorted DESC by Enterdt).
Obviously, the top keyword function is Access isn't going to do me much good on its own.
I am trouble making the proper query return the top 2 values for each group.
I even looked at trying to rank them in descending order by Enterdt (Grouping by ID_Code) and pulling the first 2 ranked results, but had trouble with that.
I would actually prefer to rank them and return only the first two results, because we would have more versatility in letting a user modify the number of results he/she wants to see.
If anyone can help, I certainly would appreciate it.
If you need any further details, please let me know.
Thanks,
Randy
I have a table called tmp_Order_Lines (an order history file).
This contains all of the order lines for all orders placed by all clients for all products.
Two fields of interest are ID_Code (original, huh?) and Enterdt.
ID_Code identifies a client's customer number and the product ordered.
Enterdt is the Date when an order was placed.
Here is an example:
ID_Code Enterdt
101_abc 08/01/2006
101_abc 07/01/2006
101_abc 06/01/2006
101_abc 05/01/2006
101_xyz 07/15/2006
101_xyz 06/15/2006
101_xyz 05/15/2006
102_abc 08/01/2006
102_abc 06/15/2006
Now that I've thoroughly bored you with my simple examples, here is my quandry:
I need to return the top 2 results by Enterdt for each ID_Code (grouped by ID_Code and sorted DESC by Enterdt).
Obviously, the top keyword function is Access isn't going to do me much good on its own.
I am trouble making the proper query return the top 2 values for each group.
I even looked at trying to rank them in descending order by Enterdt (Grouping by ID_Code) and pulling the first 2 ranked results, but had trouble with that.
I would actually prefer to rank them and return only the first two results, because we would have more versatility in letting a user modify the number of results he/she wants to see.
If anyone can help, I certainly would appreciate it.
If you need any further details, please let me know.
Thanks,
Randy