Sorting a subform within a Report help

[Lorlai]

I'm certain that I am making some sort of silly mistake, and am at the frustration point where I can't see past it. I have a report that contains a header with a specific Scenario ID. Based on this Scenario ID, there are several cases that are displayed in a subform. I have the Main form grouped on the Scenario ID. This causes the report to display the correct number of cases based on the ID. However, the cases are all repeating the first case (see example before). Is there a way to properly group/sort the report so I am able to see every case associated with the Scenario ID?


Thank you for your help!
Table

Scenario ID	Case	Sequence	Unique ID
BS1	712	1	BS1_1_712
BS1	888	2	BS1_2_888
BS2	902	1	BS2_1_902
BS2	911	2	BS2_2_911
BS2	123	3	BS2_3_123

What is being displayed:

BS1


712
712

BS2
902
902
902

[June7]

Would have to analyze report recordset and design setup. If you want to provide db, follow instructions at bottom of my post.

[Lorlai]

Attached, please find the database with most data removed. The report I am having problems with is the "Copy of Report Works".


thank you for you help!


Business Scenario Help.zip

[June7]

The data for this report comes from two tables with 1-to-many relationship. It is not necessary to use subreport when only 1 'many' table is involved. Report can have a RecordSource that is a query joining the two tables then use report Sorting & Grouping features to organize the records. Set multi-level groups. First group on SCENARIO, second group on Case #. This is much easier and simpler than what you have already attempted.

The records will not sort properly on the SCENARIO or UNIQUE ID fields because these are text values subject to alpha sort. This means ABC-BS18 will sort before ABC-BS2. Would need placeholder 0, like ABC-BS02. Why have ABC part of this value? ABC is in the Track field.

Table [BS to Case] does not have primary key set. [Case #] field appears to be unique and it is the value saved in [CASE to SCRIPT], suggest it be set as primary key. There is no reason to have the [UNIQUE ID] field. This is just a concatenation of other fields and can be calculated when needed. You could make this a Calculated field type but there is no reason to bother with entering this value into field.

Reduce width of report to fit paper dimensions.

BTW, recommend no spaces or special characters/punctuation (underscore is exception) in names. There use can be source of errors and frustration.

[Lorlai]

Thank you June7, this worked perfectly!