database sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with 21 fields. I wish to sort primarily on fields 1 and 2,
but want a sub sort done on fields 8 and 9 if fields 1 and 2 are blank.

It is a database of cable records for a large campus. Field 1 is building, 2
is cable pair. Field 8 is extension, 9 is port number (in PBX).

If there is no data in field 1 or 2 then the record will be either a spare
(unassigned) extension or a spare (unassigned) port.

I usually just open the database table and view/modify the data in that
form. I do not have any forms designed at this time.

Can this be done and if so how?
 
Create a query using this table, and drag the fields into the grid.

Then in a fresh column to the right of those, type this into the Field row:
([Field 1] Is Null)
In the Sorting row under this field, choose:
Descending.
Uncheck the box in the Show row under this field.

In the next column, in the Field row, enter:
([Field 2] Is Null)
Descending sort again, and not shown.

Now drag [Field 1], [Field 2], [Field 8], [Field 9] into the grid again, to
the right of these (i.e. 4 more columns.) In the Sorting row under these
fields, choose:
Ascending.
Uncheck the Show box under all 4 fields.

The query will sort first on the rows where [Field 1] and [Field 2] both
have values (are not null), and within that group by [Field1 ] and [Field
2]. Below that it will list the records where [Field 1] and [Field 2] are
both null, and these records will be sorted by [Field 8] and [Field 9].

To see how it works, you can switch the query to SQL View (View menu), and
look at the ORDER BY clause.
 
EXCELLENT!!!! Absolutely perfect!!!

Thank you very much.

Allen Browne said:
Create a query using this table, and drag the fields into the grid.

Then in a fresh column to the right of those, type this into the Field row:
([Field 1] Is Null)
In the Sorting row under this field, choose:
Descending.
Uncheck the box in the Show row under this field.

In the next column, in the Field row, enter:
([Field 2] Is Null)
Descending sort again, and not shown.

Now drag [Field 1], [Field 2], [Field 8], [Field 9] into the grid again, to
the right of these (i.e. 4 more columns.) In the Sorting row under these
fields, choose:
Ascending.
Uncheck the Show box under all 4 fields.

The query will sort first on the rows where [Field 1] and [Field 2] both
have values (are not null), and within that group by [Field1 ] and [Field
2]. Below that it will list the records where [Field 1] and [Field 2] are
both null, and these records will be sorted by [Field 8] and [Field 9].

To see how it works, you can switch the query to SQL View (View menu), and
look at the ORDER BY clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rfcomm2k said:
I have a database with 21 fields. I wish to sort primarily on fields 1 and
2,
but want a sub sort done on fields 8 and 9 if fields 1 and 2 are blank.

It is a database of cable records for a large campus. Field 1 is building,
2
is cable pair. Field 8 is extension, 9 is port number (in PBX).

If there is no data in field 1 or 2 then the record will be either a spare
(unassigned) extension or a spare (unassigned) port.

I usually just open the database table and view/modify the data in that
form. I do not have any forms designed at this time.

Can this be done and if so how?
Click to expand...
Click to expand...
 
Back
Top