Table design problem?

I am building an employee production db at work. The problem I am having is
with the relationships and maybe my table design. I have one table for
employee, Correspondence Received and Correspondence Completed. These
employees receive and complete correspondence from taxpayers each day. There
are 50 different audit types the department would like to track. With each
audit type, there are 15 fields that may are may not apply. So, what I have
currently design, the employee can have multiple entries into the database
"in a day" if working on more than one audit type. What I need is for them to
be able to logg any corresp rec'd or completed (that they did for that day)
into one form. Putting it all together is confusing me. hope this makes
sense. I have been trying to resolve this issue for months and cannot seem to
come up with a solution. Any help would be truly appreciated. I have posted
this question 10/20 but the solution hasn't helped. My tables are as follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and Correspondence
Completed (not sure this is necessary because I use TaxType/audit type for
combo box on forms)

Thank you

It's all very confusing as you have presented it! The Correspondence
Received/Assigned table looks like a list of activities and you want to
record how many times each activity was done. What does this have to do with
correspondence? What are the 15 fields you want to record for each audit
type? In the Correspondence Completed table it looks like you have
correspondence that is either Billing or Assessment and your trying to
record how many of each have been completed??? What is DateCompleted? Is it
the date you completed a certain correspondence or the recording date for
the count of correspondence you completed. The basis of your database
appears to be pretty complex to where you need to write much more detail to
get any help!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.



"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am having is
with the relationships and maybe my table design. I have one table for
employee, Correspondence Received and Correspondence Completed. These
employees receive and complete correspondence from taxpayers each day.
There
are 50 different audit types the department would like to track. With each
audit type, there are 15 fields that may are may not apply. So, what I
have
currently design, the employee can have multiple entries into the database
"in a day" if working on more than one audit type. What I need is for them
to
be able to logg any corresp rec'd or completed (that they did for that
day)
into one form. Putting it all together is confusing me. hope this makes
sense. I have been trying to resolve this issue for months and cannot seem
to
come up with a solution. Any help would be truly appreciated. I have
posted
this question 10/20 but the solution hasn't helped. My tables are as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit type for
combo box on forms)

Thank you

I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify this,
when you receive a bill and do not agree or have a different explaination,
and respond to this bill, this is correspondence received. The correspondence
we are working is just that. The department would like to track how many are
received and completed by an employee each day. We are currently recording
this information into a excel spreadsheet, but with 47 employees and the many
types of audit types, the workbooks are becoming huge. I hope this simplifies
things.

"PC Datasheet" wrote:

It's all very confusing as you have presented it! The Correspondence
Received/Assigned table looks like a list of activities and you want to
record how many times each activity was done. What does this have to do with
correspondence? What are the 15 fields you want to record for each audit
type? In the Correspondence Completed table it looks like you have
correspondence that is either Billing or Assessment and your trying to
record how many of each have been completed??? What is DateCompleted? Is it
the date you completed a certain correspondence or the recording date for
the count of correspondence you completed. The basis of your database
appears to be pretty complex to where you need to write much more detail to
get any help!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com

If you don't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Remember that a lone man built the Ark. A large group of professionals built
the Titanic.



"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am having is
with the relationships and maybe my table design. I have one table for
employee, Correspondence Received and Correspondence Completed. These
employees receive and complete correspondence from taxpayers each day.
There
are 50 different audit types the department would like to track. With each
audit type, there are 15 fields that may are may not apply. So, what I
have
currently design, the employee can have multiple entries into the database
"in a day" if working on more than one audit type. What I need is for them
to
be able to logg any corresp rec'd or completed (that they did for that
day)
into one form. Putting it all together is confusing me. hope this makes
sense. I have been trying to resolve this issue for months and cannot seem
to
come up with a solution. Any help would be truly appreciated. I have
posted
this question 10/20 but the solution hasn't helped. My tables are as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit type for
combo box on forms)

Thank you

Melissa -

It appears that you want the employee to enter each correspondence into a
form, and to let the employee select the type of "audit" that is associated
with each corresondence that is done? Is this essentially what you seek to
do?

