Using Access to Digitize a Form

[mhig9000]

I run a substance abuse treatment facility located within a homeless shelter, as such we have a number of incidents involving patients and staff on a regular basis. It is very important that we be able to track these and share with our corporate office easily for insurance purposes and as an easy reference when we consider admitting someone to ensure that they did not cause problems in the past. Currently we use a paper form with a number of check boxes and fill-ins as well as a long narrative section (it is similar in many ways to a police report).

I am considering using access to create a form that approximates the paper form we currently use however im not sure what the best way to go about it is, or even if access is the program to use. (i have taken a beginner course on access from lynda.com and have done a bit of reading online, but I have never created a working access database before)

Basically I would like the computerized version to do 3 things:

Provide easily referenceable records of each incident with all details (excel would be able to do this easily, aside from perhaps managing the narrative section)

Provide a user form that would be very easy and straightforward for people who are not very computer literate to complete, and would conceal any information in the database about previous incidents, but allow access to all of the information to selected staff. (since they do not have a lot of computer literacy it would be nice to have as many controls on data accuracy as possible)

Provide a way to filter and/or search for records containing only one staff person or only one client.



It seems like the easiest way to do this would be to create just one table with everything in it, in which case using access instead of excel would only be for creating and customizing input forms. This would obviously run the risk of having multiple incidents about the same patient that are unlinked in any way, and therefore allowing the possibility of the same patient's name to be put in mispelled, etc and then they dont show up in searches. However, most of the relationships would be many to many (since many staff people will write multiple incidents, and multiple incidents will involve more than one staff person or patient.) I have done some research, but I have not seen much about how easily many to many relationships are managed.

So what do you think, is access the way to go, if so could anyone provide some resources for managing this data?

Thanks in advance for any answers or suggestions!

[June7]

Access is appropriate for this purpose. Some more research wouldn't hurt. Check the 'sticky' thread tutorials here http://forums.aspfree.com/microsoft-access-help-18/

Many-to-many data requires a 'junction table' of records that associate the related information. Example:
ClientStaffIncident (table name)
ClientID (foreign key relates to primary key of Client table)
StaffID (foreign key relates to primary key of Staff table)
DateIncident
IncidentCode
IncidentComment

Then start building. When you encounter specific issues post thread with question.