Link table fields

[Saylindara]

I'm using Access 2003. First database design.
Database is for courses employees go on. So each course will have many
employees and each employee will go on many courses. I have a table of
employees and a table of courses. I also have a link table with 2 primary
keys - employeeID and courseID. In the info I got from a course I did it says
I can add fields to the link table. I'm trying to add a Mandatory yes/no
field and Notes field to the link table but when I add those fields to the
course subform it won't let me enter anything. Where am I going wrong?

 

[tina]

first of all, are you adding the fields to the linking table, and *then*
adding them to the RecordSource of the subform? if not, do that.

second, if it still doesn't work after above, please post the Recordsources
of both the main form AND the subform, so we can see them.

hth

 

[Saylindara]

Sorry, what is a RecordSource? I created 2 main forms - one employees, one
courses
I created 2 queries - link table and employees, link table and courses
I created 2 subforms based on the queries - courses subform for the
employees form and employees subtable for the courses form. I hoped to use
the 2 fields from the link table (Mandatory yes/no) and Notes on the courses
subform (main form employees). Sorry to be so dim.

 

[Ron2006]

The tables contain the data/information.

The forms (using the queries) allow you to see/add/change the data in
the tables.

So add the fields to the link table if it is relevent to the link
between the specific employee and that specific course, for instance
the date on which the course was taken or ended or started, or
registered, or as many events as you need to monitor.

Add to the course table if it is relevent to the course - the teacher
maybe or classroom or whatever.

Add to the employee table if it is relevent to the employee alone -
department, phone number, manager or whatever.

Then change the queries to include those needed fields for each of the
particular forms.

From that point we all need a little more information to give you some
more specific suggestions.

Ron

 

[Saylindara]

I've got:
Tblemployee
EmployeeID
Surname
FirstName
Profession
Team
Specialty

TblCourse
CourseID
CourseName
CourseType
CourseDate

TblLink
EmployeeID
CourseID
Mandatory (Yes/No)
Notes

I like the idea of having the course date in the link table so I will
change that.
The profession, team and course names are accessed via a look-up table.
The thing is I made the employee form and subtable but the mandatory and
notes fields would not let me enter any data.

Thanks so much for your help.

 

[tina]

Sorry, what is a RecordSource?

a form's RecordSource is, literally, the "source of the records that are
displayed when you open the form".

you need to set the relationships between your tables, in the Relationships
window, first. then get rid of the queries you're basing the subforms on.
instead, use the link table as the RecordSource of both subforms. in the
employees main form, set the subform control's LinkMasterFields property to
EmployeeID, and the LinkChildFields property to EmployeeID. in the courses
main form, set the subform control's LinkMasterFields property to CourseID,
and the LinkChildFields property to CourseID.

i have a feeling the above will be a lot of greek to you, since you're new
at developing databases in Access. that's okay, we were all there once. but
to understand what you're doing, and to understand the instructions and
suggestions you get here and in other newsgroups, etc, you'll need some
familiarity with the basics of the software, and a grasp of relational
design principles. for more information, see
http://home.att.net/~california.db/tips.html.

hth

 

[Saylindara]

Thank you I'll try that and thanks for the link. I was hoping to put the
structure in place this week as I'm then off on holiday for 2 weeks but I
think I've got too much to learn.

 

[tina]

you're welcome, and good luck with it. Access does have a steep learning
curve, but you may find that it can be a fascinating challenge, and loads of
fun - at least that's how it turned out for me!

 

[Saylindara]

I knew I didn't know much but I've learnt I know even less than I thought.
I'm scrapping what I've done and will start again. The link you gave me has
been really helpful. You're right I will find it a fascinating challenge.
Thanks again.