Solved

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

Posted on 2010-11-11
13
2,884 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IT Desktop Support 11 65
SSRS 2013 - Creating a summarized report 19 32
Calculation in Access 5 25
Help with DoEvents 8 25
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

806 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