query sort order on a text field

[MLHab306]

Hi

When I run this query, it does not output in chronological order. Some times it will put a client with a time of 7:00 to 9:00 after someone scheduled 11:00 to 3:00. The time ranges are in a seperate table. ( 4 tables are used in this query. This is the table that I think is the problem. I am trying to sort using the sort col.

prim
Key time range Sort
68 6:30 0
1 7:00 to 7:30 1
2 7:00 to 8:00 2
35 7:30 to 9:30 3
64 7:30 to 10:30 4
65 7:30 to 11:30 5
3 8:00 to 10:00 10
4 8:00 to 11:00 11
5 8:00 to 12:00 12
34 8:30 to 10:30 13
45 8:30 to 11:30 14
54 8:30 to 12:30 15
6 9:00 to 11:00 20
7 9:00 to 12:00 21
8 9:00 to 1:00 22
37 9:30 to 11:30 23
46 9:30 to 12:30 24
55 9:30 to 1:30 25
9 10:00 to 12:00 30
10 10:00 to 1:00 31
11 10:00 to 2:00 32
38 10:30 to 12:30 33
47 10:30 to 1:30 34
56 10:30 to 2:30 35
12 11:00 to 1:00 40
13 11:00 to 2:00 41
14 11:00 to 3:00 42
39 11:30 to 1:30 43
48 11:30 to 2:30 44
57 11:30 to 3:30 45
15 12:00 to 2:00 50
16 12:00 to 3:00 51
17 12:00 to 4:00 52
40 12:30 to 1:30 53
67 12:30 to 2:30 54
49 12:30 to 3:30 55
58 12:30 to 4:30 56
18 1:00 to 3:00 60
19 1:00 to 4:00 61
20 1:00 to 5:00 62
41 1:30 to 3:30 63
50 1:30 to 4:30 64
59 1:30 to 5:30 65
21 2:00 to 4:00 70
22 2:00 to 5:00 71
23 2:00 to 6:00 72
42 2:30 to 4:30 73
51 2:30 to 5:30 74
60 2:30 to 6:30 75
24 3:00 to 5:00 80
25 3:00 to 6:00 81
26 3:00 to 7:00 82
43 3:30 to 5:30 83
52 3:30 to 6:30 84
61 3:30 to 7:30 85
27 4:00 to 6:00 90
28 4:00 to 7:00 91
29 4:00 to 8:00 92
44 4:30 to 5:30 93
53 4:30 to 7:30 94
62 4:30 to 8:30 95
30 5:00 to 7:00 100
31 5:00 to 8:00 101
32 5:00 to 9:00 102
33 TFY 103
*****************
Query used to generate report.

SELECT Date_last_done.Date_last_done, Customers.GateCode, Appt_Time_look_up.Sort, Date_last_done.Appt_time, Date_last_done.Payment_type, Date_last_done.Holiday, [cust2 table dogs].dog_1_weight, [cust2 table dogs].Dog_2_weight, [cust2 table dogs].Dog_3_weight, [cust2 table dogs].Dog_4_weight, [cust2 table dogs].Dog_5_weight, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.Address, Customers.City, Customers.HomePhone, Customers.FaxNumber, Customers.East_west, Customers.[Parking Restrictions], Customers.North_South, Customers.MapCordinates, Date_last_done.Dog_1_charges, Date_last_done.Dog_2_charges, Date_last_done.Dog_3_charges, Date_last_done.Dog_4_charges, Date_last_done.Dog_5_charges, Date_last_done.Misc_Charges, Date_last_done.groomer, Date_last_done.NumberOfDogsDone, [cust2 table dogs].Caution, [cust2 table dogs].Dog_Breed_1, [cust2 table dogs].Dog_1_Name, [cust2 table dogs].Dog_2_Breed, [cust2 table dogs].Dog_2_Name, [cust2 table dogs].Dog_3_Breed, [cust2 table dogs].Dog_3_Name, [cust2 table dogs].Dog_4_Breed, [cust2 table dogs].Dog_4_Name, [cust2 table dogs].Dog_5_Breed, [cust2 table dogs].Dog_5_Name, [cust2 table dogs].[Grooming instructionsDog1], [cust2 table dogs].GroomingInstructionsDog2, [cust2 table dogs].GroomingInstructionsDog3, [cust2 table dogs].GroomingInstructionsDog4, [cust2 table dogs].GroomingInstructionsDog5, [cust2 table dogs].Dog_1_Medical_info, [cust2 table dogs].Dog_2_Medical_info, [cust2 table dogs].Dog_3_Medical_info, [cust2 table dogs].Dog_4_Medical_info, [cust2 table dogs].Dog_5_Medical_info, Appt_Time_look_up.time, Appt_Time_look_up.time, Customers.Driving_Directions
FROM ((Customers INNER JOIN Date_last_done ON Customers.CustomerID = Date_last_done.Customer_ID) INNER JOIN [cust2 table dogs] ON Customers.CustomerID = [cust2 table dogs].CustomerID) LEFT JOIN Appt_Time_look_up ON Date_last_done.ID = Appt_Time_look_up.ID
WHERE (((Date_last_done.Date_last_done)=[what date]))
ORDER BY Date_last_done.Appt_time;
**************
THis is the report that I get. ( I removed personal info. It looks better with out being edited and crunched.

Friday, December 06, 2002 Groomer: Mark
7:00 to 7:30
Richard # of dogs here 2 Gate Code
Caution
Cross streets Ventura Woodley Parking Restrictions
Shelti jellinger b+b
Shelti Diva
10:00 to 12:00
Gel # of dogs here 1 Gate Code 2887
Caution
Cross streets Moorpark Whitsett Parking Restrictions 8-10 thurs
weinmawymer Mack b+b
12:00 to 2:00
Gro # of dogs here 2 Gate Code
Caution
Cross streets Ventura Balboa Parking Restrictions
Shep dizzy b+b
Shep carman b+b
Shep Ella b+b
Shep Coltraine
b+b
1:00 to 5:00
Roths # of dogs here 1 Gate Code
Caution
Cross streets Ventura Hayvenhurst Parking Restrictions
Airdale Terry #7 airdale
2:00 to 4:00
Eisner # of dogs here 1 Gate Code
Caution
Cross streets Ventura Hayvenhurst Parking Restrictions
Golden Retriever Sparky b+b trim
8:30 to 10:30
Saff # of dogs here 1 Gate Code
Caution
Cross streets Moorpark Whitsett Parking Restrictions
Bouver blake #7k sch head

10:30 to 12:30
Schneier
# of dogs here 1 Gate Code
Caution
Cross streets Burbank Van Nuys Parking Restrictions 10-12 mon
pit bull Rio b+b
Total number of dogs for the day 9.00 printed or revised Sunday, January 12, 2003 09:15 AM


Thanks for any and all suggestions.

Mark

 

[chrissie1]

ORDER BY Date_last_done.Appt_time,Appt_Time_look_up.Sort;

try this

"What a wonderfull world" - Louis armstrong

 

[MLHab306]

Hi

Thank you for responding so quickly. I have created almost everything in this database by using the wizards. I am clueless where this statement should be placed. Where would I place it?

Thank you

Mark

 

[chrissie1]

its the last line of your query

just replace the

ORDER BY Date_last_done.Appt_time;

with the one above

"What a wonderfull world" - Louis armstrong