A newbie question about a large table

[Quasius]

I am trying to build a database that ends up having well over 255 fields for each unique "ID #" (the primary key). I know Access won't let you build a table that large. I am sure the solution lies in making multiple, related tables; but I am stuck.
I have made another table that uses the same "ID #" field as its primary key as the main table does. It then contains several fields that would not fit in the main table. I then set the relationship link between the two "ID #" (primary key) fields in both tables.
But now I need to build a form to enter all the field data. My problem is that I cannot figure out how to place fields from the other table in form design view. When I click the new form button, I can choose whatever table I want to appear in the "view fields" box from the pull-down menu; but once chosen, I cannot change it to access any of the other, related fields to drag and drop onto the form.
If anyone could tell me how to do this, it would help a lot. Or if I am barking up the wrong tree, could someone point me to the correct solution? I am using Access 2000. Thanks for any help.

 

[Hellfire]

Hi,

I am not quite sure if this is the answer that you are looking for, but what you could do is:

Create a new form in design view, add your own text boxes.
Go into the properties of the text box (still in design view) select the DATA tab, and under the Control source, select the ... thing on the side, select tables, select the table where the field you are looking for can be found, and then select the field in the middle collumn, click OK. Any information entered into this text box, will be entered in the field you have chosen, on the specific table.

This should do the trick, I am fairly new at Access as well, so there might be a shorter and easier way, but this method will 100% work. Just a lot of effort though!!

Good luck, Let me know if you got it fixed.

 

[Alexandre]

255 fileds in a table most certainly means that you did not normalize your structure properly, but probably directly adapted your table from a spreadsheet...

Let us know more on what is your DB about and the tables and fields you have.

It is very important that before anything else you do some reading and understand the principles of designing a good relational database structure. Following are references that may help:
(Originally posted by Pat Hartman)

Here's a bunch of references from the Microsoft site:
ID: Q164172, "Understanding Relational Database Design Available on MSL" http://support.microsoft.com/support/kb/articles/q164/1/72.asp
ID: Q175939, "White Paper: Avoiding the Top Ten Design Mistakes" http://support.microsoft.com/support/kb/articles/q175/9/39.asp
ID: Q100139, "Database Normalization Basics" http://support.microsoft.com/support/kb/articles/q100/1/39.asp
ID: Q234208, "Database Normalization Basics" http://support.microsoft.com/support/kb/articles/q234/2/08.asp
ID: Q234208 ACC2000: "Understanding Relational Database Design" Document Available in Download Center http://support.microsoft.com/support/kb/articles/Q234/2/08.asp
ID: Q209534 ACC2000: Database Normalization Basics http://support.microsoft.com/support/kb/articles/Q209/5/34.asp
ID: Q162064 Microsoft Access 97 Articles Available by E-Mail: Tables and Database Design http://support.microsoft.com/support/kb/articles/Q162/0/64.asp
ID: Q140636 ACC: How the Table Analyzer Wizard Works http://support.microsoft.com/support/kb/articles/Q140/6/36.asp
About Designing a Database http://msdn.microsoft.com/library/officedev/off2000/acconDesignDatabase.htm

I can also recommend the book "Access Database Design & Programming", Steven Roman, O'Reilly & Associates, Inc.

And of course, Pat's own book... When it ships to sales...

Also, a couple of articles helping to understand the transition between spreadsheets and relational databases can be found at:
http://www.fmsinc.com/

 

[Freddy]

I had a similar problem using a poorly designed legacy database. It seems the programmer decided to split every possible element - SSNs (xxx-yy-zzzz), dates (mm,dd,yy) and times (hh,mm,ss) were all stored separately.

I created a query to join the two tables, then recombined the fields. The users didn't need some of the other fields, so the net was well under 255. I then created a form linked to the query.

I don't know if access will handle a query with 255+ fields. If it doesn't work, try making two queries/forms.

 

[Quasius]

Create a new form in design view, add your own text boxes.
Go into the properties of the text box (still in design view) select the DATA tab, and under the Control source, select the ... thing on the side, select tables, select the table where the field you are looking for can be found, and then select the field in the middle collumn, click OK. Any information entered into this text box, will be entered in the field you have chosen, on the specific table.

I had actually tried this before and when I tried it again, I got the same result. When I try to enter text into that field that i have done your suggestion to in form view, I get the "error ding" and a message at the bottom that says "Control can't be edited; it's bound to the expression '[Second]![State]'." (I named the other table and field second and state respectively for testing purposes.)

255 fileds in a table most certainly means that you did not normalize your structure properly, but probably directly adapted your table from a spreadsheet...

Let us know more on what is your DB about and the tables and fields you have.

