Solved Help needed with grouping a report (1 Viewer)

[sanj0411]

Hi,

I have created a report with the following headers:

Name; money in; money out

The report then totals the value of money in and money out at the bottom.

The format of the data in the name field is:

Fund1 class1; Fund1 class2; Fund2 class1; Fund2 class2

I would like the report to group data using the name field, however I would like the grouping to be based on just the Fund1 and Fund2 element of the name field so that the report and totals are split by Fund, rather than the individual Fund class (which is what currently happens if I try to group by name). I assume I will need to use an expression in the grouping options to achieve this, but I am not sure where to start!

Any help will be greatly appreciated!

 

[Ranman256]

you can either group by:
Name
Fund1
Fund2

or
Fund1
Name

 

[sanj0411]

Thanks - I've just spotted the 'group by characters' which has allowed me to get the results I needed

 

[Pat Hartman]

The better solution would be to normalize your schema. According to First Normal Form, each column of a table MUST be atomic. That means it cannot contain multiple attributes as yours does. You need TWO columns, one for Fund and one for Class. You can create an update query to split the names so you don't have to retype the data. If you were sloppy with typing the names and didn't include the comma, that field won't split correctly so you'll have to to it manually. If you want to display the name on your reports with the comma, just concatenate the two fields with a comma between them.

 

[sanj0411]

The better solution would be to normalize your schema. According to First Normal Form, each column of a table MUST be atomic. That means it cannot contain multiple attributes as yours does. You need TWO columns, one for Fund and one for Class. You can create an update query to split the names so you don't have to retype the data. If you were sloppy with typing the names and didn't include the comma, that field won't split correctly so you'll have to to it manually. If you want to display the name on your reports with the comma, just concatenate the two fields with a comma between them.

Thanks Pat, your advice is helpful.