@mjws00
You can be clever with this little data model and yet be relatively simple with its appearance. I might just have a little stab at creating this app myself just for fun. The easiest way is to use a main form and subform combined together. In which direction you look at design interface is a matter of choice clearly but this is my contribution as I see this:-
tblTool
Tool_no data type autonumber (Primary key)
ToolCategory datatype text size(50) (foreign key to tblToolCategory)
ToolDescription datatype text size(255)
InputDate datatype datetime default Now()
tblToolCategory
ToolCategory datatype text size(50) (Primary key >linked to tbltool.ToolCategory)
InputDate datatype datetime default Now()
tblToolUseage
UID autonumber (Primary Key)
tool_no datatype Number (Long Integer) (foreign key to tblTool.Tool_no)
emp_id (datatype assumed as Number Long integer but depends on your referencing) (foreign key to tblEmployee.emp_id)
DateOut datatype datetime (no default)
TimeOut datatype datetime (no default)
DateBack datatype datetime (no default)
TimeBack datatype datetime (no default)
InputDate datatype datetime default Now()
There are obvious validation check constraints to be applied to the above table noteably the date fields ie: cannot be back if it has not gone out that type of thing)
tblEmployee
emp_id (Primary key assumed to be autonumber but up to you)
FirstName datatype Text size (50)
Surname datatype Text size (50)
InputDate datatype datetime default Now()
Relationships
a) Create a one to many link between tblToolCategory.ToolCategory field and tblTool.ToolCategory in the relationships window. Set cascade update to yes
b) Create a one to many link between tblTool.Tool_no field and tblToolUseage.Tool_no in the relationships window.
c) Create a one to many link between tblEmployee.emp_id and tblToolUseage.emp_id in the relationships window.
The linkages on b and c to tblToolUseage provide for a
many to
many relationship. An Employee can have one particular tool many times out conversely a Tool can have been out to a single employee or many employees, but only once obviously at any one time. (I hope I am making myself clear)
1) Create one simple query based on tblCategory save it as qryToolCategory create a tabular form based on that query and save it as frmCategory, design it to your liking. This form services any edits you might want on that datasource.
2) Create one simple query based on tblEmployee save it as qryEmployee create a tabular form based on that query and save it as frmEmployee, design it to your liking. This form services any edits you might want on that datasource.
3) Create one simple query based on tblTool save it as qryTool create a tabular form based on that query and save it as frmTool, design it to your liking. This form services any edits you might want on that datasource. Include in that form a dropdown controlsource of which is ToolCategory and its rowsource set to
"SELECT ToolCategory FROM tblToolCategory ORDER BY ToolCategory;" This effectively provides you with the assignment capability for a category set against a particular tool.
4) Now create a query based on tblToolUseage save it as qryToolUseage. This is the main datasource in effect. This is main query of reference where you will be hooking an employee to a tool at any given time.
Now depending on what suits you best in how you work you can design it various ways coming at it from the Employee side or from the Tool side or a bit of both. It depends largely on what it is you are comfortable viewing. For instance if an employee comes to you and says "
I want a torque wrench" you can either call up his single record form view main form (ie not tabular) and see his details under which lists a history of his tool useage in a subform located beneath his/her details in descending order latest to the top etc etc. Sitting on this main form could be a Listbox of all Tool Categories alongside which is a list of 'Currently Available' tools that could be selectable sympathetic to the Category you pick on screen. Once an available tool is selected you hit a button to confirm and it 'appends' the displayed
tool_no (from the listbox) and displayed
emp_id to the tool useage along with the current date and time that tool goes out.
Now obviously this is
ONE way of doing it. You might wish however to see by default a list of
Available tools only in tabular view and assign an employee to it THAT way it is a matter for you..........but essentially this is how I see the structure of this. The inner workings of the tool availability, the appending of new tool useage and the display of only those tools available together with a list of ALL tools that are currently out and 'not' returned is a matter for development
I trust you can follow the design of this as I see it. Design perspective is a personal thing ultimately but I rather gather that simplicity is the ultimate goal and whilst my post is not particularly simple and concise it is meant to help you in some way..I hope.
1