Or is the "audit" type already associated with each correspondence type, and
you just want to let the employee select the correspondence type, entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the EXCEL
spreadsheet so that we can see the types of data you're capturing per row.
What data will the employee specifically have "in hand" when he/she is ready
to enter a correspondence that is done, and what will the employee need to
select or enter additionally while entering that "in hand" data?
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify
this,
when you receive a bill and do not agree or have a different explaination,
and respond to this bill, this is correspondence received. The
correspondence
we are working is just that. The department would like to track how many
are
received and completed by an employee each day. We are currently recording
this information into a excel spreadsheet, but with 47 employees and the
many
types of audit types, the workbooks are becoming huge. I hope this
simplifies
things.

"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am having
is
with the relationships and maybe my table design. I have one table for
employee, Correspondence Received and Correspondence Completed. These
employees receive and complete correspondence from taxpayers each day.
There
are 50 different audit types the department would like to track. With
each
audit type, there are 15 fields that may are may not apply. So, what I
have
currently design, the employee can have multiple entries into the
database
"in a day" if working on more than one audit type. What I need is for
them
to
be able to logg any corresp rec'd or completed (that they did for that
day)
into one form. Putting it all together is confusing me. hope this makes
sense. I have been trying to resolve this issue for months and cannot
seem
to
come up with a solution. Any help would be truly appreciated. I have
posted
this question 10/20 but the solution hasn't helped. My tables are as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit type
for
combo box on forms)

Thank you

Ken, you get what I am saying and I so appreciate that. The employee would
select the type of Audit he/she is receiving/ and worked for each day. They
would complete their production each day.
The Audit type can be received by 8 different sources; Files Rec'd, a phone
call; email etc. The department has over 50 types of audit that the division
currently works. (This is a billing/assessment dept). Here is one row of
from the datasheet. It did not paste well but, it reads, AgentID 1 on
10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit type) 5
(additional files recd via phone) then an addition 5 (referrals). We also
want to capture their ending inventory by audit type. Same example would also
apply to the Corresp completed table.

AgentID DateReceived TaxAuditType FilesReceived Phone
Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks ServCtr/Email/Phone AG
Email Other Agents Activity Notices Bankruptcy
Email TaxAppeal TPSReferrals DayEndingInventory
1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0


I am thinking in essense, that one employee can have multiple sales in one
day and those sales can be different products. (This is just for a
comparision)

Thanks for any help you can give me. I am pretty good at Access and have
built many databases for the dept, but this one has me stumped.


"Ken Snell [MVP]" wrote:

Melissa -

It appears that you want the employee to enter each correspondence into a
form, and to let the employee select the type of "audit" that is associated
with each corresondence that is done? Is this essentially what you seek to
do?

Or is the "audit" type already associated with each correspondence type, and
you just want to let the employee select the correspondence type, entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the EXCEL
spreadsheet so that we can see the types of data you're capturing per row.
What data will the employee specifically have "in hand" when he/she is ready
to enter a correspondence that is done, and what will the employee need to
select or enter additionally while entering that "in hand" data?
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify
this,
when you receive a bill and do not agree or have a different explaination,
and respond to this bill, this is correspondence received. The
correspondence
we are working is just that. The department would like to track how many
are
received and completed by an employee each day. We are currently recording
this information into a excel spreadsheet, but with 47 employees and the
many
types of audit types, the workbooks are becoming huge. I hope this
simplifies
things.

"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am having
is
with the relationships and maybe my table design. I have one table for
employee, Correspondence Received and Correspondence Completed. These
employees receive and complete correspondence from taxpayers each day.
There
are 50 different audit types the department would like to track. With
each
audit type, there are 15 fields that may are may not apply. So, what I
have
currently design, the employee can have multiple entries into the
database
"in a day" if working on more than one audit type. What I need is for
them
to
be able to logg any corresp rec'd or completed (that they did for that
day)
into one form. Putting it all together is confusing me. hope this makes
sense. I have been trying to resolve this issue for months and cannot
seem
to
come up with a solution. Any help would be truly appreciated. I have
posted
this question 10/20 but the solution hasn't helped. My tables are as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit type
for
combo box on forms)

Thank you

I can think of a few ways to do this... each with its own complications and
validation needs.

