[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Access DB Table Design Question

Hello,

I have a small database that has 3 tables:  Instructor, Certifications, Courses.

The Instructor to Certifications relationship is 1 --> M.  Courses table has Course no. and description as two of the attributes.  Certifications table just has the course no. as an attribute in the table.

I'm trying to set up a form based on Instructor, showing all of his/her certifications in a subform.  The Certifications table contains the course no. (same as in the Course table), but not the course description.  I want to pull that info out of the course table.  This is the way it should be for a normalized relationship.

Problem I have is that the subform is constructed based on a query (to get course no. and course description in the subform together), and as a result, I cannot ADD a certification to the instructor through this form / subform presentation.

Any suggestions on how to set this up would be appreciated.

Thanks...Tom
db3.mdb
0
tpigielski
Asked:
tpigielski
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How would you add a Certification?

Using your supplied database, if I open the Instructor form I can add new records in the Subform by entering a valid Course_No. This will add the record, and save it to the Certification table. That's what I would expect it to do.

Can you give more information about how you're using the database, and give use sample steps to reproduce your problem?
0
 
tpigielskiAuthor Commented:
Yes, I can add a certification to the subform, but what I really would like is to display the "description" of the certification on each line that has a certification (coming from the course table), and to fill in the course description (from the course table) when I add a new certification.  I realize this means the course description field in the subform should be set to NOT enabled.

Does this help?

Thx..Tom
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I added the Course_Description to the subform, and it displays on the subform. When I enter a new Course_No and tab out of the field, then the subform immediately displays the Course_Description.

Or perhaps I misunderstand what you're after ...

Course Image
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
tpigielskiAuthor Commented:
It appears that I can accomplish what I want to do by creating an "unbound" text field called "description" in the subform, and then just using the "dlookup" function to populate the field.  Is this the best way to do this?  When I do this however, the "new" row in the subform (which hasn't been added yet) has "#Error" in the description field.  Any way to get rid of that?

Thx..Tom
0
 
tpigielskiAuthor Commented:
Ok....seems like I was making this harder than I needed to.  I did as you did (adding the description field to the subform), and it does as you describe.

Not sure why I was making this more difficult..

Thanks again for your help.

Tom
0
 
Paul SauvéRetiredCommented:
If there is a 1:1 relationship between the Courses table (Course no. and description)  and the Certifications table (just the Course no.), then it is required?

Second point, can more than ONE Instructor have the SAME Certification? If so, the relationship Instructor to Certifications is M --> M rather than 1 --> M.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now