Keeping a non-alphabetical sort order

[tom r.]

I have a table with records in a desired sort order (I appended them
to the table in the order I wanted). It would look something like
this:

Table 1
Name Date Code DisplayOrder
------ ------------ ----------- ------------------
A1 1-21-04 XB56 10
A1 1-21-04 XB56 20
A1 1-21-04 XB56 30
A2 1-21-04 CT57 40
C2 1-21-04 DF58 50
C1 1-21-04 NN59 60
B1 1-21-04 GJ60 70
B2 1-21-04 FT61 80
B2 1-21-04 FT61 90

I now run a SELECT DISTINCT query on this table - not including the
DisplayOrder field and I get:

Table 2
Name Date Code
------- ------------ -------------
A1 1-21-04 XB56
A2 1-21-04 CT57
B1 1-21-04 GJ60
B2 1-21-04 FT61
C1 1-21-04 NN59
C2 1-21-04 DF58

Here is my problem: I have to drop the DisplayOrder Column to get the
SELECT DISTINCT to run, but then it reorders the recordset. In this
case, it is now ordered by Name (alphabetically). Table 2 has the
data I want, just not in the order I want. Can anyone help?

Thanks in advance.

tom r

[Michael Cleroux]

Well, since you are using SELECT DISTINCT, there's no reason why the A1 and
B2 records need to have seperate entries for DisplayOrder use this instead:

Table 1
Name Date Code DisplayOrder
------ ------------ ----------- ------------------
A1 1-21-04 XB56 10
A1 1-21-04 XB56 10
A1 1-21-04 XB56 10
A2 1-21-04 CT57 40
C2 1-21-04 DF58 50
C1 1-21-04 NN59 60
B1 1-21-04 GJ60 70
B2 1-21-04 FT61 80
B2 1-21-04 FT61 80
Which will come out like this with SELECT DISTINCT ...
Name Date Code DisplayOrder
------ ------------ ----------- ------------------
A1 1-21-04 XB56 10
A2 1-21-04 CT57 40
C2 1-21-04 DF58 50
C1 1-21-04 NN59 60
B1 1-21-04 GJ60 70
B2 1-21-04 FT61 80
"tom r." <ri******@neo.rr.com> wrote in message
news:28**************************@posting.google.c om... I have a table with records in a desired sort order (I appended them
to the table in the order I wanted). It would look something like
this:

Table 1
Name Date Code DisplayOrder
------ ------------ ----------- ------------------
A1 1-21-04 XB56 10
A1 1-21-04 XB56 20
A1 1-21-04 XB56 30
A2 1-21-04 CT57 40
C2 1-21-04 DF58 50
C1 1-21-04 NN59 60
B1 1-21-04 GJ60 70
B2 1-21-04 FT61 80
B2 1-21-04 FT61 90

I now run a SELECT DISTINCT query on this table - not including the
DisplayOrder field and I get:

Table 2
Name Date Code
------- ------------ -------------
A1 1-21-04 XB56
A2 1-21-04 CT57
B1 1-21-04 GJ60
B2 1-21-04 FT61
C1 1-21-04 NN59
C2 1-21-04 DF58

Here is my problem: I have to drop the DisplayOrder Column to get the
SELECT DISTINCT to run, but then it reorders the recordset. In this
case, it is now ordered by Name (alphabetically). Table 2 has the
data I want, just not in the order I want. Can anyone help?

Thanks in advance.

tom r

[Larry Linson]

By definition, records in a relational database table are unordered. Thus,
you need to specify _some_ field if you want them ordered in a particular
way.

You see them ordered by the name field, simply because it is convenient for
the database engine; they might, however, after some future release or
patch, be ordered differently or simply randomly.

Michael suggests that you can use the same DisplayOrder field based on the
Name field (neither Name nor Date, by the way, is an appropriate name for a
Field because both are Access reserved words and sooner or later, that will
be confusing). That would work for this particular query, but it may not be
appropriate for other situations in your application.

Have you considered not using a DISTINCT, but instead using a Totals Query,
selecting Group By for each field except Display Order which will be
"First", and selected for Sorting. I think that will do what you want.

Larry Linson
Microsoft Access MVP

"tom r." <ri******@neo.rr.com> wrote in message
news:28**************************@posting.google.c om...

I have a table with records in a desired sort order (I appended them
to the table in the order I wanted). It would look something like
this:

Table 1
Name Date Code DisplayOrder
------ ------------ ----------- ------------------
A1 1-21-04 XB56 10
A1 1-21-04 XB56 20
A1 1-21-04 XB56 30
A2 1-21-04 CT57 40
C2 1-21-04 DF58 50
C1 1-21-04 NN59 60
B1 1-21-04 GJ60 70
B2 1-21-04 FT61 80
B2 1-21-04 FT61 90

I now run a SELECT DISTINCT query on this table - not including the
DisplayOrder field and I get:

Table 2
Name Date Code
------- ------------ -------------
A1 1-21-04 XB56
A2 1-21-04 CT57
B1 1-21-04 GJ60
B2 1-21-04 FT61
C1 1-21-04 NN59
C2 1-21-04 DF58

Here is my problem: I have to drop the DisplayOrder Column to get the
SELECT DISTINCT to run, but then it reorders the recordset. In this
case, it is now ordered by Name (alphabetically). Table 2 has the
data I want, just not in the order I want. Can anyone help?

Thanks in advance.

tom r