merging redundancy

[Guest]

I have database for employee accident, in the database the employee can have
several accidents. I have employees that have had 3 or more accidents is
there a way that I can merge them into one subdata sheet. for example:
John Stockon has 3 different accidents
1. broken finger
2. slip and fall
3.Vehicle accidnet
So when I view John Stockon name I will see the three accidents under his
name and not in separate records. I hope I am making sense.

LHEMA
LHEMA

 

[Jeff Boyce]

If you are saying that the relationship of your data in the real world is
one-to-many (one employee can have zero, one or more accidents), then you
need to model that in your relational database (Access), rather than using a
spreadsheet model (one row per accident, repeating employee info).

One table will hold Employee info (EmployeeID, firstname, lastname, dob,
....). One table will hold Accident info (AccidentDate, Description,
EmployeeID, ...). Note that you do NOT repeat the employee's data.
Instead, just use the EmployeeID from the Employee table to point back to
who it was who had the accident.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for the response. I have two tables Employee Accident and Accidents.
What I did not have is Employee ID in both tables and my relationship is one
to one. I guess I need to add EmployeeID to Accident table and link them as
one to many AccidentID to EmployeeID..is that correct

 

[Jeff Boyce]

I'm not sure I understand from your description.

I can envision one table (tblEmployee) with employee-only information, and a
second table (trelEmployeeAccident) with accident-specific information, one
row per employee accident. The first table uses an EmployeeID as a primary
key. The second table includes a foreign key field that holds the
EmployeeID to which that accident relates. The relationship, in my mind, is
one-to-many, since one employee can have many accidents.

This does not sound like what you described.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Yes that almost right but I did not make employeeID my primary because an
employee can have as many accidents. My primary key is Accident ID for both
tables but I linked tbl accident (accidentID) to tbl employee accident
(employeeID).

 

[Jeff Boyce]

By using the same primary key for both tables, I believe you are creating a
one-to-one relationship. That doesn't sound like what you've described.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

No its not, I think I am confusing myself, what I mention to you is what I
want. I want to be able to go to that employee and look at all the accidents
they have had. Right now they are in separate records. I change my
relationship to a one to many and linking tbl emp accident (primary key
accidentID)to tbl accident and adding a column for EmployeeID and linking it
to tbl employee (accidnetID). Still not getting the right results

 

[Jeff Boyce]

If I were trying to do what you just described, I would:

* create two tables (tblEmployee, w/ EmployeeID, FirstName, ...; and
trelEmployeeAccident, w/ AccidentID as primary key, EmployeeID as foreign
key, and other accident-specific fields)
* create two queries, one for each table
* create two forms, one for each query.
* open the form for the Employees -- this would be my "main" form -- in
design mode
* add the form for the Accidents as a sub-form to my main form
* make sure the parent & child properties of the subform control (on the
main form) used the field that links the two forms together (i.e., the
EmployeeID).
* use an unbound combo box in the header of the main form to have a way to
select an Employee to load into the main form.

When ever the main form displays a different employee, the subform displays
that employee's accidents.

JOPO - just one person's opinion...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Ok..let me try this and I will get back with you with the results.Thank you

 

[Guest]

That did not work for me, I am still getting separate records with same
employees. Reading some of the questions in the form, I think my problem is
with my relationship. This is what I have:

table employees: primary key - AccidnetID
design: name, address, department, jobtitle,DOB, DOH
Employees can have as many accidents as possible so this is a duplicate entry

table accidents: primary key - AccidentID
location, time of accident, type of injury, etc

I have these two tables link as AccidentID from tbl accidnet link to
EmployeeID from tbl Employee and its a 1 to many. My results does not give me
what I want. When I go to my query I am looking for a Employee who has had
several accidents, all I want to do is have that employee with one record and
all the accidents that he/she have had listed below. Is this possible. Thanks
in advance

 

[Jeff Boyce]

Please re-read my previous post -- I offered an alternative table structure
with two tables.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

It did not work ..... I am still getting separate records for an employee
with many accidents

 

[Jeff Boyce]

What you reported as your new structure does not match what I suggested.

We may be describing the situation in different terms...

In a well-normalized relational database design, you WOULD expect to have
multiple records... but one "Accident" record per accident X Employee.

You would have only one record with the Employee information, then use the
EmployeeID from that table as a foreign key in your AccidentXEmployee table.
In that second table, you would have one record per EmployeeAccident.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Looking at Northwind database, I decided to not worry about what's in the
tables or query and just create a report that group each employee by
department and name.By doing this it gives me what I want on the report. I
just thought I can do the same in the query or table.Thanks for opening my
eyes to other options