如果然后语句查看具有多个值的字段
我的查询具有带多个值的计算字段。
值为10R，10W，10S，10C和10M。该字段可以生成答案，作为这些值中的任何一个或这些值的任何组合。例如，一个人可能会得到这样的结果：10R，10S
另一个可能是：10W，10C（该字段是由逗号分隔的一个或多个值。
我想知道是否有一个函数只查看表示该字段中所有值的字符串的一部分。我找到了这个例子：语言：IIf（[CountryRegion] =" Italy"，" ;意大利语，其他语言）
是否可以将其修改为仅查看字段的某些部分或键入单个值？如下所示：
阅读：IIf（[WhichTests] =" 10R"，" Reading"，""）
这看起来是字段WhereTests如果10R是其中一个值，它会在列中放入Reading，否则，它将为空。这可能吗？谢谢。
I have a query that has a calculated field with multiple values.
The values are 10R, 10W, 10S, 10C, and 10M. The field may generate an answer as any one of these values or any combination of these values. For example one person may have this as the result: 10R,10S
Another may be: 10W, 10C (The field is one or multiple values separated by commas.
I am wondering is there a function that looks at just part of the string representing all of the values in that field. I found this example:Language: IIf([CountryRegion]="Italy", "Italian", "Some other language")
Is it possible to modify this to only look at parts of the field or to key in on a single value? Something like this:
Reading : IIf([WhichTests] = "10R","Reading","")
This would look that the field WhichTests and if 10R is one of the values there, it would put "Reading" in the column, otherwise, it would be blank. Is this possible? Thanks.
计算是否合并了值以使字段包含多个值，或者是否包含多个值表那样存储吗？您永远不希望在记录的单个字段中存储多个值，这个问题只是其中一个原因。如果将它正确存储在具有一对多关系的相关表中，则可以使用带有LEFT JOIN的子查询将相关记录转换为同一记录中的字段。
Does the calculation combine values to make the field contain multiple values, or does the table store it that way? You never want to store multiple values in a single field of a record, with this problem being just one of the reasons. If you have it stored properly in a related table with a one-to-many relationship, then you can use subqueries with a LEFT JOIN to turn the related records into fields on the same record.
这是一个漫长的过程。这是将多个记录连接到单个记录开始的流程的最后一步。每个人或学生必须参加一项或多项考试（最多5次考试）。每个值最初都作为单独的记录列出，因此如果学生必须完成所有五项测试，他或她将有五个记录代表它们。我能够连接数据，以便每个学生都有一个记录，所有的tess都出现在计算字段中。现在，如果我可以从10R到阅读并让每个必须参加阅读测试的学生，请在第一列中显示一个值，并在下一列中创建一个类似的函数，如果10M是值，则生成Math为任何和所有学生列出。因此，当我完成每个测试将在它自己的列中表示，如果学生没有进行特定的测试，则该单元格将为空白。我无法改变数据的原始格式。我能做的最好的事情是将它连接到一个记录，但我需要在它自己的列中进行每个测试，而不是作为一组值。你怎么看？
This has been a long process. This is the final step in a process that started with concatenating multiple records into a single record. Each person or student has to take one or more test (up to 5 tests). Each value was originally listed as a separate record, so if a student had to take all five tests, he or she would have five records represent them. I was able to concatenate the data so that each student has a single record and all of their tess appear in the calculated field. Now, if I could go from 10R to Reading and have every student who has to take the reading test, have a value show up in the first column and create a similar function in the next column that would generate Math if 10M is among the values listed for any and all students. So that when I finish Each test would be represented in it''s own column and if the student did not have that particular test to take, the cell would be blank. I can not alter the original format of the data. The best I was able to do was to concatenate it to a single record, but I need each test in it''s own column, not as a group of values. What do you think?
正确的表格设计会要求你有一个Students表和一个Student_Tests表。每个学生将在Students表中输入一次，但Student_Tests表将具有学生的Primary Key值以及学生在每个测试中作为单独记录的测试。因此，如果学生已经完成了所有五项测试，那么Student_Tests中将有五条记录。请参阅数据库规范化和表结构。如果不遵循这些指导原则，您将继续遇到问题，因为您当前的设计将很难利用这些信息来生成有价值的信息。
有一种方法可以使用IIF（）函数正如您所描述的那样，但它不是推荐的解决方案。您可以在表达式中使用LIKE运算符以及*通配符。
Proper table design would dictate that you have a Students table and a Student_Tests table. Each student would be entered once in the Students table, but then the Student_Tests table would have the Primary Key value for the student and the test that the student takes with each test being a separate record. So if a student had taken all five tests, then there would be five records in Student_Tests. See Database Normalization and Table Structures. By not following these guidelines, you will continue to run into issues because your current design will make it very difficult to utilize the information to produce valuable information.
There is a way to use the IIF() function as you have described, but it is not a recommended solution. You can use the LIKE operator in your expression along with the * wildcards.