I am trying to create a cross-tab report that sorts on a field which is the concatenated IDs of several other fields. Basically, there is one main field per record that will always have data, and then up to 3 other fields which may or may not have data. Here's an example:
Field1: Text field that will always be present
Mod1: Text field that may or may not be present
Mod2: Text field that may or may not be present
Mod3: Text field that may or may not be present
So my concatenated field which I am sorting on is a number field that would look like this:
ID_Field1 & ID_Mod1 & ID_Mod2 & ID_Mod3
My thought process is that if only Field1 is present, the sort field will be only the ID of the contents of Field1. But, if I have any number of modifiers, those modifier IDs will be added on to the Field1 ID, which I thought would make it a unique item in the sub summary section. For example:
Record #1: Field1 = "Meeting", ID_Field1 = 5 (Sort field = 5)
Record #2: Field1 = "Meeting", ID_Field1 = 5; Mod1 = "Morning", ID_Mod1 = 1 (Sort field = 51)
Let's say that my columns are days of the week, where the data in the body of the report is simply a "Y" (for Yes) if that event is happening that day. For Sunday, Record #1 is present ("Y" for the "Meeting" row), and for Monday, Record #2 is present ("Y" for the "Meeting, Morning" row). So I would expect that a "Y" would ONLY be present for the "Meeting" row for Sunday and "Meeting, Morning" row for Monday. However, I am getting a "Y" for both rows for both days.
I'm not sure where my misunderstanding lies, though I feel like it almost has to be with the way the sorting is being handled. It's strange that I'm getting the sub-summary rows for each individual combination of Field1 and Mod# fields, but that the body data is present for all rows, even if it should be specific to only one row/record.
The above scenario is a very simple example of the issue that I'm having, not the exact type of data I'm trying to report, but I think that this gives a good indication of the issue I'm running into. Anyone have any ideas why I am getting this duplicate data?