Adding audits to my database

  • Thread starter Thread starter LMB
  • Start date Start date
L

LMB

Hi Guys,

I use Access 2000. I can't quite figure out how to explain what I want to do so I'll just get started and see if someone can understand.

I have a database with employee names etc. I want to add charting audits for each employee to it. We have several documents our employees chart on and we want to have 100% of the documents filled out. We are doing random audits on each employee so we can report to them the percent time that they fill out each of the items on the document. I have been trying to work it out on paper all day on and off but I am not sure how to set it up.

The only way I can think to do it would be to make a table for each document we are auditing with EmpID in each table to link to the main Employee table but I think proper database design would frown on this. All of the fields in the tables that have the audits would be yes/no. Below is what I think is close to what I need but I am getting stuck on how to link the documents to each employee through my tblEmpAudits

tblEmployee
EmployeeID
Etc......

tblEmpAudits
EmpAuditsID
EmployeeID
AuditTypeID

tblAuditType
AuditTypeID
AuditType

I think the documents that we are auditing would be the audit type (Blue Cards, Vent Sheets, Special Procedure Sheet)

I can't figure out how to make the audit sheets fit in to the whole thing because there are many fields in each of the documents to track.....Blue cards have these items we are looking to see if they are filled in. Other audit sheets have different items to fill in to fill in.

All the fields will be y/n and will be linked to each employee. If you understand what I am trying to do and have a better name for things, that would be wonderful.

These are some of the items we have on our "Blue Cards"

Pt Number
Doctor
Diagnosis
BS Pre
BS Post
Cough
Etc...

Each employee will have 4 of each document audited. The final report would have the employees name at the top of the report and have each document listed with the items from each document showing what % the employee fills out each item..

Example

Linda Loo

Blue Card

Pt Number 50%
Doctor 33%
Diagnosis 100%
etc..

Vent Sheet

Tube Size 75%
Tube Placement 100%
Diagnosis 0%
etc...

Thanks,
Linda
 
setting up a separate table for each audit would mean that every time you
want to audit a new document, you have to create the table, queries, forms,
and reports for it - not a happy solution. suggest the following instead:

tblEmployees
EmpID (primary key)
FirstName
LastName
(other fields that describe an employee)

tblAudits
AuditID (primary key)
AuditName (such as Blue Card, Vent Sheet, Special Procedure Sheet; one
document name equals one record)
(other fields that describe an audit, for example: the date auditing was
implemented, date discontinued, etc.)

tblAuditItems
ItemID (primary key)
AuditID (foreign key from tblAudits)
ItemName (Pt Number, Doctor, Diagnosis, BS Pre, etc; one item name equals
one record)
(other fields that describe an audit item)

tblEmployeeAudits
EmpAuditID (primary key)
EmpID (foreign key from tblEmployees)
AuditID (foreign key from tblAudits)
AuditDate

tblEmpAuditDetails
DetailID (primary key)
EmpAuditID (foreign key from tblEmployeeAudits)
AuditItemID (foreign key form tblAuditItems)
FilledOut (Yes/No field)

don't save any totals or percentages in your tables. you can calculate them
as needed for forms and/or reports. you have different options for the data
entry of audit results, ranging from simple forms that you can create
easily, but will be more labor-intensive for the user to input data, to more
complex forms that will take more work for you to build but can result in
very fast and simple data entry for the user.

this setup also allows you to report the data most any way you want - by
employee, by date or date range, by audit document, or any mix of those.

hth


Hi Guys,

I use Access 2000. I can't quite figure out how to explain what I want to
do so I'll just get started and see if someone can understand.

I have a database with employee names etc. I want to add charting audits
for each employee to it. We have several documents our employees chart on
and we want to have 100% of the documents filled out. We are doing random
audits on each employee so we can report to them the percent time that they
fill out each of the items on the document. I have been trying to work it
out on paper all day on and off but I am not sure how to set it up.

The only way I can think to do it would be to make a table for each document
we are auditing with EmpID in each table to link to the main Employee table
but I think proper database design would frown on this. All of the fields
in the tables that have the audits would be yes/no. Below is what I think
is close to what I need but I am getting stuck on how to link the documents
to each employee through my tblEmpAudits

tblEmployee
EmployeeID
Etc......

