Linking 2 tables

[davecarson]

I have reached the maxium fields for a table but still need to add more. How do I link the tables togather so one is a continuation of the other for reports.

Thanks

 

[HitechUser]

davecarson,

Both tables will need a common "key". Select a field(s) in both tables. Create a join (on these fields) using the query wizard. Use the new query as the record source for your report.

I hope this helps..

Good Luck...

 

[willir]

Red alert, red alert, red alert....

When I hear ...

I have reached the maxium fields for a table

... I immediately ask my self is the database normalized??

This question is asked fairly frequently in this forum where a person asks how they can create more than the 255 fields allowed in a table design.

The most common best answer is -- don't.

It is your database, but you may be setting your self up for a lot of "hurt" later on.

Example:
A shift supervisor sets up a database for tracking regular and over time work, and wants to track the day of the week the OT occurred on.

Perhap a design would be ...
tblHoursWorked
EmployeeID
EmployeeName
EmployeePhone
EmployeePosition
WeekOfYear
StartOfWeekDate
MondayRegularHrs
MondayOTHrs
TuesdayRegularHrs
TuesdayOTHrs
etc...


One big honking table to track everything.

There are several problems with this...
- Every week, you have to re-enter the employee info
- You have to enter numerous amount of info for regular and OT hours, etc.

There are over issues too.
- This table is a nightmare to maintain. Suppose you need to include the CellPhone number. Now you have to edit the table design, edit all the affected forms, queries and reports -- a lot of overhead for a simple change.
- Your queries become extremely complicated -- 100's or 1000,s of characters long. Your WHERE clauses become incredibly long.
- Displaying data as useful information becomes very tough. Because of the maintenance issues mentioned, the chance of errors can be high.

...Oh yea, a common issue where this "not enough fields" problem crops up is when some one designs a "Survey" database or similar where each field is a question.

The whole idea on using a relational database is to capitalize on the power of relations -- enter an employe name once, track only info required, simplified queries, ease of use to gather data and present accurate and useful information.

There is some standard set of documents to read on Normalization and Relationships...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read document on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Yet another source...
Introduction to Relational Databases - Part 1: Theoretical Foundation (15 seconds)

Basically, Normalization is a well researched and developed concept with proven results.

Good luck
Richard

 

[HitechUser]

I second what Richard said. What exactly are the fields in this table? Personal curiousity!.

 

[davecarson]

I have a question/Answer type of data base. One field is a yes/no/na check box and the other is notes fieled. There are over 300 questions and 300 note fileds. This is for an audit compliance review.

 

[lespaul]

So the answer to the question appears to be, no the database is not normalized!



Leslie

 

[davecarson]

Is there a better way to handle this type of Data?

 

[lespaul]

check out the links provided above for a general understanding of normalization. You can also search this Access Forum, Forum701, Forum181 for 'survey database', one of the MVPs (dhookem) has several postings in threads referencing a normalized survey database on his website.

Leslie

 

[willir]

There is also this post...
Table exceeding 255 fields

 

[davecarson]

I only have 2 fields for each question, a field to hold the check box field for a yes/no/na answer and a field to hold a note to each question. it sounds like this is a normalized data base to me. The problem is the number of question that there are.

 

[willir]

...2 fields for each question...check box...a note to each question...

Okay, how do you "store" the question? Is this what you mean by "linking"? Is it that you are not sure how to link the "question" to the "answer" or response?

To answer this, "we" need to know how you store the question. And then, how will you store the information for each survey response?

And...

I have reached the maxium fields for a table...

When I see this type of post along with your last comment, I have to ask if it is possible that you may confusing "fields" and "records"?

A field is part of the table design. A record is data stored within the table per the table design. Using a Contact database as an example...

tblContact - table name
Following are names of fields or columns
ContactID - primary key, long number, autonumber
ContactLN - name, text string, 25 characters
ContactFN - name, text string, 15 characters
...etc

The records in the table would be...
[tt]
ContactID ContactLN ContactFN
1 Smith John
2 Lee Mary
3 Johnson Bob
[/tt]

I used a Contact database as an example since it is fairly standard and a survey database can vary in design. And I appologize for the rambling if you know what fields and records are -- in this type of forum, we have to be sure.

...Moving on
The biggest issue with a survey using Access (or any database since they all have limits on the number of fields) is that the most typical approach, inlcuding examples posted on the web, is that they use a field for each question (and usually offered answers - A B C D Yes No), and assume that they will not run into the 255 field limit for the table design.

Richard

 

[davecarson]

Ok the questions them selfs are stored as lables on the form it's self.
Then there is a field for the yes/no/na checkbox and a field to store the answer(note) for each question.

 

[willir]

Hmmm.
By lables, do you mean you add the "lable" with the question manually to the form, or are the "lables" created automatically when you use the wizard to create the form?

Regardless, a table is limited to 255 fields or columns, and a form is limited to 255 objects.

I guess you must find this really frustrating. I understand. But I go back to my original comment in that you may want to re-think your design.

Using "labels" for your questions is problematic in that it seems you run up against a "resource" wall / application limit, and this method of approach is very problematic in terms of maintenance. When ever you have to add a question or change or delete a question, you have edit the form and perhaps the tables and queries and reports. Using a normalized approach, you would treat the question as a record on a table, the possible answers as a record(s) on a table and the answers as records on a separate table.

I realize you have busy on trying to trouble shoot thsi, but did you get a chance to read my link
Table exceeding 255 fields
...or down load Duane Hoolom sample database
At Your Survey 2000 (354 KB) Access 2000

Richard

 

[lespaul]

can you give us some sample data along with the field names for your table?

leslie

 

[BNPMike]

Your database is normalised. It's just an inadvisable design. Relational databases are much better at adding rows than columns, so represent your critical cardinality as rows.

This leaves you with

(QuestionId
QuestionText
Answer
Notes)

Display the question-set as a grid in a subform.