Sorting Question? 2

[marcoman44]

I have two fields 1)Date 2)Name. I am trying to sort by date in ASC order and then by Name. I am having trouble keeping the name together when date in the primary sort. For example this is what I am looking for: (Any help is greatly appreciated).

02/05/2005 Smith,Bob
02/07/2005 Smith,Bob
02/08/2005 Smith,Bob
02/06/2005 Jones,John
02/07/2005 Jones,John

 

[dhookom]

One method is to create a totals query like:
SELECT [TheName], Min([TheDate]) as MinDate
FROM tblTheTable
GROUP BY [TheName];

Then include this query in your first query and join the [TheName] fields and sort by [MinDate] then [TheDate].

This query would not be updateable.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[traingamer]

Then you really want to sort by name, date not the other way around.

select date, name from table 
where ...
order by name, date

traingamer

 

[marcoman44]

Thanks for the help. The problem is that the date has to be the first on the list then name.

 

[marcoman44]

The goal is to have the dates in Ascensing order and then group it by the Name/Person_ID.

02/05/2005 Smith,Bob
02/07/2005 Smith,Bob
02/08/2005 Smith,Bob
02/06/2005 Jones,John
02/07/2005 Jones,John

In this case Bob Smith had the closest date. I want to include all other records for Bob Smith. And then go to the next name based on ASC date.

Again thanks for the help.

 

[lespaul]

The problem is that the date has to be the first on the list then name

that's what the query traingamer does:

select date, name from table 
where ...
order by name, date

in the SELECT clause it's DATE then NAME
in the ORDER BY clause it's NAME then DATE

what about this solution doesn't work for you?


Leslie

 

[traingamer]

No, I get it now. He wants the Min(date) for each person, then all data for that person, then the Min(date) for the next person. You can use two queries like:
typed, not tested

qMin

select Min(date) as DateMin, name
...
group by name

then select results from there

select t1.date, t1.name
from table t1 inner join qMin on t1.name = qMin.name
where ...
order by qMin.date

traingamer

 

[traingamer]

And, on re-reading this, Duane answered it in the first response

traingamer