Hi MGFoster,
Thanks so much for your response. It was very helpful. I will look for the
book you suggested at the bookstore.
I do have another question, I have interpreters who have varying schedules
and once their availability is put into the database, I need to pull out all
the Interpreters of a certain language type
that are available and see if they can potentially fulfill Interpretation
Requests for the time, date and language the request was made by the
department.
I am confused about how to set up the relationships for this in the E-R
diagram and also not sure what type of query to use for this.
Thanks so much for your help.
Kind Regards,
Smriti
"MGFoster" <me@privacy.com> wrote in message
news:smxDe.1028$Uk3.945@newsread1.news.pas.earthli nk.net...[color=blue]
> Smriti Dev wrote:[color=green]
> > Hi There,
> >
> > I'm creating a MS ACCESS database using Ms Access 2000 to store
> > Interpretation requests by different departments in a hospital and
> > Interpreter availability. All internal departments will be calling in[/color][/color]
or[color=blue][color=green]
> > faxing their Interpretation requests to Intake staff at the[/color][/color]
Interpretation[color=blue][color=green]
> > department and the intake staff will feed the request into the database.
> > However, their is one department, Rehab, that is going to be going to be
> > able to put in Interpretation requests thru an access form and they[/color][/color]
want[color=blue][color=green]
> > the capacity to view the appointments (Interpretation Requests) they[/color][/color]
have[color=blue][color=green]
> > requested.
> >
> > I have the following tables in my database:
> >
> > 1.. Interpreters
> > 2.. Interpreter Type
> > 3.. InterpreterAvailability Schedule
> > 4.. Internal Departments
> > 5.. Rehab Solutions
> > 6.. Intake Staff
> > 7.. Rehab Staff
> > 8.. Interpreter requestsbyInternalDepts
> > 9.. Interpreter requestsbyRehabSolutions
> > 10.. AppointmentBookingStatus
> > 11.. Appointments
> > 12.. AppointmentType
> > 13.. Invoice
> > 14.. Language
> > I read on 'How I use Microsoft Access User-Level Security' from
> >
www.geocities.com/jacksonmacd dated
> > march 2, 2004 that
> > "Permissions are applied at the table level. You can't configure Jet to
> > allow some users to read or write to the whole table while restricting[/color][/color]
other[color=blue][color=green]
> > users to select rows or columns."
> >
> >
> >
> > I am wondering what is the best way to design the tables for[/color][/color]
Interpretation[color=blue][color=green]
> > Request. Is it possible to keep one table for (ie. InterpreterRequests[/color][/color]
vs .[color=blue][color=green]
> > the tables:
> > 1.. Interpreter requestsbyInternalDepts
> > 2.. Interpreter requestsbyRehabSolutions I do want Rehab staff to[/color][/color]
view[color=blue][color=green]
> > the appointments they have requested
> > and still allow Rehab to view just the appointments they have requested?
> > Thanks so much for your
> > help with this.[/color]
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Put all the Interpreter requests into one table, w/ a column that
> indicates which departments have made the requests.
>
> In Access security you can assign users to Groups (aka Roles in other
> RDBMS). Create a group for each Department that will be using the DB
> and assign users to the appropriate group(s). Create a table of
> groups/departments like this:
>
> Table: DepartmentGroups
> Columns: GroupName - the name of the security group
> Department - the name of the hospital department
>
> Load it with the appropriate info. E.g.:
>
> GroupName Department
> ========= ==========
> REHAB Rehabilitation
> ACCT Accounting
> OBGYN Obstetrics/Gyneocology
> ... etc. ...
>
> Create a View (Select Query) that will read the Interpreter Requests
> table & filter the records according to which user is using the view.
> The view will have a VBA function that will determine which group the
> CurrentUser is assigned to & then display only those records that have
> info for that group's department. E.g.:
>
> SELECT IR.interpret_date_time, IR.room_no, IR.language
> FROM InterpreterRequests As IR INNER JOIN DepartmentGroups As DG
> ON IR.department = DG.Department
> WHERE DG.GroupName = GetUserGroup()
>
> The function GetUserGroup() would determine the group the current user
> belongs to & return it to the query/view. The query/view will show only
> the records for the department that group is associated with.
>
> There's a book _Access # Developer's Handbook_ by K.Getz, et. al. (the #
> indicates which version of Access), which describes a routine that
> determines the user's group. It is also on the WEB somewhere try a
> search in Google, or your favorite search engine.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQt6iEYechKqOuFEgEQIrPQCgyak9hjR8s8kooWDVxs9zzZ xYjoYAoOYl
> UuuJFKULfyDkfAQlr0DQYjkX
> =1ZnU
> -----END PGP SIGNATURE-----[/color]
