How to Move Record(s)

[Training Spec.]

I have two Tables (Employees and Hardware) in one Form (where Employees is
the Form and Hardware is the Sub-Form). Both the Employees and Hardware
Tables’ Primary Key is Position Number. We allocate hardware to our employees
based on the position they hold. If they change positions, they get a
different set of hardware. In the interim, we move their hardware to “stockâ€
(Last Name field in the Employees Table). When we change the Primary Key to
one associated with stock, Access removes the associated hardware.

Is there a way to change the 'association'? That is, we’d like to move the
associated hardware (one to eight records in the Hardware Table) to one of
the Stock records (in the Employees table) until a different employee fills
the position.

Thanks.

 

[Tom van Stiphout]

On Tue, 5 May 2009 05:12:01 -0700, Training Spec.

This really requires more discussion before the best database design
can be determined, but I am leaning towards creating Kits independent
of employees. A Kit is a collection of hardware for a Position:
KitID autonumber PK
KitDescription text50 UIDX
PositionID long int FK

HardwareID autonumber PK
HardwareName text50 UIDX

KitID long int PK
HardwareID long int PK

This gives us a M:M between hardware and kit.

Next in Employees table have a KitID NULL field.

-Tom.
Microsoft Access MVP

 

[tina]

your tables structure is very confusing. you have Stock records in the
Employee table? why don't you have a Stock table? and one to eight Hardware
records can be associated with an employee? then how can the Employees and
Hardware tables share a primary key? that would have to be a one-to-one
relationship, so only one hardware record could be associated with only one
employee record.

normalization rules require that each table be about a single subject. the
Employees table should describe an employee, not what hardware is assigned
to him/her. and the primary key certainly shouldn't be a "Position" number;
that would mean changing the record's primary key each time the employee
changed positions. no, the primary key of a table should be stable as well
as unique. the Hardware table should describe hardware, not who it's
assigned to. and so on. if you need to track specific pieces of hardware
assigned to specific employees (such as, the employee gets hardhat #12, not
just employee gets a hardhat), then the Stock table should list each
specific piece of hardware with its' unique identifier - serial number,
whatever, including a foreign key from the Hardware table, because that's
where the hardware description resides. you would need an EmployeeStock
table, to assign specific pieces of hardware to specific employees, probably
with an Out date (assigned on) and a Return date (turned back in to stock).
you probably also want a PositionHardware table, to document what hardware
is required for each position - this is more a reference table than
anything. and you'll probably want a position field in the Employee table,
to track what position the employee currently holds. if you need a history
of same, then instead you'll need an EmployeePositions table, with a
DateAssigned field.

hth

 

[Training Spec.]

Thanks to both of you for the ideas. The "rules" from the top change with the
wind, making it difficult to meet stable conditions for a PK. Also, the
primary user changed her mind. Now, all she wants is to have the LastName
field display in red if she changes it temporarily to Stock.

Thanks again!

 

[Tom van Stiphout]

Well said.

-Tom.
Microsoft Access MVP

well, you're welcome. just an extra piece of advice: don't let an
uninformed user dictate your tables/relationships structure - that's your
job as developer, to do it right. and the user should never have to worry
about what's going on "under the hood"; data storage and data
display/interaction are two completely different subjects, so store the data
correctly, then work on giving the user an interface that will his/her
needs.

hth

<clip>

 

[tina]

thanks, Tom. on second read, though, turns out i should have said it just a
little better! i meant to say: "...work on giving the user an interface
that will MEET his/her needs."