Windows XP 
Access 2007
I'm not looking for someone to do this for me, but I’ve google’d around with limited success. I cant find anything that matches exactly what I am trying to do.
I’m vaguely familiar with databases but have had little hands on experience. I am trying to create a database with similar functionality to the desktop issue tracking template. This will be used to log items found and reported to Lost and Found. The most important functionality will be organizing data by fields in the Lists table (item category, item status, location, ETC). My issue is understanding how to create a form that’ll dynamically update information based on what I’m looking for. It is my hope that theres a way to use a form to query specific info but if I need to create a form for each ‘set’ of data that’s fine too.
In Access 2007 I have created two tables – Lists and Items. Lists has columns for various information (ie: locations [bin 1, bin 2, etc]; Item Type; Status). All of these fields are linked to the items table via lookup tables and that works great. Ultimately the lists table is just an easy way to update drop down menus for data entry and as fields that I eventually want to sort all of the data by.
The Items table basically has all the fields from the Lists table (via lookup lists) plus text fields for things like Name, Patient name, Date fields, ETC.
*
There are a few things I need to figure out how to do.
Add some history to each record. For example if I have a record for a lost* cell phone I’d like to have a form that will prompt me for the item ID and I can simply add a comment like “PT called” or “Item donated” and have the entry time stamped. I have created a memo field in my Items table and set “Append Only” to Yes, so now I am able to clear the text in the field, save the record and view history by right clicking that field and selecting “view history”.
How can I create a form that will prompt for the item ID and give the user a blank space to type text, the save the record in such a way that it updates the memo field?
Would it be better to create a comment/memo/history table, with the item ID as a foreign key then* create a form that will save a new record (as a comment) in the history table itself – then when I need info on a specific item a form can query all of the history table records and group by foreign key?
Create a form that prompts me for the item ID that then displays all the record information (including the memo history) for printing. Is it easier here to pull history info if its stored in its own table or as a memo field? (see 1a and 1b above)? How exactly do I get the form to auto update based on user input? The desktop issue tracking template brings up a from like this for each entry, I just don’t know how to set it up. Below is a copy and paste of mock data using the issue tracking template.
Create a form that prompts for some combo of info (probably drop downs from lists table) then displays a list of all entries fitting that criteria. Basically the form would need to allow me to chose from the information for each column in the lists table (status, location, category, ETC) For example if I wanted to see a list of all the cell phones found on the 4th floor I could give the form those pieces of data and get the list. Once setup this will be the most* common functionality of this database – since each director could get a list of info specific to their areas, I could sort all the items by what stage (status field) its in (which would basically give me a to do list). I am hoping this will be as easy as creating auto-updating drop down menus off fields in the Lists table (category, status) but have no idea how to link the form to the query – or how to setup the query to accept the info from the form
Reply With Quote
