MS Assets Management sample DB - History table?

[Tec92407]

I'd like to modify the sample MS Assets management database to include a
history of asset ownership. For example, if a user is assigned an asset (such
as a computer) and that asset is re-assigned to a different user, I'd like
that information to be stored in a history table where it could later be
reviewed or a report generated.

I'm using Access 2003.
Can anyone provide some guidance on how to do this?

Thanks.

 

[tina]

a review of the Asset Tracking database template that ships with Access 2003
shows the following relationship

Employees (parent) 1:n Assets (child)

the two tables are linked on the primary/foreign key fields which are both
named EmployeeID. this setup allows a only one particular employee to be
assigned to each asset at any given time, which doesn't allow for historical
data.

allowing for historical data about asset employee assignment requires
changing the tables' relationship to many-to-many. to model this type of
relationship in Access, you need to create a third table to serve as a
"linking" table between the two. suggest you delete the EmployeeID field
from table Assets. create a new table, as

tblAssetAssignments
AssignID (primary key, Autonumber)
AssetID (foreign key from table Assets)
EmployeeID (foreign key from table Employees)
DateAssigned

note: the above table is based on the assumption that an employee is
responsible for an asset from their DateAssigned, until the next employee
assignment record is added for that asset. but if the asset sometimes sits
idle in inventory, rather than going directly from one employee to another,
you may want to add a DateReturned field to the above table.

each time you assign a particular asset to a specific employee, add a new
record to this table. you can find out who is *currently* assigned to each
asset, by using a Totals query to GroupBy EmployeeID and return the "Max"
DateAssigned. (or, if you have a DateReturned field, you can do a simple
SELECT query WHERE the DateReturned field is Null.)

hth

 

[Tec92407]

Thanks Tina

I followed your suggestion and it worked well.

In addition, I created a new page (tab) on the assets form and used the new
Asset Assignments table for the subform. So I can click an "Assignments" tab
to assign an employee.

I have a couple of other questions.

1. At the moment, I only show the Employee ID, DateAssigned and DateReturned
fields on my Assignments subform. I'd also like to add the employee
DepartmentID as a view only field. I'm thinking I need to add a DepartmentID
foreign Key in the Employees table and set a relationship between employees
and department tables.
Is this correct?

2. There is an Employee form with a subform that is accessed with a Tab
labeled
"Assets assigned to this Employee". It shows three fields, asset
description, barcode number and serial number. It doesn't work correctly
anymore since making the new change. Am I correct in thinking that I will
have to change this form to reflect a select query? I want to show more
fields than just these three.

thanks again for your help.

 

[tina]

comments inline.

Thanks Tina

I followed your suggestion and it worked well.

In addition, I created a new page (tab) on the assets form and used the new
Asset Assignments table for the subform. So I can click an "Assignments" tab
to assign an employee.

I have a couple of other questions.

1. At the moment, I only show the Employee ID, DateAssigned and DateReturned
fields on my Assignments subform. I'd also like to add the employee
DepartmentID as a view only field. I'm thinking I need to add a DepartmentID
foreign Key in the Employees table and set a relationship between employees
and department tables.
Is this correct?

yes. keep in mind that departments are related to employees, not to assets
or asset assignments. to *show* an employee's department in the
AssetAssignments subform, use a query or SQL statement as the RowSource of
the EmployeeID combo box in the subform. in the query/SQL, link tblEmployees
to tblDepartments on the primary/foreign fields pair. add the DepartmentName
field to the query grid. make sure you increase the combo box's ColumnCount
property by 1, and add the following to the *end* of the ColumnWidth
property's value, as

; 0"

now the combo box includes a column containing the employee's department
name, but the data doesn't show in the droplist. however, the column's data
is available to you programmatically in the subform. so add an unbound
textbox to the subform, i'll call it txtDepartment, and set its'
ControlSource to

=[ComboboxName].[Column](n)

replace "n" with the index number of the DepartmentName column. combo box
columns have a zero-based index, so the first column (counting
left-to-right) is zero (0), the second column is (1), the third column is
(2) , etc.

2. There is an Employee form with a subform that is accessed with a Tab
labeled
"Assets assigned to this Employee". It shows three fields, asset
description, barcode number and serial number. It doesn't work correctly
anymore since making the new change. Am I correct in thinking that I will
have to change this form to reflect a select query? I want to show more
fields than just these three.

well, i haven't looked at the template db again. but you're correct that
once you remove a field from a table, you also have to remove the field from
queries, forms and reports. and remove references to the field from VBA
code, macros and expressions. frankly, it's a pain in the butt to go through
a database and track down all that, but that goes with the territory of
modifying somebody else's database (or even your own db, after it's
completely built "one way".) on the plus side, you usually get an pretty
thorough grasp of how the database operates when you have to dig through
everything, and tearing apart a functioning database is a great way to learn
about various aspects of development.

in this particular case, a form that was built on the concept of an employee
being linked directly to an asset, is going to fail, because that concept
has been replaced with the indirect link provided by the linking table
(tblAssetAssignments). you can go through and tinker with the form until it
supports the new table relationships, or you might find it easier to simply
build a new form from scratch.

hth

 

[tec92407]

Hiy

I've created a query and added it to my form. It worked as expected. Later,
I realized I needed to filter the query on a null ReturnDate date field so it
would only show records where the ReturnDate field is empty. So, I added the
ReturnDate field to my query, unchecked "show" and typed Is Null in its
criteria field. But the criteria is being ignored. What's wrong?

comments inline.

Thanks Tina

