MS Access 2007 - 2010, How do I sort by one field, but keep like groups intact?

I might have to write code to do what I want, but I wanted to throw this out just in case.  I have some truck scheduling data that I would like to sort by the time field.  This data is for each day of the week so truck A might be scheduled to arrive at 0200 on Monday but be scheduled to arrive at 0205 the rest of the week.

This truck should show as:
Truck A    0200    Kansas City    M
               0205    Kansas City        Tu W Th F

Well, I can do this by sorting by the truck name field and then hiding duplicates on that column.  The problem is that whole section of data is not sorted by date/time.  I want the data to show what is coming in first in the morning to what is coming in last, but keep truck names together.

Is this possible?  I thought I could sort the query bound to the report and then do grouping on the report, but the group does sorting...
LVL 12
rgn2121Asked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
Whew!
Try this...

It took three queries and a Report with the Hide Duplicates property set to Yes for the repeating fields.
Query1-0 sorted the Trucks and the ActualDate
Query1-1 Grouped the trucks by the "Min" ActualDate
Query1-2 Join Query1-1 to Query1-0 and sorts by the ActualDate


JeffCoachman
Access-EEQ26607345SortGroupRepor.mdb
untitled.JPG
0
 
hnasrCommented:
list few input records, and list the expected output.
0
 
rgn2121Author Commented:
I did...  The TRUCK A was my example.  I will add to it though...
Here is what I get now:
Truck A    0200    Kansas City    M
Truck B   0203    Dallas              M Tu W Th F
Truck A   0205    Kansas City        Tu W Th F
 
Here is what I want:
Truck A    0200    Kansas City    M
                 0205    Kansas City        Tu W Th F
Truck B   0203    Dallas              M Tu W Th F
This way the first item a group is used when sorting by time and the group stays together...

 


 
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hnasrCommented:
For a better understanding, create a sample database in access.
Create one table.
Add the mentioned data, and add few more records to represent all possibilites.
Attach the database.
list expected output from the table.
0
 
Jeffrey CoachmanMIS LiasonCommented:
1. Can you post the names of these fileds please?
2. I don't see any real " date/time" fields in this data?...
3. The "MTWTF" data should really be in a related table.
4. The fields that start with "0" will sort as "Text" not numbers, just FYI
Text Sort:
1
11
1129
12
19
2
210
225
276
3

If this field is what you are calling a "Time", then you will have to correctly set the Datatype of this field in the table as "Date/Time", so it sorts correctly.


In any event...
If you create a Grouped report, then this will simulate the output you are wanting.
In the wizard, select the "Truck" field to Group on, then in the next screen, select the field you wish to sort on.

Sample attached

Let us know if this is what you wanted.
Database31.mdb
0
 
rgn2121Author Commented:
<In any event...
If you create a Grouped report, then this will simulate the output you are wanting.
In the wizard, select the "Truck" field to Group on, then in the next screen, select the field you wish to sort on.>

The Grouping essentially sorts the report, unless you can tell me how to turn this off.  When I group by the TruckName field, it either orders the groups A-Z or Z-A...I don't think I can turn this off, if so, then please tell me how.

I think what I am trying to do got lost somewhere.  I can group the report by TruckName and then sort it by my ActualTime field, which is Date/Time.  I didn't show it in my data because it doesn't show on the report...it is used for sorting.  That said, I tried this originally and it doesn't do what I want.  Grouping on the TruckName and then sorting by time will Group all trucks by name and then sort by the truck name and then by the time within that group.  That is NOT what I want.  I want the report sorted by my ActualTime field, but keeping the truckNames together.  I am pretty sure this is not doable without me creating a background table in code...but I thought I would ask.



<1. Can you post the names of these fileds please?>
TruckName, ArrivalTime....ActualTime is the Date/Time field used for sorting.  They want to see the ArrivalTime (String/Text) on the report.

<2. I don't see any real " date/time" fields in this data?...>
See above...

<3. The "MTWTF" data should really be in a related table.>
Correct...and it is.  The background of my report is from a query with about 7 joined tables...I stated this in the original question.

<4. The fields that start with "0" will sort as "Text" not numbers, just FYI>
Yes...Thank You.  That is why I sort with a date/time field to get accurate results.

I will look at the data in the attached DB and tell you what I need as output.  If you grouoped and sorted the way you stated then that won't be what I need.  I will modify the data and report to give more of an example and provide accurate sorting by times.
0
 
rgn2121Author Commented:
Okay...I have changed the data in the TRUCK field to something that more resembles our truck names.  This will further highlight the way that Access, when grouping, sorts the report by group either A-Z or Z-A.

I also added a datetime field to provide you with the correct sorting.

Now...looking at was the report outputs:

This gives me a report that is grouped by truck name and sorted by time.  That would be great IF the grouping didn't also sort the report.  The first truck is FRT321, which arrives from Dallas at 2:03AM Mon, Tues, Wed and Thurs; and arrives at 2:07AM on Fri.

The problem with that is that TZF954, among others,  arrives earlier.  It arrives at 12:15AM on the same morning.  What I want is this truck to show up first.  It is the first truck that arrives.  The next truck would be TZF732.  Now, this truck though as different times on different days.  That's fine, but since it has the early time at 1:15AM, then I want that truck "group" to stay together.  This way there aren't duplicates all over the place like there would be if you removed grouping and just sorted by the date time field.

Does that make more sence?  At least for what I am trying to accomplish? Database31.mdb
0
 
rgn2121Author Commented:
Dude...That output looks FANTASTIC...Let me give this a try!
0
 
rgn2121Author Commented:
Okay...that worked perfectly!  This is exactly why I come on this site...it's amazing the times I think something can't be done without coding it manually, only to find that it can be done without all the excess code.  
0
 
rgn2121Author Commented:
That was worth more than 500...thanks again!
0
 
Jeffrey CoachmanMIS LiasonCommented:
"That was worth more than 500...thanks again!"

Yes, even I would admit this...
;-)

As you know the main challenge was grouping by the Truck and the "Earliest" ActualDate.

I will state here that some would frown on the use of three separate queries.
(so you must remember not to delete the "precedent" queries...)
But I had to do it this way as I thought through the requirements and the structure of the output, step by step.

So I will also thank you for an interesting and thought provoking question.

;-)

JeffCoachman
0
 
rgn2121Author Commented:
You're Welcome!  At first glance, just talking baout it at work I said,"Sure, that seems pretty easy to do.", but once I started looking at actual data I realized it was much trickier...

Thanks again...
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)

Enjoy the weekend..
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.