Grouping and sorting in report does not what do what I want!! (1 Viewer)

markzaal

Registered User.
Local time
Today, 12:47
Joined
Jan 15, 2013
Messages
50
access 2010

Hi,

I have a table with the following fields:
- From
- To
- TypeOfWork (to be chosen from a combo-box)
- Activity (text field to be filled in freely)

In a table i have a complete day with times (from -> to), the type of work between those times and the activity performed between those times a bit like this:

FROM TO TYPE OF WORK ACTIVITY
00:00 - 11:00 Welding Welding clamps
11:00 - 13:00 Welding Welding anodes
13:00 - 15:00 Cleaning Cleaning pipes
15:00 - 18:00 Cleaning Cleaning floor

I would like it to show in the report like this:

FROM.....TO.......TYPE OF WORK.....ACTIVITY
00:00 - 13:00......Welding............00:00 Welding clamps
...............................................11:00 Welding anodes
13:00 - 18:00......Cleaning...........13:00 Cleaning pipes
...............................................15:00 Cleaning floor

(Sorry for the ... they keep the layout correct)

So it should sort on "From", then group by "TypeofWork" and repeat the "From" field(I think...).
But I now have tried every combination of sorting and grouping I could think of and nothing works! Any thoughts?

Thanks a bunch!

Mark
 

billmeye

Access Aficionado
Local time
Today, 07:47
Joined
Feb 20, 2010
Messages
542
Are you saying that with any given From you will have multiple TypeOfWork? That would be the only way the Group on would have any affect, otherwise it will just be listing ascending based on From. You could Group on TypeOfWork sorting by From but that does not seem to be what you are looking for.
 
Last edited:

markzaal

Registered User.
Local time
Today, 12:47
Joined
Jan 15, 2013
Messages
50
A From can only have 1 TypeofWork and 1 Activity. The next From (which will be the same as the last "To" in order to fill 24hrs in a day) can have the same TypeofWork but will have a different activity. It can also have a different TypeofWork.
If do a grouping on TypeofWork the records on the report get sorted alfabetically instead of on time.
The idea is thus that it only groups the "TypeofWork" whenever there are 2 or more of the same following each other
 

billmeye

Access Aficionado
Local time
Today, 07:47
Joined
Feb 20, 2010
Messages
542
The idea is thus that it only groups the "TypeofWork" whenever there are 2 or more of the same following each other

Sorting by From will accomplish this without the need for Grouping.

Is it that you want to hide the From, To and TypeOfWork when the next event has the same TypeOfWork has the previous?

I don't think that can easily be accomplished without some way of keeping track of each record. Using your Primary Key could work if you all entries are done chronologically but it still is a bit messy.
 

RainLover

VIP From a land downunder
Local time
Today, 22:47
Joined
Jan 5, 2009
Messages
5,041
All Sorting and Grouping for a Report is done in the design view of the Report.

Your question says Report but you are talking Forms. Sorting is different in these.
 

markzaal

Registered User.
Local time
Today, 12:47
Joined
Jan 15, 2013
Messages
50
I am talking about the design view of the report... It says "From" not Form...
The report I am trying to make shows the activities of a diver between "From" and "To" as a period of time...
 

RainLover

VIP From a land downunder
Local time
Today, 22:47
Joined
Jan 5, 2009
Messages
5,041
Other than that are you using the Grouping and Sorting tool within the Report Design.
 

markzaal

Registered User.
Local time
Today, 12:47
Joined
Jan 15, 2013
Messages
50
Yes.
When I group by TypeofWork, it does just that. But then the "from" and "to" times are no longer chronological. When I sort on "From", it only sorts within the groups. Still it doesn't make it chronological over an entire day...
 

RainLover

VIP From a land downunder
Local time
Today, 22:47
Joined
Jan 5, 2009
Messages
5,041
When I group by TypeofWork, it does just that.

I believe Grouping would come before sorting so this is correct

But then the "from" and "to" times are no longer chronological. When I sort on "From", it only sorts within the groups. Still it doesn't make it chronological over an entire day...

You say it (From) sorts correctly within the group. Again this is to be expected so this is correct.

So this leaves us with "chronological over an entire day". You need to expand on what you intend here.

Perhaps if you post a screen print it may help describe you problem.
 

markzaal

Registered User.
Local time
Today, 12:47
Joined
Jan 15, 2013
Messages
50
I want the last To to be the next From time. So the first 2 hours of the day there may be 5 different cleaning activities, each with the consecutive time period and following each other. Then there may be one welding activity, then 2 cutting activities (which should also group, but show the time periods of each separate activity) and then another 2 cleaning activities. I want all of these to stay in the order they happened in and get 2 "cleaning" groups, one at the beginning of the day and one at the end...
 

RainLover

VIP From a land downunder
Local time
Today, 22:47
Joined
Jan 5, 2009
Messages
5,041
Just eating at the moment. When I finish I will do some tests on a report and get back
 

Brianwarnock

Retired
Local time
Today, 11:47
Joined
Jun 2, 2003
Messages
12,701
As Rain said the report is working as expected, but it appears that you do not want times within typeofwork group but the other way round, type of work within time periods

Brian
 

RainLover

VIP From a land downunder
Local time
Today, 22:47
Joined
Jan 5, 2009
Messages
5,041
Thanks Brian for the help. I am still a little confused by the request.

markzaal

Do you realise that you can Group by TYPE OF Work then Group by ACTIVITY.

Further you can sort within both of these. You do not have to show everything on the report provided it is included in your Query.

Two things you wrote have confused me.

I want the last To to be the next From time

and also this one.

I want all of these to stay in the order they happened in and get 2 "cleaning" groups, one at the beginning of the day and one at the end...

What happened to the To and From. Is this a different type of sort.

BTW To and From are reserved words. They can cause problems.

Try using TimeStart and TimeFrom with no spaces.
 

Brianwarnock

Retired
Local time
Today, 11:47
Joined
Jun 2, 2003
Messages
12,701
I was lazy and did not read all of the thread, now I have I see that I was pretty correct as he explains exactly what he wants in his first post :eek:

I don't know off hand how to achieve this.

It would seem that he would need to read through the records looking for change of worktype and consolidating the time period and then within that time period look for the detail of work type.

Brian
 

Users who are viewing this thread

Top Bottom