But, let's try something "easy" in terms of setup and design at the moment.

You'll want three tables:

tblEmployees
EmpID ( Primary Key )
EmpName
(etc.)

tblCorrespondenceTypes
CorrID ( Primary Key )
CorrName

tblEmpCorrWork
EmpID ( composite primary key with CorrID and WorkDate )
CorrID ( composite primary key with EmpID and WorkDate )
WorkDate ( composite primary key with EmpID and CorrID )
WorkQuantity

In the third table, EmpID and CorrID would be foreign keys back to the
"parent" tables.

Then, for data entry, I'd create a form. Set the form up as "Continuous
Forms" view. Also set its Data Entry property to Yes (we'll use this form
just for data entry right now). Make the form's Record Source be a query
that is based on tblEmpCorrWork table. All four fields from that table
should be in the query.

In the form's FormHeader section, I'd put a combo box and textbox. The combo
box would display all the employees so that the employee can select his/her
name; the combo box would be bound to the EmpID field in the form's
RecordSource. The textbox is where the work date goes -- can be
automatically entered or can be typed in by employee, or both; the textbox
would be bound to the WorkDate field in the form's RecordSource.

In the form's Detail section, I'd put a combo box that displays all the
correspondence types; this combo box would be bound to the CorrID field in
the form's RecordSource. I'd then put a textbox to allow entry of the number
of items for that specific correspondence type; this textbox would be bound
to the WorkQuantity field in the form's RecordSource.

This setup will not prohibit an employee from selecting the same
correspondence type twice for the same date, but the composite primary key
in the table will prevent the saving of any data where that is done for that
record.

But this should get you started.....
--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
Ken, you get what I am saying and I so appreciate that. The employee would
select the type of Audit he/she is receiving/ and worked for each day.
They
would complete their production each day.
The Audit type can be received by 8 different sources; Files Rec'd, a
phone
call; email etc. The department has over 50 types of audit that the
division
currently works. (This is a billing/assessment dept). Here is one row of
from the datasheet. It did not paste well but, it reads, AgentID 1 on
10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit
type) 5
(additional files recd via phone) then an addition 5 (referrals). We also
want to capture their ending inventory by audit type. Same example would
also
apply to the Corresp completed table.

AgentID DateReceived TaxAuditType FilesReceived Phone
Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks
ServCtr/Email/Phone AG
Email Other Agents Activity Notices Bankruptcy
Email TaxAppeal TPSReferrals DayEndingInventory
1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0


I am thinking in essense, that one employee can have multiple sales in one
day and those sales can be different products. (This is just for a
comparision)

Thanks for any help you can give me. I am pretty good at Access and have
built many databases for the dept, but this one has me stumped.


"Ken Snell [MVP]" wrote:

Melissa -

It appears that you want the employee to enter each correspondence into a
form, and to let the employee select the type of "audit" that is
associated
with each corresondence that is done? Is this essentially what you seek
to
do?

Or is the "audit" type already associated with each correspondence type,
and
you just want to let the employee select the correspondence type,
entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the
EXCEL
spreadsheet so that we can see the types of data you're capturing per
row.
What data will the employee specifically have "in hand" when he/she is
ready
to enter a correspondence that is done, and what will the employee need
to
select or enter additionally while entering that "in hand" data?
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify
this,
when you receive a bill and do not agree or have a different
explaination,
and respond to this bill, this is correspondence received. The
correspondence
we are working is just that. The department would like to track how
many
are
received and completed by an employee each day. We are currently
recording
this information into a excel spreadsheet, but with 47 employees and
the
many
types of audit types, the workbooks are becoming huge. I hope this
simplifies
things.

"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am
having
is
with the relationships and maybe my table design. I have one table
for
employee, Correspondence Received and Correspondence Completed.
These
employees receive and complete correspondence from taxpayers each
day.
There
are 50 different audit types the department would like to track.
With
each
audit type, there are 15 fields that may are may not apply. So, what
I
have
currently design, the employee can have multiple entries into the
database
"in a day" if working on more than one audit type. What I need is
for
them
to
be able to logg any corresp rec'd or completed (that they did for
that
day)
into one form. Putting it all together is confusing me. hope this
makes
sense. I have been trying to resolve this issue for months and
cannot
seem
to
come up with a solution. Any help would be truly appreciated. I have
posted
this question 10/20 but the solution hasn't helped. My tables are as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit
type
for
combo box on forms)

