Solved

Access DB Table Design Question

Posted on 2013-02-05
6
378 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:tpigielski
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Paul Sauvé
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now