Many-to-one relationship

[Jon Keelty]

I have developed a report-writing database for my school.

I have one table with individual student details and one with teaching Unit overviews.

For report writing I need to automatically populate a student field with data from the other table( or alternatively just display the related data field ) according to a specific code

e.g.

Student

1_WWE_code

1_WWE_description ( gets unit descriptiona ccording to code in previous field )

...

Unit

unit_ID_code ( unique )

unit_description ( to be shown according to the code in 1_WWE_code, etc. )

In the relationship view I have set up a many-to-one relationship between the 1_WWE_code... fields in "student" and the unit_ID_code in "unit"

I am a bit stumped at the moment - any suggestions would be much appreciated!

8 )

New development: I can get it working if I create a new instance of the table in the relationship diagram - but do I need to then create 36 instances to make it work ( 6 year groups, each with six different units ) aaargh...

Edited December 1, 2008 by Guest

[bcooney]

Wouldn't a student have many Units? Can't a Unit be assigned to more than one Student? If so, you need a third table that stores the combos. This is called a join table. Search for examples in the forum, there are many.

Also, learn about lookups.

[Jon Keelty]

Thanks for the reply... I will look into the join table and lookups - I think it will solve my dilemma nicely!

:B