Thank you

Thanks for your help Ken. I have completed doing the suggestions you have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the table,
then to build the form based on a qry of those fields. In this table, there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table? I put
these fields into the table 3 with the EmpID and CorrID being the foreign
keys back to the parent. What is happening is it is associating each agent
with a particular audit code when each agent may work many different types of
audits. Thanks again for your help

"Ken Snell [MVP]" wrote:

I can think of a few ways to do this... each with its own complications and
validation needs.

But, let's try something "easy" in terms of setup and design at the moment.

You'll want three tables:

tblEmployees
EmpID ( Primary Key )
EmpName
(etc.)

tblCorrespondenceTypes
CorrID ( Primary Key )
CorrName

tblEmpCorrWork
EmpID ( composite primary key with CorrID and WorkDate )
CorrID ( composite primary key with EmpID and WorkDate )
WorkDate ( composite primary key with EmpID and CorrID )
WorkQuantity

In the third table, EmpID and CorrID would be foreign keys back to the
"parent" tables.

Then, for data entry, I'd create a form. Set the form up as "Continuous
Forms" view. Also set its Data Entry property to Yes (we'll use this form
just for data entry right now). Make the form's Record Source be a query
that is based on tblEmpCorrWork table. All four fields from that table
should be in the query.

In the form's FormHeader section, I'd put a combo box and textbox. The combo
box would display all the employees so that the employee can select his/her
name; the combo box would be bound to the EmpID field in the form's
RecordSource. The textbox is where the work date goes -- can be
automatically entered or can be typed in by employee, or both; the textbox
would be bound to the WorkDate field in the form's RecordSource.

In the form's Detail section, I'd put a combo box that displays all the
correspondence types; this combo box would be bound to the CorrID field in
the form's RecordSource. I'd then put a textbox to allow entry of the number
of items for that specific correspondence type; this textbox would be bound
to the WorkQuantity field in the form's RecordSource.

This setup will not prohibit an employee from selecting the same
correspondence type twice for the same date, but the composite primary key
in the table will prevent the saving of any data where that is done for that
record.

But this should get you started.....
--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
Ken, you get what I am saying and I so appreciate that. The employee would
select the type of Audit he/she is receiving/ and worked for each day.
They
would complete their production each day.
The Audit type can be received by 8 different sources; Files Rec'd, a
phone
call; email etc. The department has over 50 types of audit that the
division
currently works. (This is a billing/assessment dept). Here is one row of
from the datasheet. It did not paste well but, it reads, AgentID 1 on
10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit
type) 5
(additional files recd via phone) then an addition 5 (referrals). We also
want to capture their ending inventory by audit type. Same example would
also
apply to the Corresp completed table.

AgentID DateReceived TaxAuditType FilesReceived Phone
Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks
ServCtr/Email/Phone AG
Email Other Agents Activity Notices Bankruptcy
Email TaxAppeal TPSReferrals DayEndingInventory
1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0


I am thinking in essense, that one employee can have multiple sales in one
day and those sales can be different products. (This is just for a
comparision)

Thanks for any help you can give me. I am pretty good at Access and have
built many databases for the dept, but this one has me stumped.


"Ken Snell [MVP]" wrote:

Melissa -

It appears that you want the employee to enter each correspondence into a
form, and to let the employee select the type of "audit" that is
associated
with each corresondence that is done? Is this essentially what you seek
to
do?

Or is the "audit" type already associated with each correspondence type,
and
you just want to let the employee select the correspondence type,
entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the
EXCEL
spreadsheet so that we can see the types of data you're capturing per
row.
What data will the employee specifically have "in hand" when he/she is
ready
to enter a correspondence that is done, and what will the employee need
to
select or enter additionally while entering that "in hand" data?
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to simplify
this,
when you receive a bill and do not agree or have a different
explaination,
and respond to this bill, this is correspondence received. The
correspondence
we are working is just that. The department would like to track how
many
are
received and completed by an employee each day. We are currently
recording
this information into a excel spreadsheet, but with 47 employees and
the
many
types of audit types, the workbooks are becoming huge. I hope this
simplifies
things.

