Order by date, while staying grouped by name

[Darkflame]

I applogies if this has been asked before, i saw many threads about grouping, but none i could figure out how to apply to what i need.
Also im sorry if this post seems rushed, i wrote it up nicely only for the connection to go a few seconds after hitting submit

Basicaly, im makeing a database of film footage on tapes.
Each tape has a Unique ID,Production (text), Date Field, and a Notes field.


eg.

ID   Production     Date(DD/MM/YYYY)   Notes 
  
1    Name A         06/10/1998       Notes
2    Name B         15/02/2001       More Notes
3    Name A         08/10/1998       Even More Notes
4    Name D         18/02/1997       Yadadyada
5    Name D         20/02/1997       blahblahblah
6    Name B         18/07/2001       ectectect
7    Name A         07/10/1998       lalala
8    Name C         19/02/1997       Finalnotes

I would liked them in order of dates from earliest to newest, but grouped by Production.
As each production has multiple tapes, each with their own date, i would like to take the minimum date and use that to base the order on.

eg.

4    Name D         18/02/1997       Yadadyada
5    Name D         20/02/1997       blahblahblah
8    Name C         19/02/1997       Finalnotes
1    Name A         06/10/1998       Notes
7    Name A         07/10/1998       lalala
3    Name A         08/10/1998       Even More Notes
2    Name B         15/02/2001       More Notes
6    Name B         18/07/2001       ectectect

Note that D comes before C, becauses D's lowest date is lower then C's lowest date.
The order dosnt go D,C,D either because the Productions must be kept grouped together.

I hope thats clear enough :-/

I assume i need multiple querys for this, but i just cant work out how.
Any help or advice would be most welcome

(im dealing with 1000+ odd tapes here, so doing it manualy isnt a solution )

 

[FoFa]

Just order by date than name (note, if you are using the graphical interface, since name comes before date, you would have to add name a second time to the end of the column list, mark it for not showing then select the sort).

 

[Darkflame]

I dont think that would work :-/
Wouldnt that mearly order any identical dates to be in order by name :?
It dosnt seem to group simerlar names together.

Thanks anyway.

 

[cstahlberg]

two queries

First you want to create a query to pull the minimum date by each production:

sql1 = "Select production, min(date) as mindate into mytable2 from [mytable]"
sql2 = "SELECT mytable.Production, mytable.Date, mytable.Name
FROM mytable2 INNER JOIN mytable ON mytable2.Production = mytable.Production ORDER BY mytable2.MinOfDate, mytable.date;"

that should work fine.