Understanding IIF

[NoelleW]

I have two tables. One with multiple records per person (MULTIPLE). One with a single record per person (UNIQUE) (see below samples). I want to update the UNIQUE table based on multiple records/values from the MULTIPLE with various conditions. For example, if any of the records for a given person in MULTIPLE have a living arrangement of "HOMELESS", then populate the HOMELESS field in the UNIQUE table with "YES"

I tried using basic IIF function (IIf(([MULTIPLE].LIVARR="SHELTER" Or [MULTIPLE].LIVARR="HOMELESS"),"YES","NO"),
However, if a person has both Yes and No for homeless status on different records, it seems to be a crapshoot as to which "truth" populates the UNIQUE table.

Any suggestions?

And can anyone tell me how the IIF works in such an instance? would it go through the records sequentially, or something else?

Much thanks in advance!

MULTIPLE TABLE:
ClientID DATE LIVARR
123 1/1/2011 PRIVATE
123 6/1/2010 HOMELESS
123 5/7/2008 PUBLIC


UNIQUE TABLE:
ClientID HOMELESS
123 ???

 

[boblarson]

Not trying to be mean here, so please don't take it that way. But you do not store that value in the unique table. You just need a query to get the data out.

So, what is your reasoning for having it in the unique table as well?

(I am hinting at data normalization which would not have you store duplicate data)

 

[NoelleW]

Thanks for the reply.

I'm creating the UNIQUE table as an interim step to export the data into another software package for reporting only. it isn't part of the ongoing data entry/storage.

I still am not understanding how to "control" the IIF so that it takes the value I am looking for out of multiple possibilities across records for an individual.

 

[DCrake]

To get the correct status you would have to use the last date as the most up to date status so use a DLookup() based on the Max(Date) and user id

 

[NoelleW]

Yes, I've used that in the past. But in this instance, no sort order makes sense. I simply want if ANY record has X as the value in the field, then return "YES". Could be earliest, most recent, or any record in between...

 

[DCrake]

I don't understand your logic. If you are storing historic data for a person as to their current living arrangements and they were homeless 2 years ago but now live in a rented house then logically they are not homeless at present. So you would need to find the most current status for the person

So you would find the correct status as described earlier.

 

[NoelleW]

The question is have they EVER been homeless...not what their current status is.

 

[DCrake]

Ok then you need to use a DLookup on the field for the key word "homeless" and if it exists then True otherwise false

IIF(Nz(DLookup([ClientID],"Table","[Field]='HOMELESS'),0)=0,False,True)

 

[NoelleW]

Awesome, thanks! And if I need to have something conditional? e.g., [Field]= 'Homeless' or [Field]='Shelter'....how do i nest the Or in there?

 

[DCrake]

IIF(Nz(DLookup([ClientID],"Table","[Field]='HOMELESS' OR [Field]='SHELTER'),0)=0",False,True)

Or

IIF(Nz(DLookup([ClientID],"Table","[Field] In('HOMELESS','SHELTER')=0",False,True)

 

[NoelleW]

Fabulous. Thanks!

 

[DCrake]

Glad to be of help

 

[boblarson]

Thanks for the reply.

I'm creating the UNIQUE table as an interim step to export the data into another software package for reporting only. it isn't part of the ongoing data entry/storage.

Are you aware that you can use a query just like a table? So, unless you are adding an autonumber field, you could use a select query instead of making a table (and that helps keep database bloat down, where make table queries bloat the database).

 

[tezread]

I would have a table called

tlb_contact (with the persons contacts details - and a primary key contactid)

I would have a 2nd table called tbl_episode with a primary key episode_id but a foreign key of contactid. In that table you would have one or more records of the persons living arrangements - that way you can see how a person's circumstances have changed over time and the audit trail is more efficient. In that table have 'living status' field and date, then wyhen you run the query you would select the 'latest' living status