SQL query and possible relation

[Ryunosuke]

i have a problem about the DB relation. for information, i'm using MS Access.
The problem is, how can i refer the foreign key to another primary key? let me
show it clearly. I have 2 tables in one DB. I name it as a tbl_modul and
tbl_dftr_mod.

The contents in these tables:

tbl_modul
id | code | subject |
-----------------------------------------------
1 A1001 Math
2 A1002 Science
3 A3300 Biology

tbl_dftr_mod
id_std | code1 | code2 | code3 -->> code15
-------------------------------------------------------------
F102 A1001 A1002
F103 A3300

[mxstu]

I think a better structure would be to use three tables: "module", "student"
and "studentModule". The the third table "studentModule" would contain the
modules (zero or more) for each student. Note - that the foreign keys in this
table are the numeric student and module record Id's not their string "codes"


MODULE
moduleId | code | subject |
-----------------------------------------------
1 A1001 Math
2 A1002 Science
3 A3300 Biology


STUDENT
studentId | code
-------------------------------------------------------------
1 F102
2 F103

STUDENTMODULE
id | studentId | moduleId
-------------------------------------------------------------
1 | 1 | 1 <!--- Student F102, A1001 Math
1 | 1 | 2 <!--- Student F102, A1002 Science
1 | 2 | 3 <!--- Student F103, A3300 Biology

With this structure you can easily query the three tables to retrieve the
information you need and can use the GROUP attribute of CFOUTPUT to format the
output as desired.