Trouble populating datasheet - Help needed!

[Guest]

Please can you help? I am trying to monitor the relocation of an asset to a
different employee or to a different department. So far I have the following
tables-

TblAssets
AssetsID (autonum, PK)
AssetDescription (txt)
EmployeeID (number, FK)
DepartmentID (Number, FK)
Make (txt)
Model (txt)
SerialNumber (txt)

TblDepartments
DepartmentID (Autonum, PK)
DepartmentName (txt)

TblEmployees
EmployeeID (Autonum, PK)
FirstName (txt)
LastName (txt)

TblTransaction
MoveAssetID (Autonum, PK)
MoveAssetDescription (txt)
AssetID (number, FK)
EmployeeID (number, FK)
DepartmentID (number, FK)
FromDept (txt)
ToDept (txt)
FromEmployee (txt)
ToEmployee (txt)
TransactionDate (Date/time)

Relationships are set so that there are one-many links between TblAssets and
TblDepartments on DepartmentID; TblAssests and TblEmployees on EmployeeID;
TblAssets and TblTransaction on AssetID.

Main form frmAssets has comboboxes for Employee and Department fields and
contains a tabbed subform frmTransaction which displays the fields
MoveAssetID, TransactionDate, MoveAssetDescription, FromDept, ToDept,
FromEmployee, and ToEmployee in datasheet view.

I have set FrmAssets so that if the Employee selected in combobox for asset
#1 is changed to a different employee then a form frmAssetMovtEmployee is
called requesting selection of options via combo boxes for
MoveAssetDescription, FromEmployee and ToEmployee. This is accomplished by
calling a macro to open the form using OnChange event. The same has been
done for frmAssetMovtDepartment.

The data changes are being recorded accurately in TblTransaction except for
the asset number and I just don’t seem to be able to get data to echo in the
subform frmTransaction. I’m sure I am missing something simple here but just
can’t see it. Your help is very much appreciated.

Joe

 

[tina]

from looking at the tblTransaction, i'm assuming you want a history of where
the asset has been, as well as where it is now. but your tables design
involves a lot of repetition, and breaks normalization rules in tblAssets:
in that table, EmployeeID and DepartmentID don't describe an asset - they
describe an asset's current assignment. suggest the following table changes,
as

TblAssets
AssetsID (autonum, PK)
AssetDescription (txt)
Make (txt)
Model (txt)
SerialNumber (txt)

tblDepartments and tblEmployees looks okay as is.

instead of a tblTransactions, suggest the following, as

tblAssetAssignments
AssignID (pk)
AssetID (fk)
EmployeeID (fk)
DepartmentID (fk)
AssignmentDate (date/time)

this table includes the current assignment of each asset, as well as all
previous assignments. simply by doing a sort on the date field, it's easy to
present a chronological list of all assignments for a specific asset; the
most recent AssignmentDate is the current assignment, of course.

this makes the data entry easy in forms. all you need is a main form bound
to tblAssets, with a subform bound to tblAssetAssignments. use combobox
controls in the subform, bound to the EmployeeID and DepartmentID fields,
with RowSources of tblEmployees and tblDepartments respectively.

*****

just as a further note on normalization rules, lets take a look at the
tblTransactions you posted:

TblTransaction
MoveAssetID (Autonum, PK)
MoveAssetDescription (txt)
AssetID (number, FK)
EmployeeID (number, FK)
DepartmentID (number, FK)
FromDept (txt)
ToDept (txt)
FromEmployee (txt)
ToEmployee (txt)
TransactionDate (Date/time)

the MoveAssetDescription is, i'm assuming, the same data already stored in
tblAssets. you should not store the same data twice - simply store the
primary key of tblAssets as a foreign key; you can get the description data
whenever you need it by linking the two tables in a query. ditto the
FromDept/ToDept and FromEmployee/ToEmployee fields - don't store the text
data from those tables again in a separate table, just store the pk values
as foreign keys, and use a multi-table query to retrieve the text as needed.

hth

 

[Guest]

Thanks Tina for taking time to help me out. I'm sorry I've only just got
back to the discussion database today.

I've taken EmployeeID and DepartmentID out of tblAssets as you suggested. I
can see how much more work I still need to do to normalise this database
which was based on the Asset Tracking Database template from Microsoft.
Thanks for the advice re tblAssetAssignments - it describes much more clearly
what I am trying to do.

Just a clarification on the MoveAssetsDescription field - it was just meant
to provide a combobox with options to describe the reason for the change i.e.
loan, relocation etc. so I've kept the field with a name that better fits
the data contained in it.

Again, many thanks for taking the time to help get my head refocused and
back on the right track.

Joe

 

[tina]

you're welcome

Thanks Tina for taking time to help me out. I'm sorry I've only just got
back to the discussion database today.

I've taken EmployeeID and DepartmentID out of tblAssets as you suggested. I
can see how much more work I still need to do to normalise this database
which was based on the Asset Tracking Database template from Microsoft.
Thanks for the advice re tblAssetAssignments - it describes much more clearly
what I am trying to do.

Just a clarification on the MoveAssetsDescription field - it was just meant
to provide a combobox with options to describe the reason for the change i.e.
loan, relocation etc. so I've kept the field with a name that better fits
the data contained in it.

Again, many thanks for taking the time to help get my head refocused and
back on the right track.

Joe