It is actually not adatped from a spreadsheet. The database is to track medical information about many patients in a lab study. What is taking up the most room is that I am keeping track of the occurance of several diseases (17 total) in family members of the patient (10 family members for each patient). That is 170 fields right there and that does not leave enough room for the other information about the patient specifically.
So there is definitaly not "repeated data," as many normalization articles warn of. What I tried to do was make a seperate table for the family disease history. This second table also contained a field with the same Patient ID # that is in the main table. This Patient ID # is the primary key in both tabels. I linked the Patient ID # fields in both tables in relationships. After doing that, I ran into the problems given above. (I cannot access the fields from the other table in text boxes on a form.)

I had a similar problem using a poorly designed legacy database. It seems the programmer decided to split every possible element - SSNs (xxx-yy-zzzz), dates (mm,dd,yy) and times (hh,mm,ss) were all stored separately.

I don't think I have that problem. Each of my fields is well-used (as far as I can tell). I just have a lot of data that requires (as I understand it) a lot of fields.
Thanks for the tip.
I hope I gave enough information to be helped further and thank you people so much for your help.

 

[Alexandre]

From what you described it sounds that you should have a separate table for diseases, and not store diseases related info in the patients table (these are different topics!).

You should not either have extra fields for relatives (again, it's different topic) in your patients table. Here your approach may be a different table, or to use the same table for both patients and their relatives adding a flag field (IsPatient, boolean). The latter approach may give more flexibility (a relative may become a patient at a certain moment). I guess the decision here would depend much on whether you need to gather essentially similar info about patients and relatives or not. That approach would require linking the patient table to itself on the PatientID field:

TblPatients
-PatientID (Autonum PK)
-Field1
-Fiel2
...

One to Many with:

TblPatientsAndRelatives
-PatientID (Long int foreign key linked to PatientID)
-RelativeID (Long int foreign key linked to PatientID also)
(PatientID and RelativeID make a two-fields PK for this table)

You then open a second instance of the TblPatients in your relational DB diagram, give it an Alias (TblRelatives), and link it (One side) to the TblPatientsAndRelatives (Many) side on the RelativeID field.

If you provide more details about the info involved regarding patients and relatives, you may get more accurate structure suggestions from people on this forum.

 

[Quasius]

If you provide more details about the info involved regarding patients and relative, you may get more accurate stucture suggestions from people on this forum.

Alright, I will try to be more specific. I have several patients. The patient is the only person I have any personal information on (name, address, etc.) (So even if a relative would become a patient later, it would not matter.)
For confidentiality reasons, the name of the patient and other identifiers must be stored in a seperate, password-protected table or database.
Part of the information I have about each patient is their familie's disease history. An example would be cancer. I would then have a field for all 4 grandparents and the 2 parents. Each field would be answered with "Yes," "No," or "Don't Know" (a combo box). Then 4 fields asking the number of affected brothers, sisters, sons, and daughters. That comes out to 10 fields per disease. I hope I was clearer this time...
I am getting a better idea of how to arrage the table, but I still cannot figue out how to access the fields of related tables in building a form...
Thanks again.

 

[Alexandre]

What you describe would require 6 tables:

TblDiseases
-DiseaseID (autonumber PK)
-DiseaseName
..?

TblDiseasesPerRelatives
-DiseaseID (long num foreign key from TblDiseases)
-RelativeID (long num foreign key from TblRelatives)
-HasDisease (boolean two states: Yes/No)

TblRelativesAffectedFamily
-RelativesAffectedFamilyID (Autonumber PK)
-StatusID (long num foreign key from TblRelativesStatus)
-NbAffected
..?

TblRelativesStatus
-StatusID (autonumber PK)
-StatusDescription (Maternal Grand-Father, Brother, Brother, Daughter, etc)


TblRelatives
-RelativeID (autonumber PK)
-StatusID (long num foreign key from TblRelativesStatus)
-RelativeFirstName
-RelativeLastName
...?


TblRelativesPerPatients
-RelativeID (long num foreign key from TblRelatives)
-PatientID (long num foreign key from TblPatients)
...?
Here the PK is made of both RelativeID and PatientID fields


Again, before going ahead with that, you should go through some of the suggested reading. Then, maybe you will find improvements to bring to this structure to better fit your needs

Edit: Thinking better about it I believe I would consider using directly the TblRelatives and TblDiseasesPerRelatives to also store info about brothers, sisters, daughters, and sons affected for each disease, thus getting rid of the TblRelativesAffectedFamily table and simplifying your structure.
Yeah, I definitely think it is much better

BTW, it is not worth doing any work on forms before you have a sound structure in hands. Your pbs figuring how to organize your forms directly derive from your underlying structure pbs