Query question - syntax (1 Viewer)

hello

After the departure of the previous admin, I've inherited a 2007 database covering 150 staff, across 4 locations. If possible, I want to add another query to it, but not sure if my quest is even possible.

Essentially, the main tables involved are :

lookups forEmployee - lists all employees (Number, FirstName, LastName)

• All Courses available - ( Courses, CourseLength, ValidityPeriod)

Both of these tables are used to populate


Training


which lists employee, course undertaken and date completed.

I've had a quick look through queries section and cant find anything that covers this scenario,

Queries already exist to create employee history reports which also identify employees needing refresh training.

I am trying to achieve a QBE query that compares employees against the all courses table in an attempt to identify those colleagues who have not undertaken a training course.


hope this makes sense


any ponters gratefully received


Flump

Try two queries. Create one that returns those who have taken the course, then use the unmatched query wizard to compare that to all employees.

yes 2 queries:

first query, Quey1 (cartesian between employees and courses)
this will combine all employees against all courses:


SELECT employee.number, employee.lastname, employee.firstname, courses.courses, courses.courselength, courses.validityperiod
FROM courses, employee;


second query (left join):
query will join the first query you made (query1) against Training table:

SELECT Query1.number, Query1.lastname, Query1.firstname, Query1.courses, Query1.courselength, Query1.validityperiod, training.number
FROM Query1 LEFT JOIN training ON (Query1.courses = training.courses) AND (Query1.number = training.number)
WHERE (((training.number) Is Null));

the second query is what you need.

pbaldy & arnelgp


Thank you for prompt response, unfortunately telecoms issues at home meant no internet for a few days, and we are not trusted enough to visit forums or chatrooms on the company network !:banghead::banghead::banghead:

Anyway, tried the SQL statements provided, but I get a "type mismatch in expression" error message when trying to run Query2. Could this be related to having lookup fields to populate the training table? Ive attached a samplefile

I dont know enough currently to identify the problem, but thinking it might be lookup table related, I created a test dbase with 4 employees and 5 courses using tables and fieldnames shown above (but without lookup or relationships) and then copy / pasted the 2 statements exactly to avoid any typos.

Query 1 returns a list showing all 4 staff with 5 courses against each of them (attended or otherwse) - only 8 attended training events are actually recorded in the training table

Query2 runs without error on the non lookup / relationship version, but also returns a list of 20 records, when really it should only be the 12 events that have not been attended by employees



The system we use has numerous lookup tables, hence attaching that version of the sample file.


grateful for any expert advice


regards


Flump

Due to a bug in the forum software this message was "unapproved" (hidden) for some considerable time. I have just approved it. I hope no one has been inconvenience too much! The new forum software no longer has this bug, so this problem should not reoccur.

Yes, a little late.

Relationships are messed up. Training table should not have firstname and lastname fields. Should have a number field for Employee number. You do have the number field but named it firstname then you also have a number field named lastname. You have Employee table in relationships twice. Remove one and remove lastname and rename firstname to EmpID_FK in training.

Consider this all-in-one query:

SELECT employee.firstname, employee.lastname, courses.courses, training.eventdate
FROM employee INNER JOIN (courses INNER JOIN ((SELECT courses.ID, employee.number
FROM courses, employee) AS Query1 LEFT JOIN training ON (Query1.number = training.EmpID_FK) AND (Query1.ID = training.courses)) ON courses.ID = Query1.ID) ON employee.number = Query1.number;

Advise not to use exact same field name in multiple tables. Also advise not to build lookups in tables.