Having trouble with relationships & table design?

[Melissa]

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
is 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 if
working on more than one audit (correspondence) type. I 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.
Thank you

 

[KARL DEWEY]

Tell us the table structures. Post the information like --
Employee-
EMPID - autonumber
LName - text
FName - text
Dept - text
etc -

Audit-
AuditID - autonumber
EMPID - number - interger
Type - text
DateRec - datetime
DateEnd - datetime
Remarks - memo
etc -

 

[Melissa]

My table structures 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 are: 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 for your time.

 

[KARL DEWEY]

The EmployeeName field is not needed in the Correspondence Received/Assigned
and Correspondence Completed tables.

Change the EmployeeID from AutoNumber to EmployeeID-Number- Integer -
Foreign Key in both the Correspondence Received/Assigned and Correspondence
Completed tables.

Use the EmployeeID to set your relations – one-to-many Employees to
Correspondence Received/Assigned and Correspondence Completed tables.

 

[Melissa]

Ok, I get that but, how will that help me bring this all together? I guess
what I need to get accomplished is for the database to allow the agents not
to have multiple entries into the db if they have more than one audit type
they are working on. Is there someway around this?

 

[Melissa]

My last message I meant, Multiple entries by day if the agent does more than
one audit type.

Thanks for any help you can give me...

 

[KARL DEWEY]

There are several ways. You make a compound index or key.

Open the table in design view. Highlight the two fields (EmployeeID and
DateReceived ) and click the Key icon. This set a unique index of the two
field combination. One employee can only make one record per day. If you
cannot save due to violations you will need to find where you
currently have duplicates first.

Use fields EmployeeID, DateReceived, and TaxAuditType to allow one record
per day for each TaxAuditType per employee.

The second way is to open the table in design view and click on the menu
VIEW - Indexes and build a multi-field with the above fields.

 

[KARL DEWEY]

I would have use a structure that recorded each action - not just their
numbers - and then had a query to do the counting instead of the agents
possibly fudging numbers.

 

[Melissa]

Could you provide more detail on how to do that, record each action? Do you
think the design of the database is incorrect for the type of data I am
trying to capture. I am still in design phase due to the fact that I know
there is a better way, I just cannot figure it out.

Thanks again

 

[Melissa]

Could you provide more detail on how to do that, record each action? Do you
think the design of the database is incorrect for the type of data I am
trying to capture. I am still in design phase due to the fact that I know
there is a better way, I just cannot figure it out.