need help with this query

[keyur]

hi everyone

I have 3 tables:

1 Courses: columns- Course ID, Course Name, and then 1
column for each workgroup(checkbox). In each col. I have
checked off the coursenames that applies to the workgroup.

2 Work Groups: columns - Workgroup

3 Employee List: col - Employee #, FirstName, LastName,
Workgroup


This is what i want to do

I want to add subdatasheet in the Employee List that will
show all the courses for that employee (i.e depending on
which workgroup it belongs to)

similarly, i want to add subdatasheet to the WorkGroups
showing the list of employees that belongs to the
workgroup.

Thanks for any help

Keyur
.

 

[Len Boorman]

Basically your tables are not normalised and you will have increasing problems unless you do nornalise correctly.


You need 4 tables:

1 Courses: columns- Course ID, Course Name.

2 Work Groups: columns - Workgroup

3 Work Group/Courses: columns Course ID, Workgroup

4 Employee List: col - Employee #, FirstName, LastName,
Workgroup


Question.
Can an Employee belong to more than one Workgroup ?.

If so

table 4 becomes
4 Employee List: col - Employee #, FirstName, LastName,

and a new table

5 Employee/Workgroup col:Employee #, ;Workgroup

Once you have a correctly normalised set of tables you will find that tasks do become easier

L

 

[keyur]

Thanks Len, I tried it out but came out completely blank. little more help will be great. I still couldnt figure out how to set the subdatasheets.

 

[Len Boorman]

I have quickly set up the tables and relationships and set up a query that will select all employees with their names and workgroups (Table4). It then tracks through the Workgroup table (Table2) and the Workgroup/Course (Table 3) to the Course Table (Table1) where it also selects the Course Name.

This query can be amended to select a certain employee, a certain workgroup or a certain course as the source of Forms or reports.

There are generally several ways to achieve an end goal.

Have you normalised the tables?.
Have you set up the relationships ?.

Here is the query in sql

SELECT Table4.Employee, Table4.[First name], Table4.[Last Name], Table4.Workgroup, Table2.Workgroup, Table3.Workgroup, Table3.CourseID, Table1.[Course ID], Table1.Course_Name
FROM (Table2 INNER JOIN (Table1 INNER JOIN Table3 ON Table1.[Course ID] = Table3.CourseID) ON Table2.Workgroup = Table3.Workgroup) INNER JOIN Table4 ON Table2.Workgroup = Table4.Workgroup;


Len

 

[keyur]

Thanks a lot Len. THat was perfect.

 

[keyur]

Got another question.

I was trying to add a subdatasheet (Employees' FirstName and LastName) to WorkGroups Table.

Wht's wrong with this? (either just a stupid error or i am way off)

SELECT Employees.FirstName, Employees.LastName, Employees.WorkGroup
FROM Employees INNER JOIN WorkGroups ON Employees.WorkGroup = WorkGroups.WorkGroup

Thanks again.

 

[keyur]

sorry, forgot to mention. That SQL stat. gives me all the employees when i expand a workgroup.

Thanks again.

 

[keyur]

never mind, i didnt linked the child and master fields.

thanks

 

[keyur]

This is on the same database that was discussed earlier in this thread.

i have another table now called
DueDates: col- EmployeeID, CourseID, Completion Date and Due Date

The following gives me all the courses related to a employee (ie based on what workgroup the employee belongs to)

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, WorkGroups.WorkGroup, Courses.CourseID, Courses.CourseName
FROM (WorkGroups INNER JOIN (Courses INNER JOIN WorkgroupCourses ON Courses.CourseID = WorkgroupCourses.CourseID) ON WorkGroups.WorkGroup = WorkgroupCourses.WorkGroup) INNER JOIN Employees ON WorkGroups.WorkGroup = Employees.WorkGroup
ORDER BY Employees.EmployeeID, Courses.CourseID;

How can i get the dates (from DueDates table) on this query (date is specific to employeeID and CourseID)?

Any help will be great. thanks.