	AllExperts > Using MS Access
	Search

About 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 15 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

You are here: Experts > Computing/Technology > Business Software > Using MS Access > make table & checkbox controls

Using MS Access - make table & checkbox controls

Expert: Scottgem - 11/6/2009

Question

Example.png

I have a contract database that lists contract numbers, sections, and divisions (checkboxes). A distinct contract number can have multiple sections that apply to different divisions. I want to make a temporary table from this data that has one row per contract number with all applicable checkboxes on that one row. Please see the attached image of the datasheet view of the table.

Answer
First, your database is not normalized properly. By having fields for each division number you have created a repeating group which violates normalization rules.

You should have 2 tables here. One for contracts and one for contract divisions:

Example:
tblContractDivision
ContractID (FK)
DivisionID (FK)

You then join that table to your contracts table on ContractID. Then use that query as the sopurce of a crosstab query. to get your listing.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Add to this Answer Ask a Question