"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am
having
is
with the relationships and maybe my table design. I have one table
for
employee, Correspondence Received and Correspondence Completed.
These
employees receive and complete correspondence from taxpayers each
day.
There
are 50 different audit types the department would like to track.
With
each
audit type, there are 15 fields that may are may not apply. So, what
I
have
currently design, the employee can have multiple entries into the
database
"in a day" if working on more than one audit type. What I need is
for
them
to
be able to logg any corresp rec'd or completed (that they did for
that
day)
into one form. Putting it all together is confusing me. hope this
makes
sense. I have been trying to resolve this issue for months and
cannot
seem
to
come up with a solution. Any help would be truly appreciated. I have
posted
this question 10/20 but the solution hasn't helped. My tables are as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit
type
for
combo box on forms)

Thank you

I need to better understand the relationship of the "sort" fields to the
CorrID entity (which I am using as the identity of a type of correspondence
from your earlier list). What are these fields, how are they used, what type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions you have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the table,
then to build the form based on a qry of those fields. In this table,
there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table? I put
these fields into the table 3 with the EmpID and CorrID being the foreign
keys back to the parent. What is happening is it is associating each agent
with a particular audit code when each agent may work many different types
of
audits. Thanks again for your help

"Ken Snell [MVP]" wrote:

I can think of a few ways to do this... each with its own complications
and
validation needs.

But, let's try something "easy" in terms of setup and design at the
moment.

You'll want three tables:

tblEmployees
EmpID ( Primary Key )
EmpName
(etc.)

tblCorrespondenceTypes
CorrID ( Primary Key )
CorrName

tblEmpCorrWork
EmpID ( composite primary key with CorrID and WorkDate )
CorrID ( composite primary key with EmpID and WorkDate )
WorkDate ( composite primary key with EmpID and CorrID )
WorkQuantity

In the third table, EmpID and CorrID would be foreign keys back to the
"parent" tables.

Then, for data entry, I'd create a form. Set the form up as "Continuous
Forms" view. Also set its Data Entry property to Yes (we'll use this form
just for data entry right now). Make the form's Record Source be a query
that is based on tblEmpCorrWork table. All four fields from that table
should be in the query.

In the form's FormHeader section, I'd put a combo box and textbox. The
combo
box would display all the employees so that the employee can select
his/her
name; the combo box would be bound to the EmpID field in the form's
RecordSource. The textbox is where the work date goes -- can be
automatically entered or can be typed in by employee, or both; the
textbox
would be bound to the WorkDate field in the form's RecordSource.

In the form's Detail section, I'd put a combo box that displays all the
correspondence types; this combo box would be bound to the CorrID field
in
the form's RecordSource. I'd then put a textbox to allow entry of the
number
of items for that specific correspondence type; this textbox would be
bound
to the WorkQuantity field in the form's RecordSource.

This setup will not prohibit an employee from selecting the same
correspondence type twice for the same date, but the composite primary
key
in the table will prevent the saving of any data where that is done for
that
record.

But this should get you started.....
--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
Ken, you get what I am saying and I so appreciate that. The employee
would
select the type of Audit he/she is receiving/ and worked for each day.
They
would complete their production each day.
The Audit type can be received by 8 different sources; Files Rec'd, a
phone
call; email etc. The department has over 50 types of audit that the
division
currently works. (This is a billing/assessment dept). Here is one row
of
from the datasheet. It did not paste well but, it reads, AgentID 1 on
10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit
type) 5
(additional files recd via phone) then an addition 5 (referrals). We
also
want to capture their ending inventory by audit type. Same example
would
also
apply to the Corresp completed table.

AgentID DateReceived TaxAuditType FilesReceived Phone
Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks
ServCtr/Email/Phone AG
Email Other Agents Activity Notices Bankruptcy
Email TaxAppeal TPSReferrals DayEndingInventory
1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0


I am thinking in essense, that one employee can have multiple sales in
one
day and those sales can be different products. (This is just for a
comparision)

