Solved

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

Posted on 2010-11-11
13
2,811 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
  • 7
  • 4
  • 2
13 Comments
 
LVL 30

Expert Comment

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

Author Comment

by:rgn2121
Comment Utility
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
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
Comment Utility
Dude...That output looks FANTASTIC...Let me give this a try!
0
 
LVL 12

Author Comment

by:rgn2121
Comment Utility
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
Comment Utility
That was worth more than 500...thanks again!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
"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
Comment Utility
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
Comment Utility
;-)

Enjoy the weekend..
;-)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now