Challenging Report Function

[Tom]

I need some help with a function that I'd like to utilize in a report.

The following is some sample data of a report (ID, ORG, SUB-ORG are the
field headers):

ID ORG SUB-ORG
1 ABC F, G, H
DEF G, R
2 DEF M
GHI H, K, M
3 KLM S
NOP R, T



According to the example, records are grouped by the ID field. It then
shows the organisation (ORG) and the interaction with any sub-organizations
(SUB-ORG).

Here's what I'm trying to achieve:


1.For each ID (here 1 or 2 or 3), 'BOLD' the "sub-organization letter" in
the SUB-ORG string if it exists in multiple organizations.


EXAMPLES:

In ID "1", SUB-ORG "G" exists as the 2nd sub-organization of ORG "ABC".
Therefore, I want to "bold" the letter "G"... NOT the letters "F" and "H"

In ID "1", SUB-ORG "G" exists as the 1st sub-organization of ORG "DEF".
Therefore, I want to "bold" the letter "G"... NOT the letter "R"

In ID "2", SUB-ORG "M" exists as the only sub-organization of ORG "DEF".
Therefore, I want to "bold" the letter "M"

In ID "2", SUB-ORG "M" exists as the 3rd sub-organization of ORG "GHI".
Therefore, I want to "bold" the letter "M"... NOT the letters "H" and "K"

In ID "3", nothing should be "bold" in the SUB-ORG string since there are no
multiple instances of any of the sub-organizations (S, R, T are unique for
ID "3").



I am not sure if this can be done... otherwise, any advice to solve this
function is truly welcomed.


Thanks,
Tom

 

[Duane Hookom]

This is next to impossible if you are storing multiple values in a single
field.

 

[Tom]

Duane,

I kinda figured that... thanks for confirming.

Tom

 

[Tom]

Duane,

.... follow-up question: What if the sub-orgs were not concatenated in the
long (currently separated by commas). In actuality, all of the sub-orgs
are stored individually.

Would there then be a way to bold them if they were to occur more than 1
time under the same ID? Basically, they would then be displayed in matrix
format.

Thanks,
Tom

 

[Duane Hookom]

I would have to guess how the sub-orgs are stored (normalized or not) and
then suggest a solution. I would rather not guess. Could you provide some
actual sample records?

 

[Tom]

Duane:

Thanks for the reply...

I have tables:
1. tblDivision
2. tblBranch
3. tblTasks
4. tbl BranchTasks

Each division has 1 or more branches; each branch has 1 or more tasks.

The tblTasks is "static" and contains all possible tasks (once in a while,
we may add tasks to it though).

The table that contains the linkage between the divisions, branches, and
what tasks "they are doing" are stored in the tblBranchTasks.

TblBranchTasks also contains the fields that indicate the linkages amongst
the divisions.

Essentially, there are currently 16 divisions. At this time, the 16 fields
only store 1 value ("Y" for "collaboration exist with that branch" or "N"
for "no collaboration is existing).


Example Data:

Let's say we 4 divisions

TASKNO DIV BRANCH DIV01 DIV02 DIV03 DIV04
1 01 01 Y Y
1 01 02 Y Y
2 02 03 Y Y
2 03 04 Y Y

In the actual report, I "translate" the "Ys" into their appropriate
fieldname and then string them together, separated by commas.

So, I get...
1 DIV02, DIV04
1 DIV01, DIV04
2 DIV01, DIV03
3 DIV01, DIV02

Reality is that I have certainly many more linkages between the divisions.
In this example though, I want to "bold" the "DIV04" in the string of the
1st two records and bold DIV01 in the string of the last two records.

Hopefully that makes sense?!

Thanks,
Tom





This structure really works well for me.

 

[Duane Hookom]

I would normalize the table prior to doing anything else. This would involve
removing the DIV fields and creating separate records in a related table. If
this isn't possible then I would create a union query that normalizes the
data. A normalized table structure would allow you to create a totals query
that would count the number of times a DIV is present in the table.

Does this make sense so far?

 

[Tom]

Duane:

I doubt we'll able to change our current structure soon. It seems that
unless we change it there won't be any progress on this issue. Right?!

Tom

 

[Duane Hookom]

There could be progress but I don't have the time to work you through the
various queries it would take to normalize the data to more easily create
your report. Bottom-line, if you don't commit to the change, I can't commit
my time to help :-(

 

[Tom]

Duane,

that sounds fair... it not that I don't want to commit my time... it's just
that I won't influence such change in tbl design.

Thanks anyhow,
Tom

Tom

 

[Duane Hookom]

You can create a union query that resembles a normalized table. This would
allow you to then create a totals query that groups by and counts the DIVs.
Add this query to your report's record source query and you can use the On
Format event to format the DIVs with a count greater than one.