I followed your suggestion and it worked well.

In addition, I created a new page (tab) on the assets form and used the new
Asset Assignments table for the subform. So I can click an "Assignments" tab
to assign an employee.

I have a couple of other questions.

1. At the moment, I only show the Employee ID, DateAssigned and DateReturned
fields on my Assignments subform. I'd also like to add the employee
DepartmentID as a view only field. I'm thinking I need to add a DepartmentID
foreign Key in the Employees table and set a relationship between employees
and department tables.
Is this correct?

yes. keep in mind that departments are related to employees, not to assets
or asset assignments. to *show* an employee's department in the
AssetAssignments subform, use a query or SQL statement as the RowSource of
the EmployeeID combo box in the subform. in the query/SQL, link tblEmployees
to tblDepartments on the primary/foreign fields pair. add the DepartmentName
field to the query grid. make sure you increase the combo box's ColumnCount
property by 1, and add the following to the *end* of the ColumnWidth
property's value, as

; 0"

now the combo box includes a column containing the employee's department
name, but the data doesn't show in the droplist. however, the column's data
is available to you programmatically in the subform. so add an unbound
textbox to the subform, i'll call it txtDepartment, and set its'
ControlSource to

=[ComboboxName].[Column](n)

replace "n" with the index number of the DepartmentName column. combo box
columns have a zero-based index, so the first column (counting
left-to-right) is zero (0), the second column is (1), the third column is
(2) , etc.

2. There is an Employee form with a subform that is accessed with a Tab
labeled
"Assets assigned to this Employee". It shows three fields, asset
description, barcode number and serial number. It doesn't work correctly
anymore since making the new change. Am I correct in thinking that I will
have to change this form to reflect a select query? I want to show more
fields than just these three.

well, i haven't looked at the template db again. but you're correct that
once you remove a field from a table, you also have to remove the field from
queries, forms and reports. and remove references to the field from VBA
code, macros and expressions. frankly, it's a pain in the butt to go through
a database and track down all that, but that goes with the territory of
modifying somebody else's database (or even your own db, after it's
completely built "one way".) on the plus side, you usually get an pretty
thorough grasp of how the database operates when you have to dig through
everything, and tearing apart a functioning database is a great way to learn
about various aspects of development.

in this particular case, a form that was built on the concept of an employee
being linked directly to an asset, is going to fail, because that concept
has been replaced with the indirect link provided by the linking table
(tblAssetAssignments). you can go through and tinker with the form until it
supports the new table relationships, or you might find it easier to simply
build a new form from scratch.

hth

 

[tina]

post the SQL from the query. also, is the ReturnDate field in the table a
Date/Time data type?

Hiy

I've created a query and added it to my form. It worked as expected. Later,
I realized I needed to filter the query on a null ReturnDate date field so it
would only show records where the ReturnDate field is empty. So, I added the
ReturnDate field to my query, unchecked "show" and typed Is Null in its
criteria field. But the criteria is being ignored. What's wrong?

comments inline.

Thanks Tina

I followed your suggestion and it worked well.

In addition, I created a new page (tab) on the assets form and used

the
new

Asset Assignments table for the subform. So I can click an

"Assignments"
tab

to assign an employee.

I have a couple of other questions.

1. At the moment, I only show the Employee ID, DateAssigned and DateReturned
fields on my Assignments subform. I'd also like to add the employee
DepartmentID as a view only field. I'm thinking I need to add a DepartmentID
foreign Key in the Employees table and set a relationship between employees
and department tables.
Is this correct?

yes. keep in mind that departments are related to employees, not to assets
or asset assignments. to *show* an employee's department in the
AssetAssignments subform, use a query or SQL statement as the RowSource of
the EmployeeID combo box in the subform. in the query/SQL, link tblEmployees
to tblDepartments on the primary/foreign fields pair. add the DepartmentName
field to the query grid. make sure you increase the combo box's ColumnCount
property by 1, and add the following to the *end* of the ColumnWidth
property's value, as

; 0"

now the combo box includes a column containing the employee's department
name, but the data doesn't show in the droplist. however, the column's data
is available to you programmatically in the subform. so add an unbound
textbox to the subform, i'll call it txtDepartment, and set its'
ControlSource to

=[ComboboxName].[Column](n)

replace "n" with the index number of the DepartmentName column. combo box
columns have a zero-based index, so the first column (counting
left-to-right) is zero (0), the second column is (1), the third column is
(2) , etc.

2. There is an Employee form with a subform that is accessed with a Tab
labeled
"Assets assigned to this Employee". It shows three fields, asset
description, barcode number and serial number. It doesn't work correctly
anymore since making the new change. Am I correct in thinking that I will
have to change this form to reflect a select query? I want to show more
fields than just these three.

well, i haven't looked at the template db again. but you're correct that
once you remove a field from a table, you also have to remove the field from
queries, forms and reports. and remove references to the field from VBA
code, macros and expressions. frankly, it's a pain in the butt to go through
a database and track down all that, but that goes with the territory of
modifying somebody else's database (or even your own db, after it's
completely built "one way".) on the plus side, you usually get an pretty
thorough grasp of how the database operates when you have to dig through
everything, and tearing apart a functioning database is a great way to learn
about various aspects of development.

in this particular case, a form that was built on the concept of an employee
being linked directly to an asset, is going to fail, because that concept
has been replaced with the indirect link provided by the linking table
(tblAssetAssignments). you can go through and tinker with the form until it
supports the new table relationships, or you might find it easier to simply
build a new form from scratch.

hth