473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 2047
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Amardeep Verma | last post by:
Hi, I have a quick question. Which role/privileges are required before a user can give the statement "GRANT ALL PRIVILEGES"? Thanking you in Advance Have a nice day
3
by: patcho | last post by:
Hello, I have a problem that I was hoping to get some assistance with. I have built a split database (back end with all the tables and a password to protect the information & a front end to link...
1
by: Smriti Dev | last post by:
Hi There, I am designing a database and want to give privileges to a type of user so they only view some fields of a table when they are a certain type in a database. They should be able to...
5
by: Martijn Saly | last post by:
I'd like to test in my script, if it's going to be possible to enable priviliges. If I use this... netscape.security.PrivilegeManager.enablePrivilege("UniversalXPConnect") ....it presents a...
0
by: YellowFin Announcements | last post by:
Introduction Usability and relevance have been identified as the major factors preventing mass adoption of Business Intelligence applications. What we have today are traditional BI tools that...
7
by: Peter Bradley | last post by:
Hi, I'm writing a Web service (the first one I've ever written in anger, by the way) that has to do some AD manipulation and then has to create some directories on some remote servers. Doing...
8
by: =?Utf-8?B?TWFydGluIFNhY2hz?= | last post by:
I have been using MSCONFIG (XP Pro) for four years with no problem. I always use it in Administrator. Last night, when I attempted to go into selective startup mode, I got the following error...
4
by: Don Calloway | last post by:
I'm attempting to implement User-level security on an Access 2003 database in Access 2000 format and have hit a brick wall. I created a Workgroup Information File with myself as admin and added...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.