Access Database Setup

[Sarah C.]

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?

 

[John Spencer MVP]

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.]

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?

 

[John W. Vinson]

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?

The next step is to STOP, reread John's good advice, take ten steps back, and
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.

 

[Jeff Boyce]

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

 

[Keith Wilby]

Contact me if you are interested.

But read this first: http://home.tiscali.nl/arracom/whoissteve.html

 

[Steve Sanford]

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

 

[troy23]

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?  

You won't need so many columns.
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/

 

[John... Visio MVP]

Hello Sarah,

I provide help with Access applications for a reasonable fee. I can help
you design your tables for a modest fee and save you the time spent trying
to sort things out. I would provide a map of the tables showing each table
you need and the relationships between the tables. The type of
relationship would be shown for each relationship. I would work closely
with you to design the tables. I could create the backend database
containing the tables if you wished. My fees would be very modest. Contact
me if you are interested.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP