Database Design

[Pete]

I need help with my design and relationships. I have the following
tables:

tblMember
MemberID
Name
Surname
etc
etc

tblEquipment
EquipmentID
Equipment

tblEquipmentType
EquipmentTypeID
EquipmentID
EquipmentType

tblMembersEquipment
MembersEquipmentID
MemberID
EquipmentID (Combo from tblEquipment)
EquipmentTypeID (Combo from tblEquipmentType)
DateInstalled
SerialNo

What I want is to keep track of equipment installed for a member, and
the EquipmentTypeID Combo in tblMembersEquipment to vary depending on
EquipmentID Combo. Can anyone tell me how. I have seen the article ID
289670 Synchronizing two combo's but I'm unable to make it work with
the above.

Any help would be appreciated.

Pete

 

[Guest]

Pete:

You don't need the EquipmentID column in tblMembersEquipment. Each row in
the table maps to the correct EquipmentID via the EquipmentType ID foreign
key column. For a demo of correlated combo boxes see:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

Ken Sheridan
Stafford, England

 

[Pete]

Ken,

I've deleted the EquipmentID from tblMembersEquipment but now am unable
to store the value anywhere from the Combo. can you help me with a step
by step process to create the above?

Thanks

Pete

 

[Bob]

Pete,
Your field "Name" in tblMember has to be changed because you used a reserved
word. Try FirstName, or something. I don't know you full solution, but that
is one problem.
Bob

 

[Guest]

Pete:

You have a foreign key column EquipmentID in tblEquipmentType which
references the primary key of tblEquipment, so once you store an
EqipmentTypeID value in tblMembersEquipment you know what row in tblEquipment
this refers to. Consequently you don't need to store the EquipmentID in this
table, and moreover should not do as it would constitute redundancy, which is
'a bad thing'. When entering data into tblMembersEquipment in a form (the
same can apply in datasheet view though most Access developers would not use
a combo box as the display control in datasheet view of a table on the basis
that data should only be entered via forms) you'd have a combo box bound to
the EquipmentTypeID field. The RowSource property of the combo box would be:

SELECT EquipmentTypeID, EquipmentType, Equipment
FROM tblEquipmentType INNER JOIN tblEquipment
ON tblEquipmentType.EquipmentID = tblEquipment.EquipmentID
ORDER BY Equipment, EquipmentType;

This combo box, cboEquipmentType say, would have the following properties:

ControlSource: EquipmentTypeID
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;2.5cm;2.5cm or rough equivalent in inches.
ListWidth: 5cm this should be the sum of the ColumnWidths

Experiment with the ColumnWidth dimensions to get the best for, but the
first dimension must be zero to hide the first column. The ListWidth value
should equal the sum of the ColumnWidth dimensions you use.

The combo box should now list the EquipmentType and Equipment values in
Equipment order with the EquipmentTypes in order within each Equipment group.
When you select an item it will show the EquipmentType. To show the
Equipment for the selected type put an inbound text box on the form with a
ControlSource property of:

=cboEquipmentType.Column(2)

The Column property is zero-based so Column(2) is the third column i.e.
Equipment.

For data entry of this type you'd normally use a subform based on
tblMembersEquipment embedded in a form based on tblMember (or better a sorted
query based on the table). The subform and parent form would be linked on
the MemberID columns, so you would not need a control for MemberID in the
subform as it would be given a value automatically by the linking mechanism.
So, assuming MembersEquipmentID is an autonumber, the only controls on the
form would be for the following fields, the first being the combo box
discussed above.

EquipmentTypeID
DateInstalled
SerialNo

Ken Sheridan
Stafford, England