We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Base: sort before groups [closed]

asked 2016-11-01 17:58:38 +0200

ace214 gravatar image

updated 2016-11-01 19:50:49 +0200

I'm a musician and trying to convert my repertoire list from a spreadsheet to a database so that I could more easily generate reports in different ways rather than maintaining multiple copies. I'm having trouble getting the report exactly the way I want it.

Here is my version from Calc: PDF on Dropbox

I can get the 2nd part of that (p. 6-10) fine: group by category, then sort by composer, etc. It's the first part that I have trouble with: I want 2 groupings, but sorted by another field in between.

So: group by Category (sorted in a particular order), then sort by Date, then group by Program (so that all pieces from the same concert are grouped together), then sort by order in that Program.

Here's a screen shot of how I laid out my table: Imgur link. (Note that not every entry is part of a full program, but I want them sorted by date rather than the "null" group.)

What's the best way to do this report?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-15 01:09:00.314504

1 Answer

Sort by » oldest newest most voted

answered 2016-11-02 06:13:27 +0200

Ratslinger gravatar image

updated 2016-11-07 04:34:59 +0200

Looking at your sample and table and reading your question about what seems to be a thousand times, I may understand what you want. In order to get the needed sequence, I concatenated Date, Program and Ensemble using the following SQL as a Query:


The field names are the same just all upper case (has it's benefits).

For the report I used two levels of grouping - First "CATEGORY" and second "MUSICGROUPING" ( the concatenated field). Sort on CATEGORY then MUSICGROUPING then ORDER. That produces this report:

image description

It's a bit crude but gives the results (used some of you data but just in relevant parts). Also changed one record date which produced this report:

image description

This shows grouping with same dates is in correct sequence. All the dressing up and formatting is up to how you want it to appear.

Hope I interpreted your question correctly.

Edit 11/06/16 Add Sample - MusicListReports4.odb

There are three reports in sample: 1 = first one shown in this answer; 2 = second one shown; 3 = same report as first without the ORDER field displayed.

edit flag offensive delete link more


Thanks, that does answer my question. So I need to create a field that contains that relevant information with the date at the front so that it uses the date to sort.

Is there a way to sort the entries in group by "Order" (sort of like track numbers on a CD), but not actually display that field on the report?

ace214 gravatar imageace214 ( 2016-11-07 01:57:29 +0200 )edit

To do that, everything is the same except that the ORDER field is not placed on the report. It is, however, still sorted and still in the Query as input to the report. This sequence is already shown in the samples in the answer unless I am not interpreting your question correctly. FYI the SQL in the answer creates the Field 'on-the-fly'. There is nothing to add to your DB.

Ratslinger gravatar imageRatslinger ( 2016-11-07 02:25:10 +0200 )edit

I have added a sample in the answer to hopefully make things a bit more clear.

Ratslinger gravatar imageRatslinger ( 2016-11-07 02:46:16 +0200 )edit

It seems to do a descending sort then, which is odd. I can work around that though.

ace214 gravatar imageace214 ( 2016-11-07 02:50:46 +0200 )edit

I don't understand - where is descending?

Ratslinger gravatar imageRatslinger ( 2016-11-07 02:57:33 +0200 )edit

Here's my report: Dropbox link

If you compare that to the Imgur screenshot of my table, you'll see that the Order of the pieces within each grouping is descending (13, 12, 11, etc.). It seems to sort it this way regardless of how it's sorted in the query (tried both ways).

ace214 gravatar imageace214 ( 2016-11-07 03:06:28 +0200 )edit

I only went by your statement in the question - "... then sort by order in that Program." It is very easy to change that sort sequence. Edit the report. From the menu select View->Sorting and Grouping. In the dialog under Groups select the field ORDER. Then under Properties change Sorting from Ascending to Descending.

Ratslinger gravatar imageRatslinger ( 2016-11-07 03:23:48 +0200 )edit

Yes, I want each group sorted by the "Order" field, but I don't want that column actually visible on the report.

ace214 gravatar imageace214 ( 2016-11-07 03:26:46 +0200 )edit

Changed the sample to display what I was talking about in comment. Report "MUSICLISTdescending" is same report as "MUSICLIST3" with "ORDER" reversed. "MUSICLISTdescNOPRINT" is same as "MUSICLISTdescending" with just "ORDER" field removed from the detail area. I also changed grouping of those with blank PROGRAM names.

Ratslinger gravatar imageRatslinger ( 2016-11-07 04:11:51 +0200 )edit

I get an error when I try to open your reports- "document cannot be opened"

ace214 gravatar imageace214 ( 2016-11-07 04:25:30 +0200 )edit

Question Tools

1 follower


Asked: 2016-11-01 17:58:38 +0200

Seen: 432 times

Last updated: Nov 07 '16