Multiple records for unique id number

[Pyrite]

I have a table, tblEngineerDetails. This simply contains a unique Engineer ID
Number, Surname, Forename. On my other tables I then include Engineer ID
field as a primary key and link the fields in the relationship e.g.
tblEngineerAddresses. This way on an enter engineer details form the payroll
number is entered once and populated everywhere. I also have tblFormsSent.
This is where my problem comes in. One engineer can have multiple forms sent
to them so this table has no primary key as this would not allow duplication
of the EngineerID field which is necessary if they have had more than one
form sent to them. I have linked this with a one to many relationship from
tblEngineerDetails. My real problem comes now. I have created a form with two
combo boxes, one displays Engineer ID and the other displays Forms Sent. When
a user selects the Engineer ID the Forms Sent cbo is populated with the forms
which have been sent to them so the user can select which of the forms this
wish to update (has it been returned etc). My problem is that in the Engineer
ID field it is simply dropping down an Engineer ID for each form they have
been sent, e.g. there may be four '1111' Engineer ID's displayed in the combo
box, each representing an individual record in the Forms Sent table so when
each is selected there are no further options in the second drop down, it is
populated with the forms sent from that record. The user would then have to
go back to the original drop down and select the second '1111' Engineer ID
and see which docs that one relates to and so on until they found the right
one. I want '1111' to be displayed only once in the first drop down
regardless of how many records exist for that number in the Forms Sent table
and then when that is selected I want all the forms sent to be populated in
the next box so that the user can choose which form and then press my control
button which opens another form which runs the required query. The Engineer
ID field is required in the Forms Sent table to keep track on who the forms
have been sent to.

 

[Ken Sheridan]

Your post raises a number of issues in addition to the problem you are
experiencing:

1. By having EngineerID as the primary key of more than one table the
relationship is one-to-one. Where the second table records attributes which
are common to all engineers, e.g. address data, there is no need for a second
table; all that's needed is a set of extra columns in tblEngineerDetails.
Where a second table related one-to-one to tblEngineerDetails would be
appropriate would be if the second table represented a sub-type of type
Engineers and had attributes specific to the sub-type, e.g. you might have
tables ElectricalEngineers and MechanicalEngineers each with a primary key
EngineerID, but with separate sets of columns representing the attribute type
appropriate to the different types of engineering.

2. The tblFormsSent table can, and should, have a primary key. In this
case it’s a multiple one of EngineerID and Form (if each form can be sent
only once to each engineer) or EngineerID, Form and DateSent for instance (if
each form can be sent multiple times to each engineer on different dates).

3. As regards your problem with the combo boxes the first, cboEngineer say,
should be set up like so:

RowSource: SELECT EngineerID, Forename & " " & Surname FROM
tblEngineerDetails ORDER BY Surname, Forename;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

The second, cboFormsSent say, should gave a RowSource property like this:

SELECT Form FROM tblFormsSent WHERE EngineerID = Form!cboEngineer ORDER BY
Form;

Note that, both controls being in the same form, the Form property can be
used here rather than a full reference to the form by name.

In the AfterUpdate event procedure of cboEngineer requery cboFormsSent with:

Me.cboFormsSent.Requery

I've assumed in the above that the tblFormsSent table contains a column Form
with the form name or description rather than a foreign key numeric FormID
referencing the primary key of a tblForms table. If the latter is the case,
however, the RowSource property of cboFormsSent would be:

SELECT Form FROM tblFormsSent INNER JOIN tblForms ON tblForms.FormID =
tblFormsSent.FormID WHERE EngineerID = Form!cboEngineer ORDER BY Form;

4. Arising from the last point above, even if tblFormsSent includes a Form
column with the name or description of the form you should still have a
tblForms table with a Form column as its primary key and one row per form.
You can then enforce referential integrity in the relationship between
tblForms and tblFormsSent, which prevents an invalid form name or description
being entered in the latter.

Ken Sheridan
Stafford, England