Question: I need to conditionally format a field based on it's order within a report (further details bellow)
Introduction: I have the following database:
In this database, there are several lines of text, that should be coded. Each line can have several codes and each code can be associated to several lines, hence the LInes_Codes table. For example, in a text that has 500 lines (and each line is a record in the "Lines" table), each line can have one or multiple codes associated to it.
I also have the following layout which is a report:
As you see, this report is populated from the Lines_Codes table and works when sorted by "code" field (from "Codes" table). The "Lines_number" and "Text in Line" fields are from the "Lines" table.
What I need help with: The above report will show several lines that fall under each code. For example, there might be a bunch of lines by the line numbers 2,3,4,5 and then 11,12,13,14 and then 45,46,47 that all are shown under a single code. My problem is that I want to conditionally format the "text in line" field (and/or "Line number" field) in a way that it changes its colour if a new section of the text (that is not adjacent to the previous section) is associated to the same code. This would be very helpful visually.
In the above example, the lines 2,3,4,5 would have no colour, but the 11,12,13,14 should be coloured. Then comes the liens 45,45,47, and these again, should not have any colour (just like lines 2,3,4,5) or should have a different colour, just so that it makes it visually easier to read and understand that these are different parts of a text that are not adjacent. [What I need is just like what Excel does with tables: one can apply colours to lines so to make it easier to follow. however, the difference here is that if there are multiple consecutive lines, they should all have the same colour and the next batch of lines should have another colour]
Any help would be much appreciated!
Please let me know if you had any questions or if I was not clear enough.