Nested IIF for Hierachy

[Guest]

I have a report that shows equipment in a Hierachy format for each page.
(Equip1, Eqp2.....Eqp5). Each equipment may or may not have a serial number.
For the report I need to see the serial number that corresponds to the last
Eqp in the Heirachy. For example: Pg 1 may have
1 Eqp1
2. Eqp2
3. Eqp3.............. I would want to see the serial number for Eqp3
if there is one or nothing if it is not.
Pg 2. Might have
1 Eqp1
2. Eqp2
3. Eqp3
4. Eqp4.............I would want to see the serial number for Eqp4
if there is one or nothing if it is not.

I was using this:
=IIf([Serial5]<>"" And [Serial5] Is Not Null,[Serial5],IIf([Serial4]<>"" And
[Serial4] Is Not Null,[Serial4],IIf([Serial3]<>"" And [Serial3] Is Not
Null,[Serial3],IIf([Serial2]<>"" And [Serial2] Is Not Null,[Serial2]))))

But I realize that this is only contigent upon the Serial number and not the
actual euip.
I want to be able to say if Eqp 5 Is Null show serial No 4. If not show
Serial 5. If Eqp 4 is Null show seial 3, If not show Seial 4......etc down
to 1. How do I write this or is there a better way to do it.

 

[Guest]

It seems like you have your table setup like a spreadsheet. You need to
have them as intended in a relational database.

Use the Employee-Spuervisor relationship as below.
TblEmployee --
EmpID - primary key
Name
Supervisor - this is a foreign key
Phone
etc.

John Smith - EmpID - 4001
Supervisor - 1005
Bill Jones - EmpID - 4022
Supervisor - 4001
Will Smith - EmpID - 4051
Supervisor - 4001

To do this you place the Employee table in the relationship window twice and
create a one-to-many relation between TblEmployee, field EmpID and
TblEmployee_1, field Supervisor. The employee that is a supervisor will be
in many records as supervisor of other employees.