Solved

Access DB Table Design Question

Posted on 2013-02-05
6
410 Views
Last Modified: 2013-02-05
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
Comment
Question by:tpigielski
  • 3
  • 2
6 Comments
 
LVL 84
ID: 38855432
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
 

Author Comment

by:tpigielski
ID: 38855512
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38855601
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:tpigielski
ID: 38855743
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
 

Author Comment

by:tpigielski
ID: 38855767
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
 
LVL 32

Expert Comment

by:Paul Sauvé
ID: 38855897
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question