Making a table with many fields.

[Guest_imported]

I am trying to make an Access database of patients' families' medical histories. With all the diseases and family members I want to enter for each patient, it is far more than 255 fields. So what is the best solution to "fake" a bigger table?
I tried creating a seperate table for each disease and entering the status of the disease in each family member in that table and include the patient ID # as the primary key. I created a relationship between the Patient ID # field in that table with the Patient ID # field in the main table, but it still does not seem to work.
If I try to make a form using the wizard with fields from both tables, I get a general "The wizard could not make the form" error. If I try it in design view, I cannot figue out how to access the field list of any other table except foe the one that I initially chose after clicking on the new form button.
By the way, I am using Access 2000 if that makes a difference. I hope I was clear enough to be helped. Thanks for any tips.

 

[Guest_imported]

Maybe I was not clear enough for someone to help me? My problem seems to be that I cannot figure out how to access the fields from related tables in form design view. I can select one table from the pull-down menu when I click the new form button, but I cannot get fields from any other related table to drag onto the form. Any help would be greatly appreciated, please.

 

[Robway]

I'm a bit of a novice at this too, so you may get better advice from someone else, but...

With regard to tables, I'd have one for the patients and another for diseases. There'd be a many/many relationship between them, so put a link table between them which will show which patient/s have which disease/s.

Are you basing your form on a query? I would make a query with all the fields I want for the form and use that as the data source for the form.

Read up on normalisation - it will help you to keep your tables under control.

Cheers,

Robyn

 

[LittleSmudge]

First off Quasius Normalise your data.

At a guess you are looking at :-
a Family table containing generic family details
a Members table that will hold one record for each person and a Foreigh Key to the Family table to identify which Family that Person is a member off.
a Disease table with a Foreign Key pointing to the Members table.

Eg.

tblFamily          tblMember
FamilyId . . |     MemberId. . . |
FamilyName   |. . .FamilyRef     |      tblDisease
Address1           FirstName     |      DiseaseId
Address2           DateOfBirth   | . . .MemberRef
Address3           Gender               DiseaseName
Town               InsideLegLength      DStatus
County             etc ..               DType
PostCode                                etc ..
etc..

Also, see the FAQ

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=78740754&CFTOKEN=15215991

about avoiding b|@5^£d space characters ( and other fancy characters like # ) in object names.



'ope-that-'elps.

G LS

 

[Guest_imported]

Alright, thanks for all the tips. I am getting a better idea of how to structure the table, but I still cannot figure out how to access other, related tables when building a form. I can only get the fields list of the table I initially select when I click on the "new form" button.
Thanks again for any help.

 

[Robway]

Seriously, don't base your form on a table. Base it on a query. Build your query from multiple tables, then all the fields you select for your query, from each table, will show up in the field list of your form.

 

[Guest_imported]

If you base your form on a query as suggested, only that data is retrieved, instead of the entire table...
Once you have the relationships established, make a SubForm first, containing ALL the detail records you want for each parent: ALL Diseases for each Family, or All Families for each Disease...just make a simple form, and drag the fields you want from the ALL (many) side, including the linking key field onto your form and make ViewsAllowed 'datasheet'.
You can make the linking key field Visible=False (it's always the same as the parent anyway) and then save the form as 'FamilyDiseaseSubForm' or whatever.
Then make a new form using the parent (Families) table and insert that subform onto it. Whenever the parent record changes, the detail list will keep up. If you make the subform editable, you'll need something in the 'BeforeInsert' event on the SubForm that will put the parent's linking id into the child record automatically: [ParentLinkingId] = Forms![Parent]![Parentid].
.d

 

[Guest_imported]

If you base your form on a query as suggested, only that data is retrieved, instead of the entire table...
Once you have the relationships established, make a SubForm first, containing ALL the detail records you want for each parent: ALL Diseases for each Family, or All Families for each Disease...just make a simple form, and drag the fields you want from the ALL (many) side, including the linking key field onto your form and make ViewsAllowed 'datasheet'.
You can make the linking key field Visible=False (it's always the same as the parent anyway) and then save the form as 'FamilyDiseaseSubForm' or whatever.
Then make a new form using the parent (Families) table and insert that subform onto it. Whenever the parent record changes, the detail list will keep up. If you make the subform editable, you'll need something in the 'BeforeInsert' event on the SubForm that will put the parent's linking id into the child record automatically: [ParentLinkingId] = Forms![Parent]![Parentid].
.d