Solved

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

Posted on 2010-11-11
13
2,945 Views
Last Modified: 2012-05-10
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...
0
Comment
Question by:rgn2121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 2
13 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 34110229
list few input records, and list the expected output.
0
 
LVL 12

Author Comment

by:rgn2121
ID: 34110438
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
 
LVL 30

Expert Comment

by:hnasr
ID: 34110538
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34114357
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
 
LVL 12

Author Comment

by:rgn2121
ID: 34117835
<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
 
LVL 12

Author Comment

by:rgn2121
ID: 34117891
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 34118347
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
 
LVL 12

Author Comment

by:rgn2121
ID: 34119246
Dude...That output looks FANTASTIC...Let me give this a try!
0
 
LVL 12

Author Comment

by:rgn2121
ID: 34119342
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
 
LVL 12

Author Closing Comment

by:rgn2121
ID: 34119345
That was worth more than 500...thanks again!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34121530
"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
 
LVL 12

Author Comment

by:rgn2121
ID: 34124615
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34124815
;-)

Enjoy the weekend..
;-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question