Simple organization chart type layout report (1 Viewer)

[sponsoraw]

Hi guys, I've got a bit of a challenge here and I want to pick your brains on it. I'm looking to create a simple organization chart type layout report, but not sure if it's possible at all.

Imagine a school being the highest level, then you have several classes (2nd level) containing several pupils in each class (lowest level). The school name is just a name which can sit in report or page header so it's not a problem. Normally you will have the class name control sitting in the header (like a label) and then the pupils control in the detail section (acting as a list of pupils for the class).

The problem I got that the classes and pupils are dynamic. I can't just stick a fixed number of class controls in the header as they will very from a school to school. How do I create a dynamic control of classes which will then display me associated pupils for each class?

I hope my analogy make sense.

Any ideas will be much appreciated?

PS. I'm a beginner with coding.

Thanks
Adrian

 

[JHB]

Could you post a printscreen of how you want it?

 

[sponsoraw]

Example as requested.

 

[spikepl]

You can make reports do many things but sometimes it simply isn't worth the effort. Off-hand, I'd prepare this stuff in Access and export it to Excel by writing in each Excel cell

 

[CJ_London]

I have done something like this before with forms rather than reports, so just my suggestion, but assuming your data is normalised the principle would be to have

1. a main report (single) with highest level as the recordsource
2. a subreport based on a crosstab for the second level and filtered on the highest level (single)
3. a subreport for each second level value based on a select query filtered on the second level entry (continuous or datasheet) using linkchild/linkmaster. Note you only need one subreport 'form', the same one can be used in multiple subreport 'controls'.

The issue you are going to have is layout in terms of balance for varying numbers of second level columns. You also have some text below some of the columns which may also be an issue.

In terms of balance, your crosstab for second level will need to return calculated incremental column headings such as 'class1','class2' etc (note not class42, class53) with the value being the class name - the value column will be 'first' rather than 'sum'

You can then use class1, class2 as your control controlsources. Have as many controls as required for the maximum requirement.

to position them evenly across the page, you need VBA to

a. hide controls which do not have a value. Or make their width zero or perhaps left align on Class1, making sure that Class1 is 'on top'
b. have an algorithm to space out the controls evenly across the page, and perhaps resize them as well. You'll need to experiment but suggest the onformat event
c. a continuation of that algorithm for the lines - the main issue here will be lining up the vertical lines that are in the level2 subreport with the ones on the main report. Recommend keep the 'class1', 'class2' controls top at 0 (no borders) and the height of the subreport control (again, no borders) the same as the height of the class1, class2 controls.

It is normally considered bad practice to name controls abc1, abc2 etc, but in this situation, it is important that you do (so name subreport controls SR1, SR2 etc. so that your algorithm (in main form format event) would be something like

 For I=1 to 20
     with SRClass.form("class" & I)
         if isnull(.value) then 'hide controls
             .visible=false 'hide class control
             me("SR" & I).visible=false 'hide 3rd level subreport
         else 'space controls with 100 twips between them
             .left=SRClass.form("class" & I-1).left+SRClass.form("class" & I-1).width+100
         end if
     end with
 next i

Not sure about your conditional formatting, depends on the rules

 

[JHB]

You can make reports do many things but sometimes it simply isn't worth the effort. Off-hand, I'd prepare this stuff in Access and export it to Excel by writing in each Excel cell

I do agree, I would do the same - use Excel.

 

[spikepl]

I'd advise you to stick to the KISS-principle. Any Access report will go belly-up the moment the size of your chart crosses the page size. Then yes, you can fiddle such things in Access but it is a lot of effort and for what? Prepare data in Access, output it in Excel.

 

[sponsoraw]

Thanks guys for your help. It does seem not worth spending days on one report. It still was worth trying at least.

 

[vbaInet]

Side note: I was thinking along the same lines as CJ if it was going to be done in Access but I agree with everyone else regarding keeping it simple. Besides it's best to use the right tools for the right job and in this case it would be Visio! And Visio can be driven through VBA too.