Too many iif statements for my query

[Mrs.Meeker]

I have a query which has 14 fields that I need to query. That was 5 too many, (for the maximum of 9) so I split the query up and ended up with two.

This wasn't such a good idea.

Now my data starts over instead of being one continuous list. For example topic one, two, five, seven, twelve, one, two, five...

Can I somehow add more rows to increase my number of iif statements.

I thought about nested iif statements, but not sure how I would do that.

I am attaching the two queries that I wrote for reference. Can anyone help me?

Thanks, Rhonda

 

[Mile-O]

To achieve what you want you are going to have to normalise your table structure.

Having all those topics is the wrong way to go about it as what happens when you need to add a new topic field, five new topic fields, etc. - you'd have to rewrite all your queries, rebuild your forms and reports etc.

Search for normalising on the forum - there are many explanations.

 

[Mrs.Meeker]

This time I managed to compact my database and get it attached. (hmmm...I think) The reason for doing so, is that I am trying to understand normalization. I've done a little reading, but I've never heard of normaliztion before today.

Should I create more tables? The queries are based on fields that help to sort information that produces the reports. The fields 2b, 2o, 3b, 3c, 3r, 4i, 4s, 5t, 5f, 5p, 5v, 6s, 6p, and 6c were supplied to me by the end user. He sorted his 19 pages of topics with these codes.

Should I make a table for each code? Some topics may have three or four codes, so they would then appear in multiple tables.

I thought I was about done with this, until more thorough testing. This is my first database...always seem to learn things the hard way

I'm going back to my reading now.

 

[Mile-O]

Uh oh! You have that table looking almost exactly like an Excel spreadsheet.


First of all create a table with those codes - tblCodes, or something and give it two fields: CodeID and Code.

Personally, I prefer to use autonumbers as my primary keys although in your case the code itself could be the table's primary key.

In the code field you can list all of your codes.

in fact, I'm not too clear on what this table is that needs breaking down - i.e what it refers to.

Ideally, tables should refer to something i.e Employees, Grades, Cities, etc.

This table is, apparently, "quality control" although it has no primary key and I would expect that some of the fields in here don't directly relate to the quality control process and should therefore be in tables of their own.


I think, for further help, you are going to have to explain your tables further as I can't make head nor tail of what they are representing - none of the tables in the example you posted have a primary key yet you've built one-to-one relationships in the relationship window.

 

[Mrs.Meeker]

Okay, I've built this new table named tbl_selection (I already had one named tbl_codes). But some of these items can have more than one selection.

I'll try to answer your question on what this table that needs to be broken down is for.

End users want a check list that they can print out and take with them. There are two kinds of end users. One for quality control and one for quality assurance. I set up the first table and called it quality control but it will also be used to build reports for quality assurance so maybe I should rename it tbl_qcqa.

I placed all those "codes" (which I named selections) in that table in separate columns because some of the "items to check" have more than one selection. I'm not sure how else to tie these items together. The person who got me started here at work said she doesn't use primary keys and that I didn't need them. Now I'm not sure what to use. She is not available to help very often, gives me pointers and then leaves me on my own. In reading the access 97 bible and information I've gotten off the board I've become terribly confused. I think part of the problem is that I'm chiefly an Excel user and I'm having problems getting away from that style of thinking.

I have other tables which include code, heading, subheading, plus the qcqa and selection that I already mentioned. I was also instructed to create an index and to then go into tbl_qcqa and add each heading, subheading, or code into the correct location via a drop down box. (The code table is used exclusively for the quality assurance report.)

I am assuming that I should also create an index for these selections. But like I said before some of these "items to check" have more than one selection.

I hope this makes some sort of sense and that someone can give me a clue. I don't want you to do my work for me, I just want to learn to do it the right way and feel that I've gotten off on the wrong foot.

thanks, Rhonda

 

[Mrs.Meeker]

Well, I think I've finally got some circuits firing in my old muddled brain. I have incorporated all of these choices into one field by duplicating the lines of text and changing the selection.

I was thinking that I could now rewrite my query with the same set of iif statements but changed to a nested iif statement. I have searched through the board and I'm still unsure of how to actually take what I have an turn it into a nested iff statement.

These are my statements:

IIf(([Forms]![frm_DataSelection]![optPackage]=1),"2b","x")
IIf(([Forms]![frm_DataSelection]![optPackage]=2),"2o","x")
IIf(([Forms]![frm_DataSelection]![optProject]=1),"3b","x")
IIf(([Forms]![frm_DataSelection]![optProject]=2),"3c","x")
IIf(([Forms]![frm_DataSelection]![optProject]=3),"3r","x")
IIf(([Forms]![frm_DataSelection]![optAbutment]=1),"4i","x")
IIf(([Forms]![frm_DataSelection]![optAbutment]=2),"4s","x")
IIf(([Forms]![frm_DataSelection]![optPier]=1),"5t","x")
IIf(([Forms]![frm_DataSelection]![optPier]=2),"5f","x")
IIf(([Forms]![frm_DataSelection]![optPier]=3),"5p","x")
IIf(([Forms]![frm_DataSelection]![optPier]=4),"5v","x")
IIf(([Forms]![frm_DataSelection]![optSuperStr]=1),"6s","x")
IIf(([Forms]![frm_DataSelection]![optSuperStr]=2),"6p","x")
IIf(([Forms]![frm_DataSelection]![optSuperStr]=3),"6c","x")

I also have one other code (1a) that is included in all reports. It is in this same field as the others, and I'm not sure if that was a mistake or not. Any with 1a need to be included in every report. Not sure how to write that in.

Any help would be a delight.

Thanks in advance,
Rhonda

 

[Mrs.Meeker]

If you want to look at the changes in my database, it's attached. I think I have it normalized correctly, but now I'm having trouble getting the queries to work. I've been playing around with the nested iif statements and it's not working. When I tried to change it to this:

IIf(([Forms]![frm_DataSelection]![optPackage]=1,"2b",
([Forms]![frm_DataSelection]![optPackage]=2,"2o",
([Forms]![frm_DataSelection]![optProject]=1,"3b",
([Forms]![frm_DataSelection]![optProject]=2,"3c",
([Forms]![frm_DataSelection]![optProject]=3,"3r",
([Forms]![frm_DataSelection]![optAbutment]=1,"4i",
([Forms]![frm_DataSelection]![optAbutment]=2,"4s",
([Forms]![frm_DataSelection]![optPier]=1,"5t",
([Forms]![frm_DataSelection]![optPier]=2,"5f",
([Forms]![frm_DataSelection]![optPier]=3,"5p",
([Forms]![frm_DataSelection]![optPier]=4,"5v",
([Forms]![frm_DataSelection]![optSuperStr]=1,"6s",
([Forms]![frm_DataSelection]![optSuperStr]=2,"6p",
([Forms]![frm_DataSelection]![optSuperStr]=3,"6c","x")))))))))))))))

I recieved error messages.

I went back down the hall and asked the person who started me off on this and she said I had to go back to the table with all the fields that you guys told me to get rid of and write make table and apend queries and I'm not really wanting to go back to something I can't maintain. She said I could maintain it by continuing to write apend queries.

I have a bad feeling about this and I'd rather find a better way. Any help?

Thanks, Rhonda

 

[Mrs.Meeker]

It works

I got some help from the programmer down the hall and it works the way it's supposed to. It isn't done the way you suggested but I thought you might like to see it in action. She tells me that any addition fields that may need to be added in the future can also be done with delete and append querries.

This is making learning a little difficult for me but this project had to be completed.

Rhonda

 

[Mrs.Meeker]

oops! forgot the file