You are here:

Using MS Access/Access 2013 - Form & Table showing wrong column

Advertisement


Question
Hello - hoping you're able to assist here.  I have 2 questions that seem to be along the same thread.

Let me give you the background on the DB so my questions make sense.  I have created 2 tables - Coverage and CovType.  Within Coverage, I have 4 fields called CovType1, CovType2, CovType3, and CovType4.  All four have a relationship to the CovType table which serves as a lookup table.  CovType table has 2 columns - ID and Acronym.

ISSUE 1: The Form

On the form that users fill out, I have a combo box for CovType1, CovType2, etc.  The field of Acronym is (correctly) showing up in the form combo box for the user to select.  What's not working is that it then stores that data in my Coverage table as the ID instead of the Acronym that the user selected.

ISSUE 2: The Report

I have a report that is supposed to display the data typed into the form.  Just like the table is only showing the TypeCov ID field, the form is doing the same and is showing the ID instead of Acronym.

Ultimately, I only want ID to be used as a primary key to link the data.  I don't want it to show up on anything.

Any help is appreciated - thank you!

Answer
Actually things are working exactly as they should. But you do have some problems with your table structure. Whenever you have fields like CovType1, CovType2, etc. you have what is called a repeating group. This violates Normalization rules. What you have here is a many to many relationship. A coverage can have multiple types and a Type can apply to multiple coverages. The proper way to model such a relationship is using a junction table:

tjxCoverageandType
CoverageandTypeID (Primary Key Autonumber)
CoverageID (Foreign Key)
CovTypeID (FK)

You should NOT accept just the default name for PKs, name them tablenameID as I illustrate. So you should not have fields in a table for Coverage Types but records in a child table. The Foreign Keys indicate which records on either side of the relationship they join to.

The way to enter this data is using a subform bound to the table above and linked to your main form on CoverageID. On that subform is a single Visible control; a combobox with the following relevant properties:

RowSource: SELECT CovTypeID, Acronym FROM CovTypes ORDEr BY Acronym;
Bound Column: 1
Column Count: 2
Column Widths: 0";1"

A combobox displays the first non zero width column, so the user sees the Acronym but the ID is what is stored. (this is clearly how your comboboxes are setup now). What you are missing is that your report needs to be bound to a query that joins all your tables. The query will include the Acronym column not the CovTypeID column so the Acronym can be displayed in the report.

Just to clarify you said; "I only want ID to be used as a primary key to link the data." And that is correct, but to include the Acronym is then redundant data. That's why queries are used as the source for reports so they can pull the data you want to see in the report from multiple tables.

Again, this is very much standard design.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.