Problems with Multiple Value Fields

[umarus]

Sorry, first I put this thread on wrong place

Hi, I had a database which was fine (See Attachments)
Then I decided to change one field to Multiple Value (Exams.ClassID) and I had to change relations in queries to Exams.ClassID.Value
So now I have very strange problems:
1. Exams Tab, Press New Exam Start Typing: Error. But click Ok and it works
2. Email/Sms Tab, Select Subject and Press exam results: Error. Or just open Exam Scores Crosstab Query and Try to generate AutoReport
And maybe there are more of them.

What is problem with multiple values? Can you explain me? Regards.

Here is working demo.zip and not working demo2.zip

[June7]

Don't understand why an exam would have multiple class IDs.

I don't use multi-value fields because they are so annoying.

http://office.microsoft.com/en-us/ac...001233722.aspx

http://social.msdn.microsoft.com/For...9-67f2128bee14

[umarus]

Thanks, I will look there. I think it would help.
Can you help me with structure of the tables in the example without multiple value

But anyhow MS should fix it, it is not so bad idea and time saver

[June7]

You said the example without multiple value fields is working. I don't right off see anything wrong. What issue are you having?

What does MS need to fix? Multi-value fields work as intended. I just don't like the way they work.

[umarus]

In working example, I have 1 ClassID per ExamID. Thats why I want to use multiple values.
As I mentioned there error and useless messages.

[June7]

The crosstab query runs. Are the results not correct?

You have multi-value field for SubjectID in the Classes table. A single class covers more than one subject? What education level is the db for? Is this db supposed to cover multiple semesters and years? If you use multi-value field for ClassID in Exams table, how will you know which class the student took the exam in or is that not important? Consider the following:

tblStudents
ID

tblClasses
ID

tblClassesSubjects
ID

tblStudentsClasses
ID
ClassesID (FK)
StudentID (FK)

tblExams
ID
ClassesSubjectsID (FK)

tblStudentsExamResults
StudentID (FK)
ExamID (FK)
Result

[umarus]

We have tuition center. by classes I meant groups so one student in one class(group) only. Is it real problem if I use lookup relation between them.
Now because one exam may be for different groups should i make tblExamClass (examId, ClassID). Can you write all structures again.
About crosstab, you see it works but I have to generate Autoreport based on it and it doesn't with multiple value table, i have odd error.
Sorry for troubling it is my first project I am learning basics. Regards

[June7]

What do you mean by 'write all structures again'? I don't intend to build db for you.

Crosstabs are very dynamic. Can't always count on the same fields generated. Stabilizing a crosstab to serve as source data for perpetual report is not easy.

As stated, I don't use multi-value fields and don't want to try to figure out these issues regarding their use. If you want them I am afraid you are on your own.

"should i make tblExamClass (examId, ClassID)" - I just don't know enough about your data and your needs. How will you know which subject the exam is for? Or does the exam cover all subjects in the class? Or is each student studying only one subject in the 'class'?

You need to thoroughly analyse your data entities and their relationships and design db structure to fit.

[umarus]

Thanks, I am asking to write just the names like you did it before. Exam is related to one subject only, and all students who take that subject must write exam ( of course if that exam intended for their class(group)).
But anyhow thanks, I think I need just think (now I aproximately have idea) and will get it