Unsure of which Join to use

[Core]

I want to create join between 2 tables.

One is a list of Assessors just ID and Names.

The second is a list of NVQs. I want the database to know which Assessors are qualified to assess which NVQs.

So for example when i got to Joe Bloggs' Record it will show the NVQs he can assess (example: CS Level 2, CS Level 3 and Retial Level 2). This would be done, I presume using a a form and subform.

Any sugestions.

Regards,

 

[Kryst51]

IS this a many to many relationship? Can more than one assessor be qualified to assess an NVQ? If so you will need a junction table that looks like this:

AssessorNVQID - Autonumber, PK
AssessorID - ID, number from Assessor table, FK
NVQID - ID, number from NVQ table, FK

This will then allow for the many to many, if that is not the case that is fine.

In the relationship screen (I suggest not using lookups at the table level, you can use them at the form level) build your relationships.

Then you can build a form with a subform, and if everything is set up correctly you should be all set to use the wizards to help you get it done easily.

 

[Core]

I understand what you mean and I have done as such, creating a 3rd table to create the join. I am just a little unsure how to create the form subform. For example, the form is using the Assessor Table and the subform using the NVQ table, but where does the 3rd table come into this?

IS this a many to many relationship? Can more than one assessor be qualified to assess an NVQ? If so you will need a junction table that looks like this:

AssessorNVQID - Autonumber, PK
AssessorID - ID, number from Assessor table, FK
NVQID - ID, number from NVQ table, FK

This will then allow for the many to many, if that is not the case that is fine.

In the relationship screen (I suggest not using lookups at the table level, you can use them at the form level) build your relationships.

Then you can build a form with a subform, and if everything is set up correctly you should be all set to use the wizards to help you get it done easily.

 

[Kryst51]

Actually, you might want to build a query that the subform pulls from then base your subform off of that, have the query include info from the junction table and your NVQ table. Include all the fields you want on your subform, then use the subform wizard to build your subform based off the query.