If Then Statement Looking at a Field with Multiple Values

[Promise007]

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:Languag e: 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.

[Seth Schrock]

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.

[Promise007]

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?

[Seth Schrock]

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.

Reading : IIf([WhichTests] LIKE "*10R*","Reading","")

Please note that the LIKE operator is very slow, so depending on the number of records in your database, the query could run very slowly.

[Promise007]

I am following you and my set up matches what you have said. I have a student table that list every student in the building and I have a test table that list every test that has to be taken by every student. Each test is a separate record. The primary key for the student table is the Student ID number and this number is the foreign key in the test table. That is as far as things go. I think from the reading, I need to set up a one to many relationship between the primary key in the student table and the foreign key in the test table. But after that I am lost. How do I take those tests names from the field in the test table and display each unique test in it's own column assigned to student in the query that is taking that particular test? I don't know that mechanism in the query to do that. Thanks.

[Seth Schrock]

Create a subquery on your tests table for each test and join back to the student table based on the student id. Because you will have five subqueries, you can get the TestName from each with the alias of the test name.

[PhilOfWalton]

I hate to disagree with Seth, but ....
There should be 3 tables:
Students
Tests
JoinStudentTest , the latter one something like

StudentID        Combined Unique Key
TestID           Combined Unique Key
TestDate
TestResult
Etc.

So each student is linked with however many or few tests have been taken, and whatever results etc are obtained. (The limit of 5 tests is irrelevant)

Conversely one can look from the Test point of view and ask "Which Students have taken the Reading Test, and what were their results?"

Normally you would have a main form for the students, and a linked subform for the Tests and the results. The subform would be a continuous form, with a Combo Box to select the tests taken.

Phil

[Promise007]

Hopefully, it won't make any difference, but the student information system provides me with a text file that list the OGT test that each student has to take. The list is a alphabetical list where is student has multiple records in the list, depending on how many tests each individual student has to take. I need an alphabetical list where each student has a single record and the test that the student has to take is represented by it's own individual field. I have to add more information to each record such as contact information, so that someone can call that student's contact information and have a list of test to inform the student that they should plan on coming and taking since each test is on a different day.

I have started to research the subquery and so far I have found out that it can only be done with an SQL statement. So I started to look at the basics of SQL statements. I have not done anything on this level in about 6 or 7 years. Actually, I think it was longer than that. The subquery approach seems interesting. Can you give me some more guidance on this? From what I was reading it involves a nested SQL statement. I have seen a couple of example, but I can't quite apply the components of the statement to my particular situation.

[PhilOfWalton]

Your remark that each Student Test is an individual record is why I suggested the 3 tables. The Student should appear only once and needless to say has all the information appertaining a student in that table - address, contact, age .....

Obviously a query will sort that in whatever order you choose (Alphabetically by Last Name, First Name)

The Various tests appear only once and, apart from the test name, they may have additional information say difficulty or examining board.

It is the Join table that contains the variable information.
Initially you join a Student to Test. That means the student has to take that test.
Then on that join table for that record, you may add a Test date.
When the test is taken, you may add the results.

Please read up on Normalisation.

A rough guide is that a table should contain ONLY information related to the subject, so a Student is a person, and he/she exists whether or not they take any tests.

The test exist, and whether anyone takes them or not is irrelevant.

A further guide is if you look at a table and see lots of repeated information - (Students names) you know it is not normalised.

Phil

[Promise007]

I did read the material on database normalization and I understand that.
I have tried to find some information on the Join table. I found some information on this page:

https://support.office.com/en-us/article/Join-tables-and-queries-3f5838bd-24a0-4832-9bc1-07061a1478f6?ui=en-US&rs=en-US&ad=US

Now I am fuzzy on which field names to use to create the combined unique keys for the table that you missed in your previous post. I am going to include a picture of the design view of both tables and it would help me if you would suggest which fields to use in the join table to create the combined unique keys. I also include a picture of a third table to change the code for the test to the test name. The field that lists the test that the students have to take is the Required Test Field.
Thanks.

[PhilOfWalton]

Oh Dear.

I hope you won't take this amiss, but may I give you a number of pointers.

Generally, Spaces in field names and Table names are a bad idea. They need surrounding with square brackets [] when a reference is made to them.

Table OGTest: I assume ID is an AutoNumber & the Key. To Save confusion, change it's name to TestID, and remove the spaces from RequiredTest & OGTTest.

All Students ....
Again I would rename that TblStudents.
Birthdate should be a date field, not text
I would have a field StudentID Autonumber Key
Fiscal year I suspect is nothing to do with a student and I am not sure what GradeLevel is for.

Now your WhoNeedsit table is horrible

It should look more like this:-

StudentID        Combined Unique Key
TestID           Combined Unique Key
DateTested       Date
RequiredScore
Score
Grade

I have no idea what your Local ID is, so further information required on that one.

I would have thought that if RequiredScore is a number, Score should also be a number.
What sort of values does Grade have. Should this also be a table with a Combo box to select the value, in which case this should be changed to GradeID, a long number.

Relationships should be set up and enforced between the 3 tables. (possibly 4 if you include TblGrades)

In this table, by implication, once the StudentID is entered, all the information about the student is accessible.
Similarly, once the TestID is entered, the implication is that this student has to take this test, and as DateTested is added and results added, the picture is built up.

The quick guide that something is wrong is if you look at your existing WhoNeedsIt table, Duplicate Names, gender & Test Codes appear a number of times.

Phil

[Promise007]

I am going to reply quickly so that hopefully I will get your reply back this morning. I understand the spacing and the brackets. I learned that from you first and second post, I just haven’t changed it yet. It works somewhat and I was waiting until I understood everything before I changed everything.
The RequiredTest is the field with the information that I am trying to join to one student.
To go through the fields, the field ID in OGTTest is and autonumber field and Key. I will make those changes. Fiscal year is very import and I just it should be a date field also. It is the year that the student started 9th grade. It determines whether the test that are associated with a student are valid, because students that started after 2015 don’t have to test after all. Grade level is just the grade the student is in. I can put in an automnumber as a key. The key that I had been using was the student ID number issued by the state. The local ID is the student number issued by the school.
WhoNeedsIt is the created by uploading the text file to Access, that the student information system generates. I can make the changes that you suggest, but this the part I don’t quite understand. The file that student system generates comes with each line referring to one of the five tests that have to be given: 10R, 10M,10W, 10S, 10C. And so if a student has to take more than one test, he has multiple lines in the file. I have no way to change this. This is the problem.
The part I don’t get is that how to set up the combined unique key. In the your suggested WhoNeedsIt table what happened to the main field, the RequiredTest field? Will it work if it is not included? I can make all of the changes you have suggested, but just need to know how to create the combined unique keys. Is that a combination of the primary key and the foreign key?
Thanks for your continued help.

[PhilOfWalton]

Thanks for the explanation.

The OGT table look fine other than changing "ID" to TestID" for the sake of clarity.

The Student Table should have StudentID as an autonnuber and the Key. The LocalID from your explanation, is part of the student's record so should be in the Student table.

I have inserted an image of your WhoNeedsIt table (I called it JoinStudentTest which I fee is more descriptive - but that's an aside)



To create a joint key, highlight Both StudentID & TestID together, and press the Key symbol.

Phil