Solved

Access DB Table Design Question

Posted on 2013-02-05
6
415 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

713 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