Thanks for any help you can give me. I am pretty good at Access and
have
built many databases for the dept, but this one has me stumped.


"Ken Snell [MVP]" wrote:

Melissa -

It appears that you want the employee to enter each correspondence
into a
form, and to let the employee select the type of "audit" that is
associated
with each corresondence that is done? Is this essentially what you
seek
to
do?

Or is the "audit" type already associated with each correspondence
type,
and
you just want to let the employee select the correspondence type,
entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the
EXCEL
spreadsheet so that we can see the types of data you're capturing per
row.
What data will the employee specifically have "in hand" when he/she is
ready
to enter a correspondence that is done, and what will the employee
need
to
select or enter additionally while entering that "in hand" data?
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to
simplify
this,
when you receive a bill and do not agree or have a different
explaination,
and respond to this bill, this is correspondence received. The
correspondence
we are working is just that. The department would like to track how
many
are
received and completed by an employee each day. We are currently
recording
this information into a excel spreadsheet, but with 47 employees and
the
many
types of audit types, the workbooks are becoming huge. I hope this
simplifies
things.

"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am
having
is
with the relationships and maybe my table design. I have one
table
for
employee, Correspondence Received and Correspondence Completed.
These
employees receive and complete correspondence from taxpayers each
day.
There
are 50 different audit types the department would like to track.
With
each
audit type, there are 15 fields that may are may not apply. So,
what
I
have
currently design, the employee can have multiple entries into the
database
"in a day" if working on more than one audit type. What I need is
for
them
to
be able to logg any corresp rec'd or completed (that they did for
that
day)
into one form. Putting it all together is confusing me. hope this
makes
sense. I have been trying to resolve this issue for months and
cannot
seem
to
come up with a solution. Any help would be truly appreciated. I
have
posted
this question 10/20 but the solution hasn't helped. My tables are
as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to
CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit
type
for
combo box on forms)

Thank you

I related the EmpID and CorrID to the parent tables. I then made a combo box
on the form for both of these fields. The Correspondence Name consists of all
50 different types of correspondence(audit types) received which will be
chosen from a drop down list. Now grant it, no one employee will do 50
different audit types in a day (or work the correp from all 50) but, they
will at the very minimun do 3 different types. And each of those types
usually comes in through different avenues. A audit type would be for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc. Each
tax type can have different types of bills. That is why there is so many.

In the third table EmpCorrWork I did not include the EmpName or CorrName
since the EmpID and CorrId is already linked to these tables. In my form I
placed the EmpName and Date in the FormHeader and the detail section I put
the different sorts of how they would receive the files. Phone, FileRm etc.
The form would not allow me to tab down to any of my fields within the detail
section after I enter the date. Should I use a subform for the detail section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields to the
CorrID entity (which I am using as the identity of a type of correspondence
from your earlier list). What are these fields, how are they used, what type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions you have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the table,
then to build the form based on a qry of those fields. In this table,
there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table? I put
these fields into the table 3 with the EmpID and CorrID being the foreign
keys back to the parent. What is happening is it is associating each agent
with a particular audit code when each agent may work many different types
of
audits. Thanks again for your help

"Ken Snell [MVP]" wrote:

I can think of a few ways to do this... each with its own complications
and
validation needs.

But, let's try something "easy" in terms of setup and design at the
moment.

You'll want three tables:

tblEmployees
EmpID ( Primary Key )
EmpName
(etc.)

tblCorrespondenceTypes
CorrID ( Primary Key )
CorrName

tblEmpCorrWork
EmpID ( composite primary key with CorrID and WorkDate )
CorrID ( composite primary key with EmpID and WorkDate )
WorkDate ( composite primary key with EmpID and CorrID )
WorkQuantity

In the third table, EmpID and CorrID would be foreign keys back to the
"parent" tables.

Then, for data entry, I'd create a form. Set the form up as "Continuous
Forms" view. Also set its Data Entry property to Yes (we'll use this form
just for data entry right now). Make the form's Record Source be a query
that is based on tblEmpCorrWork table. All four fields from that table
should be in the query.