tblEmpAudits
EmpAuditsID
EmployeeID
AuditTypeID

tblAuditType
AuditTypeID
AuditType

I think the documents that we are auditing would be the audit type (Blue
Cards, Vent Sheets, Special Procedure Sheet)

I can't figure out how to make the audit sheets fit in to the whole thing
because there are many fields in each of the documents to track.....Blue
cards have these items we are looking to see if they are filled in. Other
audit sheets have different items to fill in to fill in.

All the fields will be y/n and will be linked to each employee. If you
understand what I am trying to do and have a better name for things, that
would be wonderful.

These are some of the items we have on our "Blue Cards"

Pt Number
Doctor
Diagnosis
BS Pre
BS Post
Cough
Etc...

Each employee will have 4 of each document audited. The final report would
have the employees name at the top of the report and have each document
listed with the items from each document showing what % the employee fills
out each item..

Example

Linda Loo

Blue Card

Pt Number 50%
Doctor 33%
Diagnosis 100%
etc..

Vent Sheet

Tube Size 75%
Tube Placement 100%
Diagnosis 0%
etc...

Thanks,
Linda
 
Thanks Tina!

This is going to be a tough one for me. I'll get to work on the tables and for sure will be back to get help on building the queries and forms.

Linda
setting up a separate table for each audit would mean that every time you
want to audit a new document, you have to create the table, queries, forms,
and reports for it - not a happy solution. suggest the following instead:

tblEmployees
EmpID (primary key)
FirstName
LastName
(other fields that describe an employee)

tblAudits
AuditID (primary key)
AuditName (such as Blue Card, Vent Sheet, Special Procedure Sheet; one
document name equals one record)
(other fields that describe an audit, for example: the date auditing was
implemented, date discontinued, etc.)

tblAuditItems
ItemID (primary key)
AuditID (foreign key from tblAudits)
ItemName (Pt Number, Doctor, Diagnosis, BS Pre, etc; one item name equals
one record)
(other fields that describe an audit item)

tblEmployeeAudits
EmpAuditID (primary key)
EmpID (foreign key from tblEmployees)
AuditID (foreign key from tblAudits)
AuditDate

tblEmpAuditDetails
DetailID (primary key)
EmpAuditID (foreign key from tblEmployeeAudits)
AuditItemID (foreign key form tblAuditItems)
FilledOut (Yes/No field)

don't save any totals or percentages in your tables. you can calculate them
as needed for forms and/or reports. you have different options for the data
entry of audit results, ranging from simple forms that you can create
easily, but will be more labor-intensive for the user to input data, to more
complex forms that will take more work for you to build but can result in
very fast and simple data entry for the user.

this setup also allows you to report the data most any way you want - by
employee, by date or date range, by audit document, or any mix of those.

hth


Hi Guys,

I use Access 2000. I can't quite figure out how to explain what I want to
do so I'll just get started and see if someone can understand.

I have a database with employee names etc. I want to add charting audits
for each employee to it. We have several documents our employees chart on
and we want to have 100% of the documents filled out. We are doing random
audits on each employee so we can report to them the percent time that they
fill out each of the items on the document. I have been trying to work it
out on paper all day on and off but I am not sure how to set it up.

The only way I can think to do it would be to make a table for each document
we are auditing with EmpID in each table to link to the main Employee table
but I think proper database design would frown on this. All of the fields
in the tables that have the audits would be yes/no. Below is what I think
is close to what I need but I am getting stuck on how to link the documents
to each employee through my tblEmpAudits

tblEmployee
EmployeeID
Etc......

tblEmpAudits
EmpAuditsID
EmployeeID
AuditTypeID

tblAuditType
AuditTypeID
AuditType

I think the documents that we are auditing would be the audit type (Blue
Cards, Vent Sheets, Special Procedure Sheet)

I can't figure out how to make the audit sheets fit in to the whole thing
because there are many fields in each of the documents to track.....Blue
cards have these items we are looking to see if they are filled in. Other
audit sheets have different items to fill in to fill in.

All the fields will be y/n and will be linked to each employee. If you
understand what I am trying to do and have a better name for things, that
would be wonderful.

These are some of the items we have on our "Blue Cards"

Pt Number
Doctor
Diagnosis
BS Pre
BS Post
Cough
Etc...

