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...
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...
Hamed Nasr
list few input records, and list the expected output.
ASKER
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...
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...
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.
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.
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
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
ASKER
<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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dude...That output looks FANTASTIC...Let me give this a try!
ASKER
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.
ASKER
That was worth more than 500...thanks again!
"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
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
ASKER
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...
Thanks again...
;-)
Enjoy the weekend..
;-)
Enjoy the weekend..
;-)