I have a query set up where there are 4 tables:
t_zipcode
pk zipnr
state

t_firm ( of course it has more variables but im only listing what is relevant)
pk firmnr
firm1
fk zipnr

t_dept
pk deptnr
dept

t_contact
pk contactnr
name
fk firmnr
fk zipnr
fk deptnr

ok thats what the tables look like then i set up a query where i want to see the following

t_firm.firm1 t_contact.deptnr t_zipcode.zipnr t_zipcode.state

ok that was all simple to do...im new at this but the query looks great up until i want to add something to the query

so first i told the query under zipcode i want it for zip codes between 20000 and 29999 lets say...worked beautiful

i can also tell i want to see at the same time only dept between 1 and 7 and all data comes out everything is fine have double proofed

ok now i want to tell the query, and i dont know how, have tried for days and cant get the correct iif funktion so maybe its something else...

i want to tell the query now if in one firm there a more than 3 depts i just want to see dept 1 for ex.... inf firm x there are 3 depts dept 1 and dept 2 and dept 3 i can see them all on my query but we want to mail something to only the dept heads in dept 1 if there is a dept dept 1 if there isnt a dept 1 then check to see if there is a dept 2 if so, then choose dept 2 but also it gets trickier...if i have both dept 1 and dept 2 it should pick then automatically dept 1 on the list and not show dept 2. same goes if there are dept 2 and dept 3 at the same time then dept 2 will be chosen.... is that clear enough...

i had tried iif([t_dept]![deptnr]=1 or [t_dept]![deptnr]=2, 1,next iif and so on.....
the problem.... at the end of the long iif statement... i just get everything in the query...i know my iif statement is not right but dont know what to do can someone help.. i hope i was clear enough

thanks ben