Each employee will have 4 of each document audited. The final report would
have the employees name at the top of the report and have each document
listed with the items from each document showing what % the employee fills
out each item..

Example

Linda Loo

Blue Card

Pt Number 50%
Doctor 33%
Diagnosis 100%
etc..

Vent Sheet

Tube Size 75%
Tube Placement 100%
Diagnosis 0%
etc...

Thanks,
Linda
 
I hit my first snag. When creating my tblAuditItems and typing in the ItemName (PtNumber,Doctor,Diagnosis,Treatments) I am confused because the Blue cards have these items on them to fill in but the Ventilator sheets will have different items on them to fill in like (Settings, Cuff Pressure, Tube Placement, etc) the items are related to a specific document.

Thanks,
Linda
setting up a separate table for each audit would mean that every time you
want to audit a new document, you have to create the table, queries, forms,
and reports for it - not a happy solution. suggest the following instead:

tblEmployees
EmpID (primary key)
FirstName
LastName
(other fields that describe an employee)

tblAudits
AuditID (primary key)
AuditName (such as Blue Card, Vent Sheet, Special Procedure Sheet; one
document name equals one record)
(other fields that describe an audit, for example: the date auditing was
implemented, date discontinued, etc.)

tblAuditItems
ItemID (primary key)
AuditID (foreign key from tblAudits)
ItemName (Pt Number, Doctor, Diagnosis, BS Pre, etc; one item name equals
one record)
(other fields that describe an audit item)

tblEmployeeAudits
EmpAuditID (primary key)
EmpID (foreign key from tblEmployees)
AuditID (foreign key from tblAudits)
AuditDate

tblEmpAuditDetails
DetailID (primary key)
EmpAuditID (foreign key from tblEmployeeAudits)
AuditItemID (foreign key form tblAuditItems)
FilledOut (Yes/No field)

don't save any totals or percentages in your tables. you can calculate them
as needed for forms and/or reports. you have different options for the data
entry of audit results, ranging from simple forms that you can create
easily, but will be more labor-intensive for the user to input data, to more
complex forms that will take more work for you to build but can result in
very fast and simple data entry for the user.

this setup also allows you to report the data most any way you want - by
employee, by date or date range, by audit document, or any mix of those.

hth
 
if you're following the table design i gave you, then each record in
tblAuditItems is linked to a specific record in tblAudits. it is a
one-to-many (tblAudits to tblAuditItems) relationship. it's hard to make
data line up in table examples, but here's a sample that hopefully will line
up well enough to make sense to you:

tblAudits
AuditID AuditName
1 Blue Card
2 Vent Sheet
3 Special Procedure Sheet

tblAuditItems
ItemID AuditID ItemName
1 1 Pt Number
2 1 Doctor
3 1 Diagnosis
4 1 BS Pre
5 1 BS Post
6 1 Cough
7 2 Pt Number
8 2 Technician
9 2 StartTime
10 2 EndTime
11 3 Pt Number
12 3 Procedure
13 3 Authorization

of course, i made up the ItemName values for Vent Sheet and Special
Procedure Sheet, but as you can see, each item is linked directly to a
specific audit. remember, tblAuditItems is simply a list of each item for
each audit - it is *not* the audit results table.

hth


I hit my first snag. When creating my tblAuditItems and typing in the
ItemName (PtNumber,Doctor,Diagnosis,Treatments) I am confused because the
Blue cards have these items on them to fill in but the Ventilator sheets
will have different items on them to fill in like (Settings, Cuff Pressure,
Tube Placement, etc) the items are related to a specific document.

Thanks,
Linda
setting up a separate table for each audit would mean that every time you
want to audit a new document, you have to create the table, queries, forms,
and reports for it - not a happy solution. suggest the following instead:

tblEmployees
EmpID (primary key)
FirstName
LastName
(other fields that describe an employee)

tblAudits
AuditID (primary key)
AuditName (such as Blue Card, Vent Sheet, Special Procedure Sheet; one
document name equals one record)
(other fields that describe an audit, for example: the date auditing was
implemented, date discontinued, etc.)

tblAuditItems
ItemID (primary key)
AuditID (foreign key from tblAudits)
ItemName (Pt Number, Doctor, Diagnosis, BS Pre, etc; one item name equals
one record)
(other fields that describe an audit item)

