Sorting Question

[carl]

ID Time Date Code
1 93116 20060403 UMUD06C30.00
2 93137 20060403 QAAE06C65.00
3 93141 20060403 QAAE06C65.00
4 93150 20060403 DLQD06C30.00


Is possible for Access to do a sort like:

Sort By Date, Then By Time, Then by Code.

Could you show me the SQL to do this ?

After this sort is done, how can I "re do" the Primary ID to reflect the new
sorted order of the records ?

Thank you in advance.

 

[Ofer Cohen]

Yes you can

Select * From TableName Order By [Date], [Time],

If no sort type is defined it will sort it Ascending, other wise you need to
specify

Select * From TableName Order By [Date], [Time] Desc , [Code] Desc

To change the order back to the Code, you need to put it first in the right
order
Select * From TableName Order By [Code], [Date], [Time]

 

[Ofer Cohen]

One more thing,
If the fields are Date, Time consider changing it, they are key names in
Access and it can cause some problems.

 

[John Spencer]

What do you mean by "redo" the primary id? Do you want to have a value that
represents the order the records are now in?

Is ID an autonumber? If so, you can't really redo it.

Why do you feel that you need to "re do" the ID? What problem are you
trying to solve?

If you just need a line number in a report, that is simple to solve using a
control in the report.

If you need a ranking or sequential number in a query, then that can be
accomplished using a ranking query.

 

[carl]

I need a new field that will assign a sequential number to the order that the
records are in - to later use in a calculation.

Can you show me how to do that ?

 

[John Spencer]

I think that you would need SQL that looked something like the following.

SELECT A.Date
, A.Time
, A.Code
, 1 + (SELECT Count(*)
FROM YourTable as B
WHERE A.Date <= B.Date
AND A.Time <= B.Time
And A.Code < B.Code) as Rank
FROM YourTable as A
ORDER By A.Date, A.Time, A.Code


If that doesn't work perhaps Tom Ellison will chime in with a solution. It
would be a LOT simpler if your dates and times were combined and in one
datetime field.