Multiple entries in a field

[demax182]

HELLO motivated ACCESS programmers!

I'm not sure if this can be done, but can multiple entries be entered in a single field. Here's the scenario, a table/form that keeps track of roofing contracts. However, while many contracts correspond to fixing only the roof on one building, there are also cases where one contract fixes roofs of several buildings. This leads to a query nightmare! As always, thank you for your much appreciated help.

 

[PHV]

The usual way to handle a many-to-many (m:n) relationship is to play with a 3rd table having 2 ForeignKeys.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[demax182]

PHV,

Thank you for the reply, but I don't know what you mean by foreign keys. Here's a more detailed explanation of my situation, any help with designing my database is greatly appreciated. I have tblBuilding which contains building data (Building#, Size, Age, etc...). I have tblContract which contains contract info (Contract#, Building#, Cost, Contractor Name, description, etc...). Here lies my problem, Contracts often pertain only to one building, but there are often cases where a single contract may pertain to more than one building. I can only enter one building# in tblContract, but if there's more than one, I can't input something like "1,6,9,15,20". Really messes up the query capabilities of my database. How would you go about designing this database? Anybody?! Thanks.

 

[Eupher]

Hi, demax182,

What PHV is recommending is a normalized database structure in which you have your tables related via a primary and foreign key (hence the term "relational database"). Understanding primary and foreign keys is an essential element of database design.

A classic business example employs a cascading series of one-to-many relationships: a salesperson may have many customers; each customer may have many orders; each order may have many products. So the database designer then might create a Salesperson table, which would include, among other items, a field to hold a unique identifier for each salesperson - this would be designated as the primary key. There would then need to be a Customers table, with a field uniquely identifying each customer - this would be the primary key in *this* table. However, the Customers table would also have a field for the Salesperson ID, which would correspond directly to the primary key in the Salesperson table. This field is known as the foreign key. Then a relationship can be created between the 2 tables, referential integrity can be enforced, updates and deletions can automatically be cascaded, etc.

There may be times when one would need to have a relationship between 2 tables that have no common field, where there is a many-to-many relationship. This is where PHV's comment comes in. In this case, you would create an intermediary table (a "junction" table) containing 2 foreign keys, one for each of the 2 tables that need to be related.

Take a look at the Northwinds sample DB, also do a search in the Help files for "relationships" and "primary key". Also, here are some links you might find helpful:

http://www.4guysfromrolla.com/webtech/042699-1.shtml

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

HTH

Ken S.

 

[willir]

Using one field to capture multiple types of data because much more difficult to maintain, analyze and produce more usefull information.

PHV is quite correct. Instead of adopting a work-around solution, you can use relationships to better present your data.

Here is some back ground material.
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)

Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

And a really good article on queries
Harnessing the Power of Updatable Queries

...Moving on
For example, you may be able to use three tables to capture this inform regardless of a contract for one or many houses.

tblContract
ContractID - primary key
ContractOwner
BillingAddress
ScopeOfWork - memo
...etc (Terms, Start / End Date, etc)

tblBuilding
BuildingID - primary key
ContactID - foreign key to tblContract
BuildingAddress
ScopeOfWork - meno
...etc

tblContractDetail
ContractDetailID - primary key
ContractID - foreign key to tblContract
BuildingID - foreign key to tblBuilding
DetailDesc - text
Completed - boolean yes/no
Comments - memo

The way it works...
- tblContract captures basic info for contract and other all scope
- tblBuilding captures info on each building and scope of work for the building if different from tblContract
- tblContractDetail itemizes deliverables for the contract and can be used as a check list

You probably want to include the price in the itemized list.

There is one weakness in this design. If a building has work done on it more than once, the address info etc will be duplicated. I suspect this is a non-issue since it is very unlikely you will work on a house more than once for the life of the database (correct?? 20 to 30 year shingles...)

Richard