How do I do this...?

[geraldh]

Dear all,

I need to make a report that displays the repertoire performed in a certain concert series season (or some or all the seasons) according to certain parameters such as:

1) Female composers

2) Canadian composers

3) Living Composer

3) World Premiere

4) Canadian Premiere

I have checkboxes for the the above--so the data is entered. But I want it to display the number of pieces that are in the found set.

For example: Please display the number of repertoire performed in Season 2 that is composed by a female, the name of the work, and the concert in which it was played.

Also is there any way to do this by "find" rather than making a layout for each parameter?

I hope I am making sense. I've tried doing this with sub-summary reports but I'm having trouble making this work....

Any guidance would be appreciated.

[efen]

Is all this information in one table or do you have tables for Composers, Compositions and Concert seasons, or some other combination of tables?

If you have multiple tables then what you require can be done by filtering portal relationships on a layout, without a "find".

[LaRetta]

Hi yyztoronto416,

As suggested, these parameters should be a related table. As is, by using a checkbox, you cannot generate a sub-summary report. For example, let's assume Wilma is a female from Canada and she is living. Her RECORD cannot appear in a summary in three different places - only one, because she only has one record. Instead, if Wilma has a ComposerID of 6 (auto-enter serial number in your Composers table) and you have another table called Attributes with a number field called ComposerID and you put a 6 in it and type Female into the Attribute field. And then create another record and put a 6 in it and type Canadian etc ...

... then generate your report from the Attributes table, you will have multiple records for Wilma and she can appear (and be counted in) several sub-summary parts as appropriate.

This structure is easily accomplished and if you wish, we can help you set this up. It is also the best way to go if you have multiple 'like' items which might need to be totaled or summarized. Checkboxes are an import important tool we can utilize but their use should be carefully considered. :*)

ps sorry for the typo. checkboxes are not an import tool. :-/

[geraldh]

Thank you for your responses...

I was playing around with this more today and I realized what you are saying...

Right now I have these tables (among others):

1) Repertoire (with fields: Primary Composer; Checkboxes: Canadian, Female, Living; Checkboxes: Canadian Premiere, World Premiere; Date of Composition, and others such as Title, RepID, etc...)

2) Concert

3) RepertoireConcert (in other words, a record of a certain work in each concert program )

I do not have a separate table for Composer. As is now, I am inputing the composer, and his/her attributes for each Repertoire record (perhaps some redundancy here, but I did it to avoid setting up more tables; and also to avoid finding/calling and jotting down the unique ComposerID everytime I needed it)

I now realize that I will need to set up a separate table for Composer and for ComposerAttributes, and then relate the two tables

A little more work, but all a good investment.

Then will I have to have a new table relating the following 4 tables?

Repertoire -- Composer -- ComposerAttributes -- Concert

Please tell me if I have misinterpreted your suggestions.

[LaRetta]

I now realize that I will need to set up a separate table for Composer and for Composer Attributes, and then relate the two tables (along with Concert and Repertoire)

A little more work, but all a good investment.

Exactly ... a bit more work at the onset but much less work later when you need to gather the information in various ways.

In truth, now that I understand your Repertoires table, you may not need an Attributes table and you can instead put the attributes as checklist in Composers. But if you ever plan on generating a report summarizing by those checklist items then go ahead and include it now.

I'm trying find a way to work without portals on this; and rather work with a report layout.

Well, going back to your original request of:

Please display the number of repertoire performed in Season 2 that is composed by a female, the name of the work, and the concert in which it was played. Also is there any way to do this by "find" rather than making a layout for each parameter?

You can search through related tables so there are two things to consider:

1. What table will produce the end result of the report, i.e., what table will hold the individual records and how should the report be designed? Will it be summarizing? It appears that you want to count RepertoireConcerts records so that would be the table you design in. I see a date in Repertoires but that isn't the 'season 2' date, correct? I would assume that date resides in Concerts? Or in RepertoireConcerts?
   
2. You can search in Composer Attributes because it is related. You can search for ‘season 2’ in whichever table holds it – again, because they are all related. And you can display the Repertoire because it is related and the Concert information (because it is the parent side and related) - just place those related fields on your report.
   

Please check the attached relational structure. I got lazy and didn’t add very many records but at this point, you would create a script which searches the related tables for the records you wish … female composer (search Attributes::Attribute, Season 2 (search the table where that resides). Since you are on RepertoireConcerts, a summary field which is ‘count of RepertoireConcertsID will provide the result from the found set.

Anyway, this file would be a good basic structure but only you know your business requirements and whether this will fit in this situation. Let us know if this need further tweaking.

Composers.zip

Edited March 8, 2012 by LaRetta