Paper file tracking (1 Viewer)

[Renoir]

Greetings wise one,

I tinkered with the Access 2003 inventory tracking template available from MicroSoft and I am trying to make it track paper files. I’m running into a few problems.
I have a table in which the file details, a table with the owners of the files (person sitting on the file now), a table of departments (where the file is) and the file status (in, out etc…).

I like to figure out how I can track and report on the paper file’s history. I.e., who had it, and for what period.

Can someone steer me in the right direction.

Cheers,

Renoir

 

[neileg]

You need a junction table that joins the file table and the owners table. This will hold the file ID and the owner ID in a record, along with any other info you need, eg dates. When the file moves to a new owner you create a new record in this table. List the records by file ID to get the history. To find out where the file is now, you search for the record with most recent date.

 

[Renoir]

How do I make it write to this table if I want to use a button?

 

[neileg]

I would use a form bound to the junction table with a combo box to select the file and another to select the owner and other controls to capture whatever else you need

 

[Renoir]

Thanks for your reply Neil.

Sounds like I’m a bit out of my depth here.
I attached the sample DB.
Could you have a look at the table “LendingRecord” and see if this is what you mean by junction table? This is a many-to-many relation ship – right???
The “Employees” form contains a tab called “File Assigned to this Employee”.
The problem is - 1. how do I write info to the junction table from the form “Assets” and 2. how can I display this in the “Employees” form in “File Assigned to this Employee”?

I appreciate your help

Cheers,

Rene

 

[neileg]

You've got fields in the Assets table that are duplicated in the LendingRecord. I don't undestand why you need an EmployeeID in the Assets table. Otherwise the structure looks OK.

A form to create the LendingRecord entry should be bound to the LendingRecord table the Assets and Employees tables shoul be used as the data source for combo boxes bound to the AssetID and OwnerID fields.

 

[Renoir]

Does this mean I can not write to the junction table from the "Assets" form?