PLEASE help me with parameter queries & table relationships!!

[Red]

I am designing a database to keep track of the jobs we have done for our
clients. I would like users to be able to search the database using parameter
queries.

My tables are structured as follows:

Clients (Client ID, Client Name)

Industry (Industry ID, Industry Name)

Employees (Employee ID, Employee Responsible)

Project Type (Project Type ID, Project Type)

Jobs (Job ID, Client [drop-down list from Client table], Industry [drop-down
list from Industry table], Project Type 1 [drop-down list from Project Type
table], Project Type 2 [drop-down list from Project Type table], Project Type
3 [drop-down list from Project Type table], Job Description, Year, Employee 1
[drop-down list from Employees table], Employee 2 [drop-down list from
Employees table], Employee 3 [drop-down list from Employees table], Work
example

I have made a parameter query where one can enter a Client name, and it
brings up a list of all jobs done for that Client (shows all fields in Jobs
table). Have done a similar query to search by Industry. These work fine.

I would like to be able to search by Employee Name, but so far the query
only returns Employee Names in the Employee 1 field. How do I get it to also
pick up the name if it's in Employee 2 or Employee 3 fields?

Would like to do the same for Project Type but am having the same problem as
there is more than one Project Type field.

My tables are related as follows:

Industry ID (Industry table) = Industry (Jobs table)
Client ID (Clients table) = Client (Jobs table)
Employee ID (Employees table) = Employee 1 (Jobs table)
Project Type ID (Project Type table) = Project Type 1 (Jobs table)

I am suspecting the problem lies with the table relationships....would
appreciate any advice!!!!!!!!!!!!!!!!!

 

[Nikos Yannacopoulos]

I am suspecting the problem lies with the table relationships

This is juct a consequence of the main proble, which is the design
itself. It is generally bad practice to use several similar fields for
the same data in a table, and you're just beginning to see why.

Since a job can be worked on by several employees, and can belong to
several types, you should use separate tables for employees assignd to
jobs, and job types per job, linked to the jobs table on job ID. So, the
table structure should be something like:

Clients (Client ID, Client Name)

Industry (Industry ID, Industry Name)

Employees (Employee ID, Employee Responsible)

Project Type (Project Type ID, Project Type)
(so far so good)

Jobs (Job ID, Client, Industry, Job Description, Year, Work example)
(remove the job type and employee fields)

Job_Employees (Job ID, Employee ID)
(linked to Jobs and Employees)

Job_Projetc_Types (Job ID, Project Type ID)
(linked to Jobs and Project Types)

In each of the last two tables, there should be as many records for each
job as the number of employees working on it / project types the job
belongs to.

This design is not only much more flexible (no limit in number of
employees / types per job), it also makes it a lot easier to search by
employee or job title, and potentially saves storage space.

Note: it is generally advised to not use lookups directly in tables; not
only does it not really offer anything, since users will only ever work
on forms, not directly in tables, it is also known to create problems
sometimes. Use combo box look-ups in your forms instead, which offer
greatly increased functionality through more properties and events.

HTH,
Nikos

 

[Red]

Hi Nikos

Many thanks for your reply. I now understand what you mean about using
several similar fields for the same data.

I've set up the two new tables you suggested, but am confused about how all
of the tables should now relate to each other.

Is there any easy way to explain that?

Red

 

[Nikos Yannacopoulos]

Red,

To begin with, the Jobs table (being the "main" table) should include
the other tables' PK fields as foreign keys, to connect to them on,
instead of the pertinent "description" fields; that is to say, it should
have a Client ID field, for instance, not a client name field...
likewise for Industry. To make this clearer, the fields in Jobs should be:

Jobs (Job ID, Client ID, Industry ID, Job Description, Year, Work example)

So, the Jobs table is joined to Clients and Industry tables on the
respective ID fields. It is also joined to the Job_Employees and
Job_Project_Types tables on the Job ID field of each.

Job_Employees also has a join to Employees on Employee ID.

Job_Project_Types also has a join to Project Type on Project Type ID.

Hope it is clear now.
Nikos