Show all fields from a linked table for each record

[pascal]

Hi,

I have two tables called "Employees" and "Categories".
The table "Employees" was imported from an Excel-file.

Table "Employees": Fields: "EmployeeID", "Name", "CategoryID", "Field4", "Field5" etc.

Table "Categories"
Fields: "CategoryID" and "Category".

There are 10 categories. Tables are linked by the CategoryID. Now, I want to make a new table from these two tables in which each employee must show the 10 categories even if there is no record for that category in the table "Employees", which I can then export to an Excel-file again. For example:

Table "Employees":

Employee1 - CategoryID1
Employee1 - CategoryID3
Employee2 - CategoryID1
Employee3 - CategoryID2
Employee3 - CategoryID5

In my new table I want to have this:

Employee1 - CategoryID1
Employee1 - CategoryID2
Employee1 - CategoryID3
...
Employee1 - CategoryID10
Employee2 - CategoryID1
Employee2 - CategoryID2
Employee2 - CategoryID3
...
Employee10 - CategoryID10

and so on.

Can this be done only with queries?

Who can help me out with this one?

Thanks already.

Pascal

 

[KenHigg]

There appears to be some normalization issues. In table employees, what does each record represent? If this is the primary table where you place your employee data, you need to back up and re-design your db...


kh

 

[pascal]

The "Employees" table is imported from an Excel file as I said before and the only thing I want to do with Access is to make a new table which I can then export again to Microsoft Excel. But, not every employee has for each category input data. And I want to have for each employee all the categories. So what I need is for each employee 10 records in my new table, you know for all the categories. When there was no input for some category a new record should be added with only the fields EmployeeID and CategoryID filled in. The other fields must be left blank.
So, let's start from that one table I imported ("Employees"). What should I do next to accomplish what I want?

 

[KenHigg]

How many employees are there?

kh

 

[pascal]

About 500 employees.

 

[Pat Hartman]

I don't see the point either but you can do it with a left join. You want to join the employees table to the categories table with a left join and join the categories table to the employeecategories table also with a left join - visually it's:

employees --> categories --> employeeCategories

 

[KenHigg]

pascal, I don't quite get what Pat is doing (Not sure where the third table is coming from), but if you can't get her suggestion to work - repost and we'll have another go at it.

kh

 

[Pat Hartman]

The third table assumes that the tables are properly defined in a many-to-many relationship. If in fact they are not, then the solution involves creating a union query with 10 selects to normalize the structure.