In the form's FormHeader section, I'd put a combo box and textbox. The
combo
box would display all the employees so that the employee can select
his/her
name; the combo box would be bound to the EmpID field in the form's
RecordSource. The textbox is where the work date goes -- can be
automatically entered or can be typed in by employee, or both; the
textbox
would be bound to the WorkDate field in the form's RecordSource.

In the form's Detail section, I'd put a combo box that displays all the
correspondence types; this combo box would be bound to the CorrID field
in
the form's RecordSource. I'd then put a textbox to allow entry of the
number
of items for that specific correspondence type; this textbox would be
bound
to the WorkQuantity field in the form's RecordSource.

This setup will not prohibit an employee from selecting the same
correspondence type twice for the same date, but the composite primary
key
in the table will prevent the saving of any data where that is done for
that
record.

But this should get you started.....
--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
Ken, you get what I am saying and I so appreciate that. The employee
would
select the type of Audit he/she is receiving/ and worked for each day.
They
would complete their production each day.
The Audit type can be received by 8 different sources; Files Rec'd, a
phone
call; email etc. The department has over 50 types of audit that the
division
currently works. (This is a billing/assessment dept). Here is one row
of
from the datasheet. It did not paste well but, it reads, AgentID 1 on
10/13/2005, (AuditType) Corp DQ Edit (number of files) 25 (same audit
type) 5
(additional files recd via phone) then an addition 5 (referrals). We
also
want to capture their ending inventory by audit type. Same example
would
also
apply to the Corresp completed table.

AgentID DateReceived TaxAuditType FilesReceived Phone
Calls FaxRecd AdditionalCorresp CorresfromTP/Petitions Checks
ServCtr/Email/Phone AG
Email Other Agents Activity Notices Bankruptcy
Email TaxAppeal TPSReferrals DayEndingInventory
1 10/13/2005 CORP DQ EDITS 25 5 0 0 0 0 0 5 0


I am thinking in essense, that one employee can have multiple sales in
one
day and those sales can be different products. (This is just for a
comparision)

Thanks for any help you can give me. I am pretty good at Access and
have
built many databases for the dept, but this one has me stumped.


"Ken Snell [MVP]" wrote:

Melissa -

It appears that you want the employee to enter each correspondence
into a
form, and to let the employee select the type of "audit" that is
associated
with each corresondence that is done? Is this essentially what you
seek
to
do?

Or is the "audit" type already associated with each correspondence
type,
and
you just want to let the employee select the correspondence type,
entering
each one separately that the employee did?

It would be helpful if you could show one or two rows of data from the
EXCEL
spreadsheet so that we can see the types of data you're capturing per
row.
What data will the employee specifically have "in hand" when he/she is
ready
to enter a correspondence that is done, and what will the employee
need
to
select or enter additionally while entering that "in hand" data?
--

Ken Snell
MS ACCESS MVP



"Melissa" wrote in message
...
I am creating this database for work. This is to record each piece of
correspondence a employee works (resolves) in a day. To try to
simplify
this,
when you receive a bill and do not agree or have a different
explaination,
and respond to this bill, this is correspondence received. The
correspondence
we are working is just that. The department would like to track how
many
are
received and completed by an employee each day. We are currently
recording
this information into a excel spreadsheet, but with 47 employees and
the
many
types of audit types, the workbooks are becoming huge. I hope this
simplifies
things.

"Melissa" wrote in message
...
I am building an employee production db at work. The problem I am
having
is
with the relationships and maybe my table design. I have one
table
for
employee, Correspondence Received and Correspondence Completed.
These
employees receive and complete correspondence from taxpayers each
day.
There
are 50 different audit types the department would like to track.
With
each
audit type, there are 15 fields that may are may not apply. So,
what
I
have
currently design, the employee can have multiple entries into the
database
"in a day" if working on more than one audit type. What I need is
for
them
to
be able to logg any corresp rec'd or completed (that they did for
that
day)
into one form. Putting it all together is confusing me. hope this
makes
sense. I have been trying to resolve this issue for months and
cannot
seem
to
come up with a solution. Any help would be truly appreciated. I
have
posted
this question 10/20 but the solution hasn't helped. My tables are
as
follows:

Table: Employees
EmployeeName- Text
Supervisor-Text
EmployeeID-Autonumber= PrimaryKey

Table: TaxType/AuditType
TaxType/AuditType- Text

Table: Correspondence Received/Assigned
EmployeeID=AutoNumber PrimaryKey
EmployeeName-Text
DateReceived-Date/Time
TaxAuditType- Text
FilesReceived- Number
PhoneCalls-Number
FaxRecd-Number
AdditionalCorrep-Number
Correspfrom TP/Petitions- Number
Checks-Number
ServCtr/Email/Phone-Number
OtherEmployees-Number
DayEndingInventory-Number

Table: Correspondence Completed
EmployeeID-AutoNumber- Primary Key
EmployeeName-Text
DateCompleted-DateTime
TaxAuditType-Text
InventoryPending-Number
PendingBill-Number
Billing-Number
PendingAsmt-Number
Assessment-Number
CertifiedAsmt-Number
Worked/Not Completed-Number

My Relationships are
One to many= Employees(1) to
CorrespondenceReceived/Assigned(many)
Related fields a EmployeeName
One to many= Employee(1) to CorrespondenceCompleted(many)
TaxType/AuditType= one to many on both CorrespondenceRecd and
Correspondence
Completed (not sure this is necessary because I use TaxType/audit
type
for
combo box on forms)

Thank you

My error -- I was designing this form in my head, and overlooked some
things.

Yes, you'll want to use a subform instead of the Detail section for entering
the CorrID and WorkQuantity values. This subform's RecordSource will be the
EmpCorrWork table. The subform also should have textboxes bound to the EmpID
and WorkDate fields, but set the Visibility property of these textboxes to
No.

Keep the combo box for EmpID selection and the textbox for WorkDate entry in
the main form's ReportHeader section. The main form should have no
RecordSource at all.

The linking fields from main form to the subform will be these (use real
names):
LinkChildFields: EmpID,WorkDate
LinkMasterFields: EmpIDComboBox, WorkDateTextBox

This should fix the data entry problem you were having.

I am not understanding how CorrID (which you're using as the type of audit)
is related to the various sort fields. Are the sort fields the "type of
bills" for each audit type? I need you to fully spell out the entities
involved in your data model. It appears that you may need an additional
field in the EmpCorrWork table, or that you may need another table. I just
cannot tell yet without a better picture of your data model.

--

Ken Snell
MS ACCESS MVP


"Melissa" wrote in message
...
I related the EmpID and CorrID to the parent tables. I then made a combo
box
on the form for both of these fields. The Correspondence Name consists of
all
50 different types of correspondence(audit types) received which will be
chosen from a drop down list. Now grant it, no one employee will do 50
different audit types in a day (or work the correp from all 50) but, they
will at the very minimun do 3 different types. And each of those types
usually comes in through different avenues. A audit type would be for
example; ST NR (sales tax no remit) or Corporate DQ (delinquency) etc.
Each
tax type can have different types of bills. That is why there is so many.

In the third table EmpCorrWork I did not include the EmpName or CorrName
since the EmpID and CorrId is already linked to these tables. In my form I
placed the EmpName and Date in the FormHeader and the detail section I put
the different sorts of how they would receive the files. Phone, FileRm
etc.
The form would not allow me to tab down to any of my fields within the
detail
section after I enter the date. Should I use a subform for the detail
section?

Hope I answered your question and thanks again for all your help.

"Ken Snell [MVP]" wrote:

I need to better understand the relationship of the "sort" fields to the
CorrID entity (which I am using as the identity of a type of
correspondence
from your earlier list). What are these fields, how are they used, what
type
of data entry is needed for them, etc.?

--

Ken Snell
MS ACCESS MVP

"Melissa" wrote in message
...
Thanks for your help Ken. I have completed doing the suggestions you
have
offered. But have a couple questions on the tblEmpCorrWork.
On the third table you suggested only to put the 4 fields into the
table,
then to build the form based on a qry of those fields. In this table,
there
would be more than 4 fields because each audit type can have up to 9
different sort fields. Would these fields go into a different table? I
put
these fields into the table 3 with the EmpID and CorrID being the
foreign
keys back to the parent. What is happening is it is associating each
agent
with a particular audit code when each agent may work many different
types
of
audits. Thanks again for your help



snipped