Multiple Table Query

[Guest]

I have five tables that I need to combine into a query to use in a report.
The table has a list of employees and thier information including their
employee number. The other tables are various skills that the employees
perform. One table for each skill. The tables are broken down into the
Employee number, the qtr in which the skills were peformed and the score for
those skills. Each employee may have several records in each table, but may
not have any records in another. I am looking at the past four qtrs. My
problem is that when I put them into a query it only returns the records for
employee that have data in all five tables. Is their any way to force access
to show all the resords from the employee table and place a null value in for
the tables that they do not have resords for. I could just add all employees
to each table put that would really complicate my life as I import the data
from excel. Thank you for any help you may have.

 

[Duane Hookom]

Consider subreports for each of the skills tables. Actually, I would attempt
to combine all the skill tables into a single table.

 

[John Vinson]

I have five tables that I need to combine into a query to use in a report.
The table has a list of employees and thier information including their
employee number. The other tables are various skills that the employees
perform. One table for each skill. The tables are broken down into the
Employee number, the qtr in which the skills were peformed and the score for
those skills. Each employee may have several records in each table, but may
not have any records in another. I am looking at the past four qtrs. My
problem is that when I put them into a query it only returns the records for
employee that have data in all five tables. Is their any way to force access
to show all the resords from the employee table and place a null value in for
the tables that they do not have resords for. I could just add all employees
to each table put that would really complicate my life as I import the data
from excel. Thank you for any help you may have.

I think your table structure is questionable. If you decide to add a
fifth skill, you'll need a new table, and you'll need to redesign all
your queries and reports!

Consider a different normalized design:

Employees
EmployeeID
LastName
FirstName
<etc etc>

Skills
SkillID
SkillName
<information about this skill in its own right>

EmployeeSkills
EmployeeID << link to Employees
SkillID << the skill they performed
SkillDate << when they did so
Score

If an employee has three skills performed, they'd have three records
in this table; if they have eight, there would be eight records. A
simple totals query will give you the result you want without any need
to store dummy data in the tables!

You can link to the Excel spreadsheets and run Append queries to
populate this table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps