Base: sort before groups

English
, , ,
#1

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?

Base - Report - Crashes
#2

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:

SELECT “DATE” || ’ ’ || “PROGRAM” || ’ ’ || “ENSEMBLE” “MUSICGROUPING”, “COMPOSER”, “TITLE”, “MASTERWORK”, “ENSEMBLE”, “PROGRAM”, “DATE”, “CATEGORY”, “ORDER” FROM “MUSICLIST” “MUSICLIST” ORDER BY “CATEGORY”, “DATE”, “PROGRAM”, “ORDER”

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.

#3

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?

#4

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.

#5

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

#6

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

#7

I don’t understand - where is descending?

#8

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).

#9

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.

#10

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

#11

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.

#12

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

#13

In direct response to your last comment, it’s just a matter of deleting the ORDER field and title from the report in edit mode. All else remains the same. It still sorts descending by the ORDER field, it just doesn’t print.

#14

Re-posted file as “MusicListReports4”.

#15

After reviewing all comments, it is still not clear what sequence ORDER is to be in - Ascending or Descending. Easy for you to change under menu item View->Sorting and Grouping as previously mentioned in a comment. Also ORDER removed from report can be done without affecting the sort sequence (mentioned in comment and shown in sample). Does this now answer your questions?

#16

Thanks- I didn’t realize the sort would remain if I deleted the column from the report. I wanted it to be ascending.

I’ve finally gotten it looking like I wanted after modifying your grouping a bit link. Thanks so much for your help with the concepts.

#17

Your welcome. Good to hear you got the results wanted.

#18

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.