Nested IIF too complex? Causing access to freeze and not respond??

ktan075

New member
Local time
Today, 19:56
Joined
Feb 18, 2010
Messages
3
Hi,

I have written in expression for with 6 nested IIFs as shown below.

IIF(Assb_1.Child is null, PartsMaster_1.PartName, IIF( Assb_2.Child is null, PartsMaster_2.PartName, IIF(Assb_3.Child is null, PartsMaster_3.PartName, IIF( Assb_4.Child is null, PartsMaster_4.PartName, IIF( Assb_5.Child is null, PartsMaster_5.PartName, IIF( Assb_6.Child is null, PartsMaster_6.PartName, PartsMaster_7.PartName))))))

i did a trial while i was setting it up and it works when there are only 3 nested IIFs.

But when i tried it with all 6, access completely freezes and i am even able to save it.

Is there any other more effecient way of achievhing this?

Thanks!
 
The underlying problem is your database structure.
The multiple variants of Assb and PartsMaster should most likely all be combined to single Assb and PartMaster tables.

Post more information about your table structures.
 
If you need to rely on Iif statements like this, something is wrong with your design.

There is absolutely no way to advise you other than that with the information you've provided us. Let us know what the tables look like and what your entire SQL statement for your query looks like and we can take another stab at it.
 
Hi,

Thanks for your replies.

what i am trying to do is create a multi-level bom based on a table (Excel sheet - assb) of Parent and Child fields. The expected results are shown in (excel sheet - query result). There can be a maximum of 8 levels in this BOM.

What my nested IIF expression is trying to achieve is to create a column which compiles the lowest level compoment in each line.

This was the only way i could think of doing this. Any other more efficient way of achieveing this is warmly welcomed.

By the way, I am newish to access so not really sure how to do VBA or other fancy stuff.

Thanks a lot! :)
 

Attachments

  • query.jpg
    query.jpg
    103.2 KB · Views: 152
  • relationships.jpg
    relationships.jpg
    93.3 KB · Views: 140
  • tables.xls
    tables.xls
    37 KB · Views: 127
  • query sql.txt
    query sql.txt
    6 KB · Views: 111
Holy mackerel Batman!

What is the objective of this query? Who are you giving it to and how will they interpret it, and for what purpose (purchase orders, financial reports, etc.)? What do the letters in the BOM fields mean/indicate?

What do the sub queries look like?
 
Basically,
the user will choose an end-user product. and the query will bring up the entire Bill of materials for that product with all the compoments in its respective location within the BOM. it will also bring up the cost for each item, lead times and the supplier.

The letters are just peusdo compoment names as shown in bom.png. Sorry i thought i had added this in as an attachment before.
 

Attachments

  • bom.png
    bom.png
    18.3 KB · Views: 152

Users who are viewing this thread

Back
Top Bottom