|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Access Database SetupOkay, I was trying to design a single table but ran into that pesky 255
column limit, so now I have multiple tables with multiple forms used for entering the data. I have one form that has fields for Individual ID#, Burial ID#, Cultural Affiliation (so on). These fields are also located in the corresponding table. This particular information is the same in both tables (one for dentition and the other from cranial traits). What I am attempting to do is have the data from one table propagate fields in another table. Is this even possible? Sounds as if you have a design problem. You really need to look at the
structure of your tables as the first step towards getting a database that will work for you. First step is to create one table that has the person information in it - just demographic information. Persons: PersonID BurialID CulturalAffiliation DOB So you would just store the PersonId in the CranialTraits database. That table would probably have three fields in it and would have one record for each cranial trait you were recording for the person. CranialTraits: PersonID TraitType TraitValue You would probably have another table listing all the Cranial Trait Types so it would be easy to consistently enter TraitType in CrainialTraits. Assuming the Dentition table, you would have something like the following to record information on each tooth (and again you might have a lookup table to ensure that ToothID was consistently entered). PersonDentalTraits: PersonID ToothID ToothSize Cavity Using a main form for the person's information and subforms to enter the trait information would take care of setting up the links between the Persons table and the various traits. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Sarah C. wrote: Show quoteHide quote > Okay, I was trying to design a single table but ran into that pesky 255 > column limit, so now I have multiple tables with multiple forms used for > entering the data. I have one form that has fields for Individual ID#, > Burial ID#, Cultural Affiliation (so on). These fields are also located in > the corresponding table. This particular information is the same in both > tables (one for dentition and the other from cranial traits). What I am > attempting to do is have the data from one table propagate fields in another > table. Is this even possible? I must admit this whole process has me so confused! But, I am determined to
do this correctly. Here is the type of information I am recording: Persons Info (like you listed previously- master form) Tooth (right and left sides) Dental Trait Dentral Trait Degree of Expression Side of Greatest Expression Cranial Trait (right and left sides) Cranial Trait Degree of Expression Side of Greatest Expression I have created a single form with subforms for maxillary dentition data, mandibular dentition data, and cranial trait data. Now what is the next step? Show quoteHide quote "John Spencer MVP" wrote: > Sounds as if you have a design problem. You really need to look at the > structure of your tables as the first step towards getting a database that > will work for you. > > First step is to create one table that has the person information in it - just > demographic information. > > Persons: > PersonID > BurialID > CulturalAffiliation > DOB > > So you would just store the PersonId in the CranialTraits database. That > table would probably have three fields in it and would have one record for > each cranial trait you were recording for the person. > CranialTraits: > PersonID > TraitType > TraitValue > > You would probably have another table listing all the Cranial Trait Types so > it would be easy to consistently enter TraitType in CrainialTraits. > > Assuming the Dentition table, you would have something like the following to > record information on each tooth (and again you might have a lookup table to > ensure that ToothID was consistently entered). > > PersonDentalTraits: > PersonID > ToothID > ToothSize > Cavity > > Using a main form for the person's information and subforms to enter the trait > information would take care of setting up the links between the Persons table > and the various traits. > > John Spencer > Access MVP 2002-2005, 2007-2009 > The Hilltop Institute > University of Maryland Baltimore County > > Sarah C. wrote: > > Okay, I was trying to design a single table but ran into that pesky 255 > > column limit, so now I have multiple tables with multiple forms used for > > entering the data. I have one form that has fields for Individual ID#, > > Burial ID#, Cultural Affiliation (so on). These fields are also located in > > the corresponding table. This particular information is the same in both > > tables (one for dentition and the other from cranial traits). What I am > > attempting to do is have the data from one table propagate fields in another > > table. Is this even possible? > On Thu, 25 Jun 2009 13:09:01 -0700, Sarah C.
<Sar***@discussions.microsoft.com> wrote: Show quoteHide quote >I must admit this whole process has me so confused! But, I am determined to The next step is to STOP, reread John's good advice, take ten steps back, and>do this correctly. >Here is the type of information I am recording: >Persons Info (like you listed previously- master form) >Tooth (right and left sides) >Dental Trait >Dentral Trait Degree of Expression >Side of Greatest Expression >Cranial Trait (right and left sides) >Cranial Trait Degree of Expression >Side of Greatest Expression > >I have created a single form with subforms for maxillary dentition data, >mandibular dentition data, and cranial trait data. > >Now what is the next step? restructure your TABLES. Tables are fundamental. Forms are just tools to manage data in tables. If your table is incorrectly designed - AS IT IS - you will never be able to get a good form! "Fields are expensive, records are cheap". I take it you have a field in your table for (let's say) tooth 4, the left upper bicuspid, buccal surface. What you need instead is a table with *one record per tooth* - a field to identify the specimen from which this tooth came, and a *small* number of fields to identify features of that particular tooth. -- John W. Vinson [MVP] Hi Sarah,
Since you are "....... But, I am determined to do this correctly." here are some sited that might help you......... Read: "The Access Web" Ten Commandments http://www.mvps.org/access/tencommandments.htm Pay attention to #3. Also, don't use special charactures "(!@#$%^&*?/)" in object names. Go back and look at John Spencer's post and the field name examples. Here is a list of reserved words (aka 'Bad Words'). Do not use them as object names http://allenbrowne.com/AppIssueBadWord.html And here are a couple of Tutorials on Database Normalization: 1) http://www.agiledata.org/essays/dataNormalization.html 2) http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=95 3) http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/ Remember, Database table design is as much an Art as a science. Listen to the three guys that responded to you. They are MVPs for a reason. :) I have learned so much from them (and the others) just by reading their responses to questions.HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) Sarah
It sounds like you are trying to make Access act like a spreadsheet ... it isn't one. John & John have offered good advice. Turn off your PC, take up paper and pencil, and map out the entities and relationships before proceeding in Access. If normalization and relational database design are unfamiliar, study up before turning Access back on! Good luck! Regards Jeff Boyce Microsoft Office/Access MVP Show quoteHide quote "Sarah C." <Sarah C*@discussions.microsoft.com> wrote in message news:11CA056F-82EB-4287-90C5-56E4FF517B49@microsoft.com... > Okay, I was trying to design a single table but ran into that pesky 255 > column limit, so now I have multiple tables with multiple forms used for > entering the data. I have one form that has fields for Individual ID#, > Burial ID#, Cultural Affiliation (so on). These fields are also located > in > the corresponding table. This particular information is the same in both > tables (one for dentition and the other from cranial traits). What I am > attempting to do is have the data from one table propagate fields in > another > table. Is this even possible? On 25 June, 19:46, Sarah C. <Sarah C***@discussions.microsoft.com>
wrote: > Okay, I was trying to design a single table but ran into that pesky 255 You won't need so many columns.> column limit, so now I have multiple tables with multiple forms used for > entering the data. I have one form that has fields for Individual ID#, > Burial ID#, Cultural Affiliation (so on). These fields are also located in > the corresponding table. This particular information is the same in both > tables (one for dentition and the other from cranial traits). What I am > attempting to do is have the data from one table propagate fields in another > table. Is this even possible? Try to break data down into facts and create a seperate table for each fact. For example you would not combine invoice and address information into one table as they are seperate facts. Once you have broken the data down you can think about how it can relate together. For total MS Access mastery http://access-databases.com/ms-access-tutorial/  
Other interesting topics
|
|||||||||||||||||||||||
