Newbie's incapable, probably really easy. (Long but pls read)

  • Thread starter Thread starter jules
  • Start date Start date
J

jules

Hello all,


I admit it - I am new to Access (or databases in general) and
none of this is really my field. I am using Access for my
doctorate in dentistry, so bear with me if my questions are
too simple... =8) Unfortunately I have reached a point where
the good ol' try-and-error procedure won't cut it anymore and
I do rely on some help for you guys - any info is greatly
appreciated.

Here's a quick rundown -

Basically I have 363 fields to take care of, 11 fields worth of
patient data and 11 attributes per tooth * 32 teeth in your average
jaw. I've found out the hard way that Access is retarded enough
only to handle 256 fields per table which really was the beginning
of my desperation: I therefore split everything up into five tables:
1. patient data (name, date of birth etc...)
2. quadrant 1 (upper right part of jaw)
3. quadrant 2 (upper left...)
4. quadrant 3 (lower left...)
5. quadrant 4 (lower right...)

The next problem was creating a form. While Access obviously is
capable of handling the total number of fields/gadgets/stings
in ONE form the subforms (quadrant...) did not work (the Auto ID
incicated "#Name?").
I split it up to five forms (as I did with the tables), no luck.
Changes were now possible but nothing was saved. The datasets did
not syncronise with each other.
I did find a very, very shaky method of achieving cohaerent
forms/datasets, but I would like to hear your opinions on how to
get to the following:

1. Open all fields in one form (is this possible despite the fact
that fields originate from other tables) and having them syncronise
in a way that if I select another patient from the main list, all
subforms refresh.
2. If that can't be done, how can I achive this in multiple forms?
3. How can I have the quadrant-forms open
a) with their top-left corner set to a specific coordinate
b) in defined size?


Any input greatly appreciated - and hey, if you have dental
questions... =8)

Thanks guys,


Julian
 
I'm not quite used to subforms but do you have Access2k or
later?
Then you should turn the autocorrect feature off

Also I would have assemble the teeth-divsion in one table
instead of creating a table for each quadrant.

Teeth:
Attribute 1
Attribute 2
..
Attribute 11
Quadrant 1-4
PatientID )candidate for primary key?
TeethNumber )
Child.... )

Patient
PatientID (Primary KEY)
...

Then you could create a subform based on the link
patientID between Teeth & Patient.
The whole process of splitting up in entities is named
normalisation
(see google) and makes database administration much easier.

Hope this helps.

PS:
In case you would need some reference to excellent Access-
sites

http://www.granite.ab.ca/access/corruptmdbs.htm
http://www.able-consulting.com/ado_conn.htm
http://www.mvps.org/access/api/api0001.htm

http://www.mvps.org/access/tables/tbl0009.htm
http://accdevel.tripod.com
http://members.rogers.com/douglas.j.steele/AccessReference
Errors.html
http://www.lebans.com/mousewheelonoff.htm
http://support.microsoft.com/?id=258049
http://www.mvps.org/access/general/gen0005.htm
http://www.trigeminal.com/usenet/usenet001.asp?1033
http://www.pksolutions.com

mailmerge
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html
http://appdevissues.tripod.com
 
Hi Julian,

As the man said, to get there you don't want to start from where you
are. Start by thinking about the real-world entities you are trying to
model. Basically, each entity gets a table of its own.

One entity is obviously the patient, so you need tblPatients that
contains a unique PatientID (simplest to use an autonumber) and the
fields that relate only to the whole patient.

The next is the individual tooth. tblTeeth needs fields for its 11
attributes - plus fields to indicate which patient it belongs to (i.e.
the PatientID) and whereabouts in the mouth it is (using whatever
standard system you're happy with).

There is a one-to-many relationship between Patients and Teeth: each
patient has zero to 32 teeth, each of will have its own record in
tblTeeth.

Doing it this way, your widest table will have fewer than 20 fields. The
structure also makes it easy to distinguish between milk teeth and adult
teeth (and can also handle anyone you might meet who has an extra pair
of teeth - which your current structure can't).

Those two tables are the main ones. It would probably also be a good
idea to create a third table called something like tblToothNames
containing perhaps one field

ToothName
Left Upper Molar 1 Adult
Left Upper Molar 2 Adult
or whatever description you prefer or are required to use. Its purpose
would be to constrain the possible values of the corresponding field in
tblTeeth.



A structure like this is counterintuitive but far more powerful and
flexible than the "wide flat" structure you started with. Access's
forms, subforms and comboboxes are optimised to work with it.

For an example, study the Northwind sample database that comes with
Access. The relationship between Suppliers and Products is (in database
terms) the same as that between Patients and Teeth.
 
Back
Top