Return a value if it meets criteria

[Guest]

I am trying to create an organization chart that automatically populates via
specific codes designated to the individuals. I have a table (Personnel) that
lists names (first, last, MI), rank, and other personal information. I added
a text field (OrgChart) and input information to show how personnel fell onto
the Organizational Chart. For example, A Squad's Leader is A01 and his
assistant is A02. The four teams under him (1-4) each have four personnel
within them, so I coded them A11 (team leader), A12 (first member), A13,
(second member) and A14 (third member). The next team is A21, A22, A23 and
A24 and so on. We have 100 personnel, so changing an Org Chart can be time
consuming, so I was hoping to create a form that has numerous text boxes
within it (set up to reflect how our org chart looks) and have each box
calculate that if it met a specific value, then the corresponding rank and
name would fall into the box. I tried =Iif([OrgChart]="A01",[Last Name]," ")
but that only brought back 100 boxes with blanks and the one box that met the
criteria showed the individual's name. Please know I only used Name (not
Rank), for testing purposes in the shown calculation, but I want Rank to show
on what I actually create. Is there a way to do this?

Thank you in advance for your help. I love this site and all the help it
has provided me.

Sue

 

[GeoffG]

Suggestions:

1. Check out Microsoft Visio Professional, which used to be Microsoft's
product for producing charts, including organisational charts.

2. Check out Powerpoint.

3. If you need to keep the data in Access and, if you were a VBA
programmer, you might be able to write Automation code in Access to start
Powerpoint and create the chart. This would be tricky, but I think
theoretically possible. Using the Immediate If (IIf) statement is probably
doomed to failure.

Geoff

 

[Guest]

Thanks for the response. I do need to keep this within Access, seeing as the
database we have is quite massive and is used to track numerous programs
(about 15) and it creates about 100 reports. I've done some basic
programming, but wouldn't know where to start on what was referenced
(starting PowerPoint). I am open to other suggestions or steps on how to
perform the VBA-ppt option.

Thanks again for all your help - I look forward to more responses.
--
Sue

Suggestions:

1. Check out Microsoft Visio Professional, which used to be Microsoft's
product for producing charts, including organisational charts.

2. Check out Powerpoint.

3. If you need to keep the data in Access and, if you were a VBA
programmer, you might be able to write Automation code in Access to start
Powerpoint and create the chart. This would be tricky, but I think
theoretically possible. Using the Immediate If (IIf) statement is probably
doomed to failure.

Geoff

I am trying to create an organization chart that automatically populates
via
specific codes designated to the individuals. I have a table (Personnel)
that
lists names (first, last, MI), rank, and other personal information. I
added
a text field (OrgChart) and input information to show how personnel fell
onto
the Organizational Chart. For example, A Squad's Leader is A01 and his
assistant is A02. The four teams under him (1-4) each have four personnel
within them, so I coded them A11 (team leader), A12 (first member), A13,
(second member) and A14 (third member). The next team is A21, A22, A23
and
A24 and so on. We have 100 personnel, so changing an Org Chart can be
time
consuming, so I was hoping to create a form that has numerous text boxes
within it (set up to reflect how our org chart looks) and have each box
calculate that if it met a specific value, then the corresponding rank and
name would fall into the box. I tried =Iif([OrgChart]="A01",[Last Name],"
")
but that only brought back 100 boxes with blanks and the one box that met
the
criteria showed the individual's name. Please know I only used Name (not
Rank), for testing purposes in the shown calculation, but I want Rank to
show
on what I actually create. Is there a way to do this?

Thank you in advance for your help. I love this site and all the help it
has provided me.

Sue

 

[mcescher]

Thanks for the response. I do need to keep this within Access, seeing as the
database we have is quite massive and is used to track numerous programs
(about 15) and it creates about 100 reports. I've done some basic
programming, but wouldn't know where to start on what was referenced
(starting PowerPoint). I am open to other suggestions or steps on how to
perform the VBA-ppt option.

Thanks again for all your help - I look forward to more responses.
--
Sue

Suggestions:

1. Check out Microsoft Visio Professional, which used to be Microsoft's
product for producing charts, including organisational charts.

2. Check out Powerpoint.

3. If you need to keep the data in Access and, if you were a VBA
programmer, you might be able to write Automation code in Access to start
Powerpoint and create the chart. This would be tricky, but I think
theoretically possible. Using the Immediate If (IIf) statement is probably
doomed to failure.

Geoff

I am trying to create an organization chart that automatically populates
via
specific codes designated to the individuals. I have a table (Personnel)
that
lists names (first, last, MI), rank, and other personal information. I
added
a text field (OrgChart) and input information to show how personnel fell
onto
the Organizational Chart. For example, A Squad's Leader is A01 and his
assistant is A02. The four teams under him (1-4) each have four personnel
within them, so I coded them A11 (team leader), A12 (first member), A13,
(second member) and A14 (third member). The next team is A21, A22, A23
and
A24 and so on. We have 100 personnel, so changing an Org Chart can be
time
consuming, so I was hoping to create a form that has numerous text boxes
within it (set up to reflect how our org chart looks) and have each box
calculate that if it met a specific value, then the corresponding rank and
name would fall into the box. I tried =Iif([OrgChart]="A01",[Last Name],"
")
but that only brought back 100 boxes with blanks and the one box that met
the
criteria showed the individual's name. Please know I only used Name (not
Rank), for testing purposes in the shown calculation, but I want Rank to
show
on what I actually create. Is there a way to do this?
Thank you in advance for your help. I love this site and all the help it
has provided me.
Sue

- Show quoted text -

You might try adding an id field, and then referencing that with a
ReportsTo field.

ID FirstName ReportsTo
1 Bob 0 ' The big boss
2 Dave 1 ' Bobs assistant
3 Brian 1 ' Bobs other assistant
4 Phil 2
5 Steve 3
6 Willie 3
etc...

You could create a report. It might not be quite as graphical as
Visio or PowerPoint, but easier to program around, and being based on
a table, it should make maintainance easier

Hope this helps,
Chris M.

 

[Bob Quintal]

Thanks for the response. I do need to keep this within Access,
seeing as the database we have is quite massive and is used to
track numerous programs (about 15) and it creates about 100
reports. I've done some basic programming, but wouldn't know
where to start on what was referenced (starting PowerPoint). I am
open to other suggestions or steps on how to perform the VBA-ppt
option.

Thanks again for all your help - I look forward to more responses.

Visio has an Org Chart automation tool that interfaces very well
with Access.In your data, you would need a unique ID number for each
box on the chart, and another to hold the ID number of the parent
box.

You open Visio, select the Org Chart Wizard, select the appropriate
Access table or query, answer a few simple questions and then watch
Visio create the chart very quickly.

 

[GeoffG]

I've been experimenting with automating Powerpoint from Access. The
Powerpoint 2002 object model includes new programming objects that simplify
the coding process for creating organizational charts. These new
programming objects should be in later versions of Powerpoint. If you're
using Powerpoint 2000, I think the job is just too difficult.

If you're still looking for a Powerpoint solution:

Which version of Microsoft Office are you using?

What size paper will you be printing the chart on? (You have 100+ staff.)

Will each person be in their own box? Or how do you see the chart
appearing?

Regards
Geoff