(Quick note - this was originally posted under the thread title - Noob needs help! LOL! - but I have since read posting info and realised this was not really helpful for anyone and so have reposted! Sorry for not reading before posting!)

Hi all,

Although this seems to be about forms, please bear with me to the end to see the table related question!

I’m a relative Noob to Access and have got myself going round in circles as to how to set up some tables to help create a form and would like your advice and input, please!
I originally created a database consisting of 6 tables, but found that I needed to split some of the tables up to clarify the data more – so I split some of them up. The main form is working fine and I have no problems there, but the Subform (explained below) is not linking as expected. I have had a look through this forum, but I'm not really sure where the problem originates, so I'm not having much luck with my search (bit hard searching for an answer to a question you don't know! - Ask Arthur Dent!!).

Currently I have these five tables that are causing my head to spin:

Exams
-Autonumber (primary field)
-Username(field that links this subform with main form info)
-Exam
-Registration Details
-Status

Course
-Autonumber (primary field)
-Username
-Exam
-Course
-Start Date
-End Date
-Extended End Date
-Registered?
-Exam Taken?
-Sent off?
-Date Taken
-Date Sent
-Date Result Received
-Result

Course (Codes and Exams)
-Course Code (primary key)
-Course (standard name – as used in Course table)
-Exam (which Exam each Course is linked to)

Comments
-Autonumber (primary key)
-Date
-Username (to link to main form))
-Comment
-Course (to link to Course table)

Reviews
-Autonumber (primary key)
-Username (to link to main form)
-Date
-Review Notes
-Course (to link to Course table)

Relationships are as follows:
Exams links to my main form through ‘Username[/i] (This is the primary key on the main form)
Exams links to Course (Codes and Exams) through Exam
Comments and Reviews are linked to Course through Course
Course is linked to Course (Codes and Exams) through Course


What I want to do is have a subform from Exams inside which would be another subform Course which would contain subforms to both Comments and Reviews (as they are course specific).
Now – here’s the problem:
The courses all link to exams (not talking ‘in Access’ here) – what I want to be able to do is choose an exam in the main subform and have that limit the list of courses that you can choose and see in the courses subform.
I created the intermediary table Course (Codes and Exams), but I don’t seem to have done it right (LOL) as I Chose the Exam field of the Exams table to be:
Date Type – Text
Display Control - Combo Box
Row Source Type – Table/Query
Row Source – Course (Codes and Exams)
but regardless of which number I put in Bound Column, it only shows me the Course Code Column. And I haven’t been able to move on from there!
Any and all help/input graciously accepted,

Bird

Originally posted as a reply to original thread name:

Originally Posted by Bird_FAT http://www.access-programmers.co.uk/forums/images/buttons/viewpost.gif (http://www.access-programmers.co.uk/forums/showthread.php?p=831620#post831620)
The courses all link to exams (not talking ‘in Access’ here) – what I want to be able to do is choose an exam in the main subform and have that limit the list of courses that you can choose and see in the courses subform.

So are you saying that an Exam has many courses? Also can a course apply to different exams?
Chris

Originally Posted by stopher http://www.access-programmers.co.uk/forums/images/buttons/viewpost.gif (http://www.access-programmers.co.uk/forums/showthread.php?p=831622#post831622)
So are you saying that an Exam has many courses? Also can a course apply to different exams?
Chris


Hey Chris,

Yes - there are a few courses that can apply to different exams. I was thinking of adding a unique reference to the table - i.e Autonumber so that I could change it to the primary key, and thus repeat the few courses that have multiple exams.

Bird

I still don't really understand your model. But lets start with the Exams table...

I think of an exam as a piece on paper with a specific date at the top and a list of questions. This is not what your table Exams describes. Your Exams table looks like it's designed to list people who sit an exam. Apolgies if that sounds trivial but I'm just trying to get my head round this.

Where does username come in? Is that the name of a student?

Chris

I still don't really understand your model. But lets start with the Exams table...

I think of an exam as a piece on paper with a specific date at the top and a list of questions. This is not what your table Exams describes. Your Exams table looks like it's designed to list people who sit an exam. Apolgies if that sounds trivial but I'm just trying to get my head round this.

Where does username come in? Is that the name of a student?

Chris

http://i235.photobucket.com/albums/ee84/Bird_FAT/Relationships.jpg

OK I'll have a go at explaining the model

There are additional tables that go towards a main form (ones that I have no problem with), that only link to this data through that Username. The whole thing is basically a student tracking database.

What I have on the main form is the student details - username, forename, surname, contact details, network area.

As I said - there are two other subforms here, but they only link to the Tables above at one point - through the Exams table username.

The Exams table is to show which exam they are planning on taking, what their exam details are and whether they are in process, or have finished this particular exam yet.

The exams can have various courses to them (in example ECDL has 7 modules - MOS has 5 core and 2 expert modules, etc.), so the Courses table is to keep track of all the details relating to the individual exams.

The Course (Codes and Exams) table was an attempt to try and make it so that a Course subform placed inside an Exam form would allow me to choose an exam and have it then limit the choices of course to the ones related to that course ONLY.

A student (Username) can do multiple exams if they wish, and then this would minimise the sifting of data if I could run through the exam form, thus limiting the data on view, rather than than through every course done!

Does that help explain a bit more - or would you like me to up a blank version of the database? (I'll add a few fake students to help see where it's all going.)

Bird

I think your design is not far off but would help a bit of renaming for clarity:
Note:
• don’t use non-alphanumeric characters in table or field names. Don’t use spaces either.
• the table headings are more aligned to what the table stores
• the primary keys are given proper names so you can easily identify them later

Exams
ExamID (PK, autonumber)
ExamName

Courses
CourseID (PK, autonumber)
CourseName
StartDate
EndDate
Etc

Student
StudentID (PK, autonumber)
StudentName

ExamCourse
ExamID (PK)
CourseID (PK)

StudentCourse
StudentID (PK)
CourseID (PK)
you can add the comments info here
you can also add the stuff about whether the exam has been taken & result etc

I appreciate this is quite close to what you had in principle. Mine might not be quite right by the way.

It would be worth posting your d/b.

Chris

OK - to ensure that this is a completed thread!

Thanks for the help stopher; After looking at the posts - I went away and got an access book from the library and sat down to read...

I have now simplified the tables a little and realise that this was indeed the problem. So, for any others with a similar issue, please feel free to do the same - after reading and playing for a few days, I realised how I was supposed to link aspects of the different tables better (not quite as above, but closer to stopher's than my originals!).

So, thanks again for all the help stopher, and I hope this helps any others with a similar problem.