avoiding lookup field

[Pampers]

hi everyone,
my dbase is for student administration;
if you pick a course, automatically the dbase assingsn the corresponding subjects;
But some people swap a subject outside the course-list; I can solve this with a lookup-field in tblSubjects - but that is against the commandments (for a good reason). How can avoid the lookupfield?

tblStudent
StudentID
StudentName
CourseID
m-1
tblCourse
CourseID
Course

1-m
tblSubj
subjID
subj (lookup-field)
CourseID



Pampers.

You're never too young to learn



Pampers.

You're never too young to learn

 

[earthandfire]

Looking at your original post could you not have a structure something like:

tblStudent:
StudentID
StudentName
etc.

tblSubject:
SubjectID
SubjectName
etc.

tblStudy:
StudentID
SubjectID
SubjectCancelled [yes/no]
etc.

This way each student could study any number of subjects and could easily change subjects.


Hope this helps.

 

[earthandfire]

sorry, I forgot to cross reference your original post for anyone else reading this one thread701-1111004

 

[earthandfire]

I wont have chance tonight, but I'll try to put something simple together tomorrow. A little bit of VBA behind a button should be able to auto-populate the default set of subjects for any given course.

 

[Pampers]

that would be great earhthandfire!
i'm gonna crash myself too;
enough dbases for today



Pampers.

You're never too young to learn

 

[Pampers]

hi there,
I thougt it worked with the lookup field in the tbl Subjects, but it doesn't. Sorry


Pampers.

You're never too young to learn

 

[earthandfire]

Resource Tables -

tblSubject:
SubjectID
SubjectName
etc.

tblCourse:
CourseID
CourseName
etc.

tblCourseSubjects:
CourseID
SubjectID

tblStudent:
StudentID
StudentName
etc.

=========================

tblStudy:
StudentID
SubjectID
SubjectCancelled [yes/no]
etc.

If you need to keep track of the Courses as well as the Subjects then ...
tblStudentCourse:
StudentID
CourseID

=========================

In tblStudy and tblStudentCourse you may want to give some thought to the Primary Key -
Could a particular student study the same subject more than once?
Could a particular student enrol on a particular course more than once?

To populate the subject/course tables:
Populate tblSubject
Populate tblCourse
For each course in tblCourse add subject

=========================

I've currently got a problem with Access - it wont run!! so I can't test anything but ...
You can now create an Append query (probably using the QBE) to add records to tblStudy to add -
StudentID, SubjectID
for each Subject in CourseID

=========================

In your form, you could have a button, btnSaveCourseDetails, which would ...

DoCmd.SetWarnings False
DoCmd.RunQuery AppenQueryDetailedAbove
DoCmd.SetWarnings True

obviously you would have needed to save the sudent first


By the way, your post between mine of 20 Aug 05 20:33 and 20 Aug 05 21:14 has disappeared.

Presumably someone decided to RedFlag it for reasons best known to themselves - but it does mess up the flow of conversion without it.

It takes all sorts

Hope this helps.

 

[Pampers]

hi earth and fire,
tnx for the reply
indeed, those messages are gone ??

i see what you mean by your last post. Actually, my first post at the Queries-forum started with the question to use an append query (append query mmmmh).

i think this is a good solution. Haven't got it yet. But I'm working on it. Hopefully you get your ACCESS is in the air again. I keep you posted on the outcome of the progress i make with this issue.


Pampers.

You're never too young to learn

 

[earthandfire]

Problem is that its on a very tired machine which keeps crashing. I'm just trying to find the time and patience to reformat and reinstall everything on it. I don't use Access on my main machine and I don't have enough licences to install on both.

 

[Pampers]

hi ahf,
well the append query is working, and puts the course related subject to tblStudy for a selected studentID. The code is:

INSERT INTO tblStudy ( StudentID, SubjectID )
SELECT tblStudent.StudentId, tblSubjectCourse.SubjectID
FROM tblStudent INNER JOIN tblSubjectCourse ON tblStudent.CourseID = tblSubjectCourse.CourseID
WHERE (((tblStudent.StudentId)=[give studentID]))
ORDER BY tblStudent.StudentId;

Next step i want to take is to run the append_query on a form with a commandbutton, for the current StudentID. Something with Me.Filter?



Pampers.

You're never too young to learn

 

[Pampers]

Hi ahf,
I think it is a good thing to consider the original problem (append query mmmmhh & avoid lookup field) is solved by using an append query and close this thread. If I cant solve the run appendquery for current SutdentID, i will post it as a new thread.
Tnxs very much for putting me on the right track and spending time on my issue;
Is your computer running yet?


Pampers.

You're never too young to learn

 

[earthandfire]

We have a Bank Holiday weekend this coming weekend (in the UK), so I'll be setting it up then.

 

[Pampers]

don't crash it!! haha


Pampers.

You're never too young to learn