Solved

Access DB Table Design Question

Posted on 2013-02-05
6
394 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 31

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

863 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

27 Experts available now in Live!

Get 1:1 Help Now