Stuck on IIf

[popen_73]

Hi.
Here is an expression in my query:

incident: IIf([tbl_attendance].[att_st]=-1,"Street",IIf([tbl_attendance].[att_wk]=-1,"work",IIf([tbl_attendance].[att_illness]=-1,"Illness",IIf([tbl_attendance].[att_sport]=-1,"Sport",IIf([tbl_attendance].[att_si]=-1,"Self Inflicted",IIf([tbl_attendance].[att_rta]=-1,"RTA",IIf([tbl_attendance].[att_home]=-1,"Home",IIf([tbl_attendance].[att_school]=-1,"School",IIf([tbl_attendance].[att_other]=-1,"Other",IIf([tbl_attendance].[att_st] And [tbl_attendance].[att_wk] And [tbl_attendance].[att_illness] And [tbl_attendance].[att_sport] And [tbl_attendance].[att_si] And [tbl_attendance].[att_rta] And [tbl_attendance].[att_home] And [tbl_attendance].[att_school] And [tbl_attendance].[att_other]<-1,"Multiple"))))))))))

OK, what I am trying to achieve is the following:

ALL the fields shown above are fed to radio buttons on a form. If it is checked, the value is -1. IF the value for a field is -1, then the description shows on the query result. That's the easy part.

Now, if the value of ALL these, or more than one of these per record is -1, then I would like the description to say 'Multiple'.
So, if more than one radion button is selected on the form, this should show as 'multiple'.

Can anybody look at the code and tell me where I am going wrong at the last IIF statement please?

Many thanks is advance,

-NEIL

 

[Mile-O]

Can anybody look at the code and tell me where I am going wrong at the last IIF statement please?

Let's edit that:

Can anybody look at the code and tell me where I am going wrong?

Your data is not normalised. You have a group that should be built in a table DOWN the way, NOT ACROSS the way.

 

[popen_73]

Thanks for that, but can you explain please?

 

[Mile-O]

From what you have written you have a table like this:

Attendance
Street
Work
Illness
Sport
Self-Inflicted
RTA
Other

where all of these fields are checkboxes. I'm guessing there's a person name, ID or something involved in that table too. I'll use that PersonID in the example I'm about to illustrate.

Now, since you are allowing for multiple selections of these values you have a many-to-many relationship that you are not addressing and, instead, burying away in your database to cause you problems....like this one.

What you want is a table for Attendance types, your current attendance table without these checkbox fields, and a new table called AttendanceToTypes or something. This new table creates to one-to-many relationships. One from the PersonID to PersonID in the new table and one from TypeID to TypeID between the new table and attendance types. The two fields in this table become the primary key. Relate these tables properly and you have a many-to-many relationship. One attendance type, from your initial question, can obviously apply to more than person and likewise one person can have more than one attendance type.

Do a search on many-to-many as there have been a lot of questions regarding this of late.

 

[popen_73]

Yep, got it.

I know exactly what you mean, and it makes sense to me.
Thanks for putting me in the right direction with this!

-NEIL