Format query output as header and detail lines

I am using Microsoft Access 2000, and I want to write a query in VB6 using SQL statements to display the department...

name only once, and then all the employee names working under that department. For example:

department A
    employee A1
    employee A2
      ...
    employee An

  
department B
    employee B1
    employee B2
      ...
    employee Bm

This is one of the more frequent SQL questions. The answer is: don't try to do this with SQL only. Formatting is best done in the application program. I don't know VB6, but I'm sure it's completely adequate for this task.

The query is very simple. Just return both "header" and "detail" columns, and make sure you sort the result set by at least the header column:

select deptname
     , emplname
  from yourtable
order
    by deptname
     , emplname

Your result set will look like this:

department A  employee A1
department A  employee A2
   ...
department A  employee An
department B  employee B1
department B  employee B2
    ...
department B  employee Bm

Now process the result set with application code, using "current/previous" logic to determine when to print a heading (this is pseudocode):

set prevdept = ''
while more rows
  begin 
    if dept > prevdept
      print dept header
      set prevdept = dept
    endif
    print empl detail
    get next resultset row
  end

Reeder feedback

Reader Frank Kulash responds:

"Formatting is best done in the application program". That's true, but it's not always possible. Here's one solution for those rare occasions when you have to do the job in SQL.

It's easy to produce all the header lines in one query, and all the detail lines in another query. But how do you combine them in one result set? That's exactly what UNION does.

  SELECT DISTINCT deptname
  FROM    yourtable
UNION
  SELECT  emplname
  FROM    yourtable;

  SELECT DISTINCT
          deptname  AS sort1
        , 1         AS sort2
        , deptname   AS display_text
  FROM    yourtable
UNION
  SELECT  deptname  AS sort1
        , 2         AS sort2
        , emplname  AS display_text
  FROM    yourtable
ORDER BY  1, 2, 3;

Now the only problem is that we've got these ugly sorting columns in the output. We need them for sorting, but we don't want to look at them. Here's one way (at least in Oracle, I don't know about MSAccess) to hide the sort columns.

SELECT  display_text
FROM
(
  SELECT DISTINCT
          deptname  AS sort1
        , 1         AS sort2
        , deptname   AS display_text
  FROM    yourtable
UNION
  SELECT  deptname  AS sort1
        , 2         AS sort2
        , emplname  AS display_text
  FROM    yourtable
)
ORDER BY  sort1
        , sort2
        , display_text;