tblEmployeeAudits
EmpAuditID (primary key)
EmpID (foreign key from tblEmployees)
AuditID (foreign key from tblAudits)
AuditDate

tblEmpAuditDetails
DetailID (primary key)
EmpAuditID (foreign key from tblEmployeeAudits)
AuditItemID (foreign key form tblAuditItems)
FilledOut (Yes/No field)

don't save any totals or percentages in your tables. you can calculate them
as needed for forms and/or reports. you have different options for the data
entry of audit results, ranging from simple forms that you can create
easily, but will be more labor-intensive for the user to input data, to more
complex forms that will take more work for you to build but can result in
very fast and simple data entry for the user.

this setup also allows you to report the data most any way you want - by
employee, by date or date range, by audit document, or any mix of those.

hth
 
Tina,

Thanks so much for taking this amount of time. That's exactly what I have written out on my paper so at least I must be understanding something but I still don't see how I will be able to establish a relationship to an employee from here or how the y/n field from the tbl empAuditDetails will be entered when filling out the form for each item on each Audit (blue card, vent sheet etc...) Each employee will be audited 4 times for each Audit. But I'm just going to have faith and go on from here, I am not sure of what to do next..Probably join a couple of tables in a query then join that query to another couple of tables?

I just want to make sure I have my tables set up correctly. I don't know how else to describe my drawing but here goes....I have on my graph paper all the tables lined up and the Primary Keys are linked with Foreign Keys like this.

From tblEmployee pk(EmpID)---fk(EmpID) in the tblEmployeeAudits, From tblEmployeeAudits pk(EmployeeAuditsID)----fk(EmployeeAuditsID) in the tblEmployeeAuditDetails,From tblEmployeeAuditDetails fk(AuditItemID)---pk(AuditItemID) in tblAuditItems, From tblAuditItems fk(AuditID)---pk(AuditID) in the tblAudits.

Is this right?

Thanks,
Linda
(e-mail address removed)
if you're following the table design i gave you, then each record in
tblAuditItems is linked to a specific record in tblAudits. it is a
one-to-many (tblAudits to tblAuditItems) relationship. it's hard to make
data line up in table examples, but here's a sample that hopefully will line
up well enough to make sense to you:

tblAudits
AuditID AuditName
1 Blue Card
2 Vent Sheet
3 Special Procedure Sheet

tblAuditItems
ItemID AuditID ItemName
1 1 Pt Number
2 1 Doctor
3 1 Diagnosis
4 1 BS Pre
5 1 BS Post
6 1 Cough
7 2 Pt Number
8 2 Technician
9 2 StartTime
10 2 EndTime
11 3 Pt Number
12 3 Procedure
13 3 Authorization

of course, i made up the ItemName values for Vent Sheet and Special
Procedure Sheet, but as you can see, each item is linked directly to a
specific audit. remember, tblAuditItems is simply a list of each item for
each audit - it is *not* the audit results table.

hth
 
Linda, is the email address (that you posted in your last message) valid? if
so, i can set up a quick demo that you can look at and learn from, and send
it to you. at least the tables/relationships. if i have time, i'll include a
rough data entry form - just enough to demonstrate one way it can be set up.
that will be quicker than back-and-forth posting and get you started in the
right direction; though you'll still want to come to the newsgroups for help
as you continue with your project.

(btw, it's usually a mistake to post a valid email address in a newsgroup
post. there are data miners that sweep newsgroups for just that info - and
you're likely to get spammed to death. too late to do anything about it now,
but you'll know for the future. you can "disguise" an email address by
adding fake characters to it, if you really need to post it.)

Tina,

Thanks so much for taking this amount of time. That's exactly what I have
written out on my paper so at least I must be understanding something but I
still don't see how I will be able to establish a relationship to an
employee from here or how the y/n field from the tbl empAuditDetails will be
entered when filling out the form for each item on each Audit (blue card,
vent sheet etc...) Each employee will be audited 4 times for each Audit.
But I'm just going to have faith and go on from here, I am not sure of what
to do next..Probably join a couple of tables in a query then join that query
to another couple of tables?

I just want to make sure I have my tables set up correctly. I don't know
how else to describe my drawing but here goes....I have on my graph paper
all the tables lined up and the Primary Keys are linked with Foreign Keys
like this.

From tblEmployee pk(EmpID)---fk(EmpID) in the tblEmployeeAudits, From
tblEmployeeAudits pk(EmployeeAuditsID)----fk(EmployeeAuditsID) in the
tblEmployeeAuditDetails,From tblEmployeeAuditDetails
fk(AuditItemID)---pk(AuditItemID) in tblAuditItems, From tblAuditItems
fk(AuditID)---pk(AuditID) in the tblAudits.

Is this right?

Thanks,
Linda
(e-mail address removed)
if you're following the table design i gave you, then each record in
tblAuditItems is linked to a specific record in tblAudits. it is a
one-to-many (tblAudits to tblAuditItems) relationship. it's hard to make
data line up in table examples, but here's a sample that hopefully will line
up well enough to make sense to you:

tblAudits
AuditID AuditName
1 Blue Card
2 Vent Sheet
3 Special Procedure Sheet

tblAuditItems
ItemID AuditID ItemName
1 1 Pt Number
2 1 Doctor
3 1 Diagnosis
4 1 BS Pre
5 1 BS Post
6 1 Cough
7 2 Pt Number
8 2 Technician
9 2 StartTime
10 2 EndTime
11 3 Pt Number
12 3 Procedure
13 3 Authorization

of course, i made up the ItemName values for Vent Sheet and Special
Procedure Sheet, but as you can see, each item is linked directly to a
specific audit. remember, tblAuditItems is simply a list of each item for
each audit - it is *not* the audit results table.

hth
 
Thanks, Tina, that would really be nice of you. Yes, it's my spam mail address so it's ok if I get spammed in there. I thought they could only get it from the reply address in my account settings though but a good thing to know.

Linda



Linda, is the email address (that you posted in your last message) valid? if
so, i can set up a quick demo that you can look at and learn from, and send
it to you. at least the tables/relationships. if i have time, i'll include a
rough data entry form - just enough to demonstrate one way it can be set up.
that will be quicker than back-and-forth posting and get you started in the
right direction; though you'll still want to come to the newsgroups for help
as you continue with your project.

(btw, it's usually a mistake to post a valid email address in a newsgroup
post. there are data miners that sweep newsgroups for just that info - and
you're likely to get spammed to death. too late to do anything about it now,
but you'll know for the future. you can "disguise" an email address by
adding fake characters to it, if you really need to post it.)
 
ok, on its' way to you shortly. details will be in the email text. (btw,
suggest you post using plain text rather than HTML in future visits to the
newsgroups.)


Thanks, Tina, that would really be nice of you. Yes, it's my spam mail
address so it's ok if I get spammed in there. I thought they could only get
it from the reply address in my account settings though but a good thing to
know.

Linda



Linda, is the email address (that you posted in your last message) valid? if
so, i can set up a quick demo that you can look at and learn from, and send
it to you. at least the tables/relationships. if i have time, i'll include a
rough data entry form - just enough to demonstrate one way it can be set up.
that will be quicker than back-and-forth posting and get you started in the
right direction; though you'll still want to come to the newsgroups for help
as you continue with your project.

(btw, it's usually a mistake to post a valid email address in a newsgroup
post. there are data miners that sweep newsgroups for just that info - and
you're likely to get spammed to death. too late to do anything about it now,
but you'll know for the future. you can "disguise" an email address by
adding fake characters to it, if you really need to post it.)
 
No wonder my posts are always so big, I couldn't figure it out. I kept
deleting the bottom halves with no change in kb size and everyone else's
were 2 or 3 kb and mine are always huge. I originally set my options up for
plain text in newsgroups and html in e-mail...I have no clue how it got
changed....brother, what a dope. Is this better?

Thanks!

Linda
 
yes, it's better, and you're welcome :)


LMB said:
No wonder my posts are always so big, I couldn't figure it out. I kept
deleting the bottom halves with no change in kb size and everyone else's
were 2 or 3 kb and mine are always huge. I originally set my options up for
plain text in newsgroups and html in e-mail...I have no clue how it got
changed....brother, what a dope. Is this better?

Thanks!

Linda
Click to expand...
 
Back
Top