If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Asset tracking design problem using MS Access 2007
I'm trying to set up a database to keep track of company equipment. We are
surveyors and so use: theodolites, tripods, prisms, cameras, and numerous other equipment. I want to set up a system whereby people can take kit out of the storeroom and then sign it out using an MS Access form. They can sign kit back in when they return (or take more kit out..). I have a table with staff information (ID, name, position...) then separate tables for theodolites, tripods, and other equipment. I am having trouble trying to decide the best way to relate things, and I am not sure if perhaps I need a junction table between the staff and the kit to represent a physical location change (ie. from the storeroom to a staff member). None of the MS templates quite fit this scenario, but I can't believe that this isn't the sort of thing that 1000's of companies must utilize all the time! |
#2
|
|||
|
|||
Asset tracking design problem using MS Access 2007
perhaps I need a junction table between the staff and the kit
Yep! AssetIssue -- AssetIssueID - autonumber - primary key AssetID - Number - Long Integer - foreign key StaffID - Number - Long Integer - foreign key DateOut - DateTime - Default - Now() DateIn - DateTime EstReturn - DateTime IssueBy - Number - Long Integer - foreign key Create one-to-many relationships between Staff and AssetIssue & Asset and AssetIssue, selecting Referential Integerity and Cascade Update options. Use a form/subform for Staff/Issue with combo to select item for issue. Maybe use available criteria on combo source query for only items with all issue records having DateIn. NOTE - Maybe add Calibration and Repair as a Staff member for when items is sent to CAL or RPR. -- Build a little, test a little. "S D H" wrote: I'm trying to set up a database to keep track of company equipment. We are surveyors and so use: theodolites, tripods, prisms, cameras, and numerous other equipment. I want to set up a system whereby people can take kit out of the storeroom and then sign it out using an MS Access form. They can sign kit back in when they return (or take more kit out..). I have a table with staff information (ID, name, position...) then separate tables for theodolites, tripods, and other equipment. I am having trouble trying to decide the best way to relate things, and I am not sure if perhaps I need a junction table between the staff and the kit to represent a physical location change (ie. from the storeroom to a staff member). None of the MS templates quite fit this scenario, but I can't believe that this isn't the sort of thing that 1000's of companies must utilize all the time! |
Thread Tools | |
Display Modes | |
|
|