Help with Database/Form Design

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I wish I could do this, but I think I am close.

Here is what I need........

ONE Plaintiff can have MANY Devices (but each device can
appear only once on the many side for each plaintiff i.e.
Right Leg, Left Leg, Right Hip, Left Hip).
EACH Device can have MANY components.

Here is how I have the tables set up (leaving out the
irrlevant fields of course)

TBL_PLAINTIFFS
PlaintiffID (AutoNum) **(PriKey)
PlaintiffName (Text)

TBL_DEVICES
PlaintiffID (Number) ** (Prikey)
DeviceType (Text) ** (Prikey)
This table has dual primary key. PlaintiffID can occur
multiple times, but only once for each device.

TBL_COMPONENT
AutoNum ** (Prikey)
DeviceType
Component


When I create the form, I want the PlaintiffID and name in
the header, and then I want to list the devices as a drop
down (there are only 4), and then under each device is the
components, which there can be many. When I change the
drop down for the device, I want the components to change
accordingly. When I naviagate to the next Plaintiff, I
also want the info to change accordingly.

When I listed the "join" between devices and componenets,
it was listed as "intermediate." I change the join type
to the second choice, so I can list ALL the devices, but
only those records in the component table where the value
is equal. The first join type between Plaintiffs and
Devices is the default first join type.

Can anyone please guide me? Clsoe, but yet so far.

Thanks so much in advance,
Don
 
yes. the number of tables is never an issue - proper
table/relationship design is paramount. you need to
analyze your data and organize it into tables correctly.
then you need to analyze the relationships between data
and set your relationships correctly, adding/adjusting
tables as necessary. the result is what it is, you don't
make decisions based on how many tables you end up with.

again, i strongly urge you to read the white paper on
table/relationship design. think of building a database as
building a house; if you don't build a strong, stable
foundation, you're going to have problems throughout the
rest of the construction and the completed house will
never be strong and stable - always needing "fixing",
shoring up, and always prone to collapsing when exposed to
stress factors. the effort you invest into building a
strong foundation will always more than pay for itself, by
making the rest of the construction and subsequent
maintenance so much easier.

good luck.
 
Back
Top