database sorting

R

rfcomm2k

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?
 
A

Allen Browne

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.
 
R

rfcomm2k

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...
 
Top