Query For Sorting and group by based on 2 columns

[abi_learn_vba]

Hi,

I have below table which has data arranged in sequential order of date.

Table1</SPAN>
Date</SPAN>	ID</SPAN>
2/1/2013</SPAN>	A3</SPAN>
2/2/2013</SPAN>	A3</SPAN>
2/3/2013</SPAN>	A3</SPAN>
2/4/2013</SPAN>	A3</SPAN>
2/5/2013</SPAN>	A3</SPAN>
2/6/2013</SPAN>	A3</SPAN>
2/7/2013</SPAN>	A3</SPAN>
2/8/2013</SPAN>	A3</SPAN>
2/9/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A1</SPAN>
2/10/2013</SPAN>	A3</SPAN>
2/11/2013</SPAN>	A1</SPAN>
2/11/2013</SPAN>	A3</SPAN>
2/12/2013</SPAN>	A1</SPAN>
2/13/2013</SPAN>	A1</SPAN>
2/14/2013</SPAN>	A1</SPAN>
2/15/2013</SPAN>	A1</SPAN>
2/16/2013</SPAN>	A1</SPAN>
2/17/2013</SPAN>	A1 </SPAN>

<TBODY>
</TBODY>

Here i need Sort by Date and then Group by the ID together that my results of the query should look like the one below. Where A3 ID is grouped together until 2/11/2013 and A1 ID starts from 2/10/2013.

Query Results</SPAN>
Date</SPAN>	ID</SPAN>
2/1/2013</SPAN>	A3</SPAN>
2/2/2013</SPAN>	A3</SPAN>
2/3/2013</SPAN>	A3</SPAN>
2/4/2013</SPAN>	A3</SPAN>
2/5/2013</SPAN>	A3</SPAN>
2/6/2013</SPAN>	A3</SPAN>
2/7/2013</SPAN>	A3</SPAN>
2/8/2013</SPAN>	A3</SPAN>
2/9/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A3</SPAN>
2/11/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A1</SPAN>
2/11/2013</SPAN>	A1</SPAN>
2/12/2013</SPAN>	A1</SPAN>
2/13/2013</SPAN>	A1</SPAN>
2/14/2013</SPAN>	A1</SPAN>
2/15/2013</SPAN>	A1</SPAN>
2/16/2013</SPAN>	A1</SPAN>
2/17/2013</SPAN>	A1</SPAN>

<TBODY>
</TBODY>

I tried many using Order by and group by without success, can any one help me with a query for this?

Thanks in Advance
-Abi

 

[Joe4]

You just want to sort by ID first, then Date second.
If your issue is figuring out how to do this in the query builder, do the following:

1. Add your Date field, then your ID field, then the Date field again.
2. Click "Descending" on the Sort row under the ID field
3. Under the second Date field, Click "Ascending" on the Sort row
4. Under the second Date field, uncheck the "Show" box

 

[abi_learn_vba]

Hi Joe,
Thanks for the reply.

I can't use sort by ID first because, Sorting by desc will not always help. The ID can interchange between old date and new date, like something below. For the below table if i sort by desc then it will not get the desired results.

Table1</SPAN>
Date</SPAN>	ID</SPAN>
2/1/2013</SPAN>	A1</SPAN>
2/2/2013</SPAN>	A1</SPAN>
2/3/2013</SPAN>	A1</SPAN>
2/4/2013</SPAN>	A1</SPAN>
2/5/2013</SPAN>	A1</SPAN>
2/6/2013</SPAN>	A1</SPAN>
2/7/2013</SPAN>	A1</SPAN>
2/8/2013</SPAN>	A1</SPAN>
2/9/2013</SPAN>	A1</SPAN>
2/10/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A1</SPAN>
2/11/2013</SPAN>	A3</SPAN>
2/11/2013</SPAN>	A1</SPAN>
2/12/2013</SPAN>	A3</SPAN>
2/13/2013</SPAN>	A3</SPAN>
2/14/2013</SPAN>	A3</SPAN>
2/15/2013</SPAN>	A3</SPAN>
2/16/2013</SPAN>	A3</SPAN>
2/17/2013</SPAN>	A3</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

And query results should be

Query Results</SPAN>
Date</SPAN>	ID</SPAN>
2/1/2013</SPAN>	A1</SPAN>
2/2/2013</SPAN>	A1</SPAN>
2/3/2013</SPAN>	A1</SPAN>
2/4/2013</SPAN>	A1</SPAN>
2/5/2013</SPAN>	A1</SPAN>
2/6/2013</SPAN>	A1</SPAN>
2/7/2013</SPAN>	A1</SPAN>
2/8/2013</SPAN>	A1</SPAN>
2/9/2013</SPAN>	A1</SPAN>
2/10/2013</SPAN>	A1</SPAN>
2/11/2013</SPAN>	A1</SPAN>
2/10/2013</SPAN>	A3</SPAN>
2/11/2013</SPAN>	A3</SPAN>
2/12/2013</SPAN>	A3</SPAN>
2/13/2013</SPAN>	A3</SPAN>
2/14/2013</SPAN>	A3</SPAN>
2/15/2013</SPAN>	A3</SPAN>
2/16/2013</SPAN>	A3</SPAN>
2/17/2013</SPAN>	A3</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

Key here is duplicate occurance of a Date will indicate there is change in ID. And Oldest date's ID should always come first , in the above example oldest date is 2/1/2013 and it has A1, so it should always come first. Hope i was able to make sense of my requirement, it would good if you can give me a SQL query.

Thanks
-Abi

 

[Joe4]

I see what you are trying to do now. I do not know if there is any way to do that with SQL, as if you have a long list of data that changes ID many times, some times A1 will come first, and some times A3 will come first, depending on the prior records. So you may have mixed Ascending/Descending order in the same data list, depending on the previous ID records.

Like I said, I can't think of any way to do that in a straight-up query (in Access or SQL), using just the data you have there. The only thing I can think of is to create an "ordering" field in your table, and to loop through your records row-by-row (i.e. using DAO in VBA) to assign a value in this "ordering" field, then sort by that when you are done.

 

[AngelJ]

I must be missing what Joe4 is talking about. Based on post #3, this simple query returns the results posted.

SELECT * FROM Table1 ORDER BY ID, Date

 

[Joe4]

I must be missing what Joe4 is talking about. Based on post #3, this simple query returns the results posted.

Look at his first post. The code you posted will NOT return the records in the order that they want (you need to look at each example in BOTH the first and second posts).

 

[AngelJ]

Look at his first post. The code you posted will NOT return the records in the order that they want (you need to look at each example in BOTH the first and second posts).

Ah, I think I see now what I was missing.

In that case, does this work Abi?

SELECT Table1.*
FROM Table1
INNER JOIN
    (SELECT ID,
        MIN(Table1.[Date]) As [Date]
    FROM Table1
    GROUP BY ID) As MinDates
ON Table1.ID = MinDates.ID
ORDER By MinDates.[Date],
    Table1.[Date]

 

[abi_learn_vba]

AngelJ,

That was awesome. This is what exactly i was looking for.

Thank you very much for your help.

Thanks & Regards
-Abi

 

[Joe4]

I think maybe I was overthinking it, or making some assumptions about things that won't happen.

I thought that the ID can be used again. Like, in your first post, is it possible to have records with ID A3 with dates of 2/18/2013 and 2/19/2013?
If so, where should those go in your query?

 

[abi_learn_vba]

Joe,

If i take example from my first post, ID changes from A3 to A1 on 2/10/2013 and 2/11/2013. And the ID will not change back to A3 for that entire month, next change in happen only in the next Month. Result of my query will be always for one particular month, so that scenario is ruled out.


May be i was not clear with my entire requirement first time itself, sorry about that.

Thank you very much for your time to look into this.

Thanks
-Abi