Finding a Person

[Navyguy]

I am quite new at this Access 2000 so I hope I can understand your answer...here goes the question

My db is for employee tasks and in my table I have a "report number" field as a (PK).

Along with some other fields I fill in the following fields of the same record:

Surname
Surname2
Surname3
Surname4

Generally the person responsible for the task will be in the Surname field, but if they assisted with a task their name may appear in any of the other fields of the record or the fields may be blank.

If I am looking for all the tasks that "Smith" has been involved in, how do I search all of these fields at once? Of course I also have more then one "Smith" but that is a separate problem.

I will then use the results of this query as the basis of a report.

Thanks for your assistance...

Navyguy

 

[Mile-O]

Change your structure.

You need a table for Staff, a table for tasks, and a third table to link them together.

i.e.

tblStaff
StaffID
Forename
Surname
details specific to staff member (must all be dependant upon StaffID)

tblTasks
TaskID
TaskName
TaskDescription
etc.

tblStaffToTasks
StaffID
TaskID

Where the key is:

Table Name
Primary Key
Field Name
Foreign Key & Primary Key
Foreign Key


Build an appropriate query that connects these three tables (one to many from StaffID to StaffID and a one to many from TaskID to TaskID) and you can then base a form/subform combination on this query, link the fields, and all people who work on a task will be countable from the one field.

 

[Mile-O]

Having a table with anything going This1, This2, This3, This4 is a repeating group and should be eliminated as soon as possible in the design process as it's a violation of the First Normal Form (1NF) of database normalisation.

And also, having blank cells is a waste of space, there's never any need to have redundant cells.

 

[Navyguy]

Thank-you for your response.

I did some quick research on your answer and I have determined that I am slightly out of my league here!!!

The db is an existing one that I am trying to make more useful and the focus/design is on the task and not really on the staff. In fact in many cases the "Surname" field may have a name of a company that provided the service/repair and not be a regular staff member.

Perhaps starting from scratch is the best thing to do...back to the books I guess.

Once again thanks and I will try to use the 1NF, 2NF idea as soon as I get a bit of a grip on it.

Navyguy

 

[Mile-O]

You might find my admittedly messy example with a many to many relationship. It's an example on that thread.

It deals with students with many comments although it shouldn't take a genius to replace students with tasks, and comments with staff.