469,613 Members | 1,363 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

Privileges, Security & Database Design Issues

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 or
faxing their Interpretation requests to Intake staff at the Interpretation
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 want
the capacity to view the appointments (Interpretation Requests) they have
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 other
users to select rows or columns."

I am wondering what is the best way to design the tables for Interpretation
Request. Is it possible to keep one table for (ie. InterpreterRequests vs .
the tables:
1.. Interpreter requestsbyInternalDepts
2.. Interpreter requestsbyRehabSolutions I do want Rehab staff to view
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.

Thanks kindly,
Smriti
Nov 13 '05 #1
4 1816
Smriti Dev wrote:
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 or
faxing their Interpretation requests to Intake staff at the Interpretation
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 want
the capacity to view the appointments (Interpretation Requests) they have
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 other
users to select rows or columns."

I am wondering what is the best way to design the tables for Interpretation
Request. Is it possible to keep one table for (ie. InterpreterRequests vs .
the tables:
1.. Interpreter requestsbyInternalDepts
2.. Interpreter requestsbyRehabSolutions I do want Rehab staff to view
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.


-----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-----
Nov 13 '05 #2
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:sm****************@newsread1.news.pas.earthli nk.net...
Smriti Dev wrote:
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 or faxing their Interpretation requests to Intake staff at the Interpretation 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 want the capacity to view the appointments (Interpretation Requests) they have 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 other users to select rows or columns."

I am wondering what is the best way to design the tables for Interpretation Request. Is it possible to keep one table for (ie. InterpreterRequests vs . the tables:
1.. Interpreter requestsbyInternalDepts
2.. Interpreter requestsbyRehabSolutions I do want Rehab staff to view 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.


-----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-----

Nov 13 '05 #3
Smriti Dev wrote:
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.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know how to create that query since your table list did not
include an InterpreterAvailability table, nor, its columns. A DDL (Data
Declaration Language description of the table(s)) would be nice; and,
some sample data. I'd guess the InterpreterAvailability table would
hold the start & end date/times of availability of each interpreter -
IOW, a work schedule table.

Re: your listed tables. You have 2 tables Intake Staff & Rehab Staff
that probably would be better as one table w/ a column indicating the
department of the staff member (intake or rehab - or some other
department).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt69qYechKqOuFEgEQKVIwCgrIuiglAbLwXTwqtvzgiyWM 9eSYMAnjOP
WqNz/bBbaiOvTa7rX71vvlJ7
=3eVS
-----END PGP SIGNATURE-----
Nov 13 '05 #4
Hi MGFoster,

Thanks for your reply and sorry for the delay in getting back to you. I was
working on the DDL
and also wanted to give you some background information.

Please see below for background info and DDL info.

Background Information:
I want to create a database to store information about Interpreters and
track the appointments they have. Interpreters are requested on a
continuous basis by Internal Departments at the UHN, and also by Rehab
Solutions. Internal departments are not charged for Interpreters but Rehab
Solutions is charged. Rehab Solutions should be able to view the request
they make and edit it. Sometimes Interpretation Requests are made for a
group of people. The number of people in the group are to be recorded if it
is a request for a group.



Information about Interpreter's will include their names, contact
information and how much they are paid/hr, the type of Interpreter and the
languages they speak the timings they are available to work. An Interpreter
can only have one appointment scheduled at a certain time.

Departments make Interpreter requests by indicating the time they require an
interpreter and the language they require. Additional information about the
appointment is stored including the Patient's Lastname, MRN number, location
of the appointment, the care provider who will be at the appointment and the
type of care provider they are & any comments. Each department can make many
requests for Interpreters. Departments are invoiced for the Interpretations
they request.

Information about Intake staff who will be booking the Interpreters will be
stored including their Staff Numbers, names, email addresses.

The status that the appointment booking is at will also be recorded. Options
include pending, assigned, cancelled, review.

DDL:

Interpreters Interpreter_ Role
Interpreter_Availability Schedule
Care Provider
CareProvider_Role

InterpreterID (PK)
InterpreterRoleID (PK)
InterpreterAvailabilityID(PK)
CareproviderID (PK)
CareProviderRole_ID(PK)

Interpreter Number
InterpreterRole

(i.e. staff, volunteer, contract)
InterpreterID (FK)

-so we know which Interpreter is avail.
CareProviderFirstName
Care_Provider_Type

(ie. nurse)

LastName
InterpreterTypeCost
DataInterpreterAvail
CareProviderLastName
FirstName
InterpreterTypePercentage
StartTimeInterpreterAvail
CareProviderPhone
PhoneNum

EndTimeInterpreterAvail
CareProviderPhoneExtension
Email
CareProviderRole_ID(fk)
InterpreterAvailabilityID (FK)

InterpreterTypeID(FK)

LanguageID (FK)







Staff Staff_Dept Interpreter requests
Area

(dept, rehab)
EnterpriseType
StaffID (PK)
Staff_dept(PK)
InterpreterReqID (PK)
AreaID (PK)
EnterpriseID(PK)
StaffNum
Staffing_dept_description
CareProviderID(fk)
RequestFrom
EnterpriseName
LastName

LanguageID(fk)


FirstName

AreaID(fk)


Email

PatLastName


HPhoneNum

PatFirstName


WPhoneNum

MRN


Staff_deptID(fk)

EnterpriseID(fk)


SiteID(fk)


location_building


location_floor


AppointmentTypeID(fk)


DepartmentID(fk)


Comments


Status_id (fk)


AttachementID


GroupID









Site
Programme

SiteID (PK)
GroupID (PK)

Site Name (ie. TGH, Corporate, PMH, TWH)
GroupName
Number_of_Students
Group (y/n)











Department
AppointmentType
Invoice
Language
Appointment

Dept_id (PK)
AppointmentTypeID(PK)
InvoiceID
LanguageID (PK)
ApointmentID

DepartmentName
Appointment Type (ie. Admission, Assessment, Discharge, Family Meeting,
Program)
InvoiceNo
LanguageDescription
InterpreterReqID (Fk)

Date

Interpreter(ID) fk

JobID

Appointment StartTime

Terms

AppointmentEndTime

RequestConfirmDate

Called

AppointementID(Fk)

LanguageID(FK)

ScheduleDate



Appointment Status
Attachments
Job
Group
DepartmentGroups

StatusID(PK)
AttachmentID(PK)
JobID(PK)
GroupID (PK)
departmetngroupID (PK)

Status_description (eg. Cancelled)
LocationofAttachemnt
Project
AreaID(fk)
GroupName

Qty
user


Description

Rate



Thanks for your help with this. I look forward to hearing from you.

Thanks Kindly,
Smriti

"MGFoster" <me@privacy.com> wrote in message
news:O4*****************@newsread1.news.pas.earthl ink.net...
Smriti Dev wrote:
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.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know how to create that query since your table list did not
include an InterpreterAvailability table, nor, its columns. A DDL (Data
Declaration Language description of the table(s)) would be nice; and,
some sample data. I'd guess the InterpreterAvailability table would
hold the start & end date/times of availability of each interpreter -
IOW, a work schedule table.

Re: your listed tables. You have 2 tables Intake Staff & Rehab Staff
that probably would be better as one table w/ a column indicating the
department of the staff member (intake or rehab - or some other
department).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt69qYechKqOuFEgEQKVIwCgrIuiglAbLwXTwqtvzgiyWM 9eSYMAnjOP
WqNz/bBbaiOvTa7rX71vvlJ7
=3eVS
-----END PGP SIGNATURE-----

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Amardeep Verma | last post: by
reply views Thread by YellowFin Announcements | last post: by
7 posts views Thread by Peter Bradley | last post: by
8 posts views Thread by =?Utf-8?B?TWFydGluIFNhY2hz?= | last post: by
4 posts views Thread by Don Calloway | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.