Solved Query creating false records (1 Viewer)

[nashaz]

Hi all

In my training db, I have CourseListT and Course_JT tables. CourseListT contains CourseID (PK), CourseName, and ValidFor fields. Course_JT contains DeliverID (PK), CourseID (FK), ProviderID (FK), and other fields. This is to handle the situation where any course may be delivered by more than one providers. The way I have linked the delivery of each training to an employee is by using an EmployeeXCourse_JT which contains ID (PK), EmployeeID (FK), CourseID (FK), and CertificationDate.

Issue arises when I try to combine this data in one query i.e., TrainingRecordQ (see the screenshot below). One of the courses, CourseID = 198 has 3 records in Course_JT, however, only one of them is currently assigned to an employee in EmployeeXCourse_JT (to EmployeeID = 52). However, the following query returns 2 extra records i.e., it assigns EmployeeID = 52 to other two CourseID = 198 as well, with the same CertificationDate and displays it in the outcome. The EmployeeXCourse_JT has a total of 21 records as for now but this query produces 23 records. How do I eliminate this error?

I tried to fix this by making another query, CourseQ (which itself outputs CourseListT and Course_JT) and joined it with EmployeeXCourse_JT with "Include ALL records from 'EmployeeXCourse_JT' and only those records from 'CourseQ' where the joined fields are equal" but to no avail.

I will really appreciate if someone could suggest what is going wrong here and why, so I can fix this problem and not repeat it in the future.

Regards

 

[plog]

Words aren't the best way to explain data issues. Example data is.

Could you please upload a copy of your database with sample data in it to demonstrate the issue. Additionally include another set of data (either in Excel or just type it into the response) which is the data you expect your query to return.

Again, 2 sets of data--the database itself and then the data you expect the query to return.

 

[MajP]

When an employee takes a course they do not take a General course but a specific Course delivered on a specific date by a specific provider.

I would think in CourseJT you have DeliverID (which is unique to a specific course given on a specific day by a specific provider. So employeeXCourts_JT should hold a DeliverID_FK not a CourseID. Then you link from EmployeeCourse_JT to a DeliverID then link Course_JT to CourseList.

 

[The_Doc_Man]

The EmployeeXCourse_JT has a total of 21 records as for now but this query produces 23 records. How do I eliminate this error?

In general, this occurs because somewhere there is a value that confuses Access due to ambiguity... so it returns both possibilities. If you can decide what ambiguous field you have, you fix that via a restrictive WHERE clause. By any chance are any of the fields in your query based on a Multi-Valued Field?

To debug this, get a datasheet view of your query's result set. See WHICH records are duplicated, because that is where you will find the ambiguity. It will not necessarily be a field of primary interest. It might just be a field that got "dragged along for the ride" in the query.

 

[nashaz]

Words aren't the best way to explain data issues. Example data is.

Could you please upload a copy of your database with sample data in it to demonstrate the issue. Additionally include another set of data (either in Excel or just type it into the response) which is the data you expect your query to return.

Again, 2 sets of data--the database itself and then the data you expect the query to return.

Hi plog

Here is the example (and stripped down) db. No of records have changed but the same issue persists. I have created two queries, TrainingRecordQ (without Course_JT) and another one with Course_JT. As for the data I expect query to return is same as that in EmployeeXCourse_JT but with the associated CourseName, ProviderID, TrainingLevel, and CourseCode.
Thanks

 

[LarryE]

Hi all

In my training db, I have CourseListT and Course_JT tables. CourseListT contains CourseID (PK), CourseName, and ValidFor fields. Course_JT contains DeliverID (PK), CourseID (FK), ProviderID (FK), and other fields. This is to handle the situation where any course may be delivered by more than one providers. The way I have linked the delivery of each training to an employee is by using an EmployeeXCourse_JT which contains ID (PK), EmployeeID (FK), CourseID (FK), and CertificationDate.

Issue arises when I try to combine this data in one query i.e., TrainingRecordQ (see the screenshot below). One of the courses, CourseID = 198 has 3 records in Course_JT, however, only one of them is currently assigned to an employee in EmployeeXCourse_JT (to EmployeeID = 52). However, the following query returns 2 extra records i.e., it assigns EmployeeID = 52 to other two CourseID = 198 as well, with the same CertificationDate and displays it in the outcome. The EmployeeXCourse_JT has a total of 21 records as for now but this query produces 23 records. How do I eliminate this error?

I tried to fix this by making another query, CourseQ (which itself outputs CourseListT and Course_JT) and joined it with EmployeeXCourse_JT with "Include ALL records from 'EmployeeXCourse_JT' and only those records from 'CourseQ' where the joined fields are equal" but to no avail.

I will really appreciate if someone could suggest what is going wrong here and why, so I can fix this problem and not repeat it in the future.

Regards

View attachment 110832

Here is the way I would constuct this application based on your picture:

1. There are multiple employees
2. Each employee may take multiple training courses
3. Each course may be taught by multiple training providers
4. Each provider issues a Pass/Fail (or other) result

So we need:

1. An employee table
2. A course table with employee table foreign key
3. A provider table with course table foreign key
4. A result table with provider foreign key

Your forms, queries and reports will be much easier to build.

 

[nashaz]

When an employee takes a course they do not take a General course but a specific Course delivered on a specific date by a specific provider.

I would think in CourseJT you have DeliverID (which is unique to a specific course given on a specific day by a specific provider. So employeeXCourts_JT should hold a DeliverID_FK not a CourseID. Then you link from EmployeeCourse_JT to a DeliverID then link Course_JT to CourseList.

Hi MajP

I tried to change the CourseID field in EmployeeXCourse_JT to DeliverID field and it started to produce even weirder results! I have attached the example db in the previous post, if you could be kind enought to please have a look at it?

 

[nashaz]

In general, this occurs because somewhere there is a value that confuses Access due to ambiguity... so it returns both possibilities. If you can decide what ambiguous field you have, you fix that via a restrictive WHERE clause. By any chance are any of the fields in your query based on a Multi-Valued Field?

To debug this, get a datasheet view of your query's result set. See WHICH records are duplicated, because that is where you will find the ambiguity. It will not necessarily be a field of primary interest. It might just be a field that got "dragged along for the ride" in the query.

Hi doc

Its not so much as duplicating records but creating records which dont exist in the table. I have attached the example db if you could kindly take a look and share your thoughts?

 

[nashaz]

Here is the way I would constuct this application based on your picture:

1. There are multiple employees
2. Each employee may take multiple training courses
3. Each course may be taught by multiple training providers
4. Each provider issues a Pass/Fail (or other) result

So we need:

1. An employee table
2. A course table with employee table foreign key
3. A provider table with course table foreign key
4. A result table with provider foreign key

Your forms, queries and reports will be much easier to build.

Hi Larry

If I understand correctly, you do not want me to have separate CourseListT and Course_JT? In this case, wouldn't it mean that every record in course table must have an employee ID attached to it, thus stopping me from adding courses which we will be delivering in the future?

As I have requested others, I have attached my example db. Please if you can, take a look at it and share your thoughts.

Thanks

 

[LarryE]

Hi Larry

If I understand correctly, you do not want me to have separate CourseListT and Course_JT? In this case, wouldn't it mean that every record in course table must have an employee ID attached to it, thus stopping me from adding courses which we will be delivering in the future?

As I have requested others, I have attached my example db. Please if you can, take a look at it and share your thoughts.

Thanks

Well you could still add training courses, but if you wished to keep a separate table with just courses, you could certainly do something like this:

So each EmployeeXCourse_JT has both a provider and employee foreign key. ACCESS can certainly do that too. Table normalization is the key and referencial integrity.

You can also reverse ProviderT and CourseListT so that each Provider has multiple courses. In that case, the CourseListT would need a ProviderID foreign key and the EmployeeXCourseJT table would need a CourseID foreign key. That may work better.

Good Luck

 

[MajP]

Should look more like this.
An employee takes a specific course.
A specific course is in the Course_JT table uniquely defined by CourseID, ProviderID. If course code and training level is not unique to a specific providers delivery of a course then those fields are generic and go in the courseListT.

 

[The_Doc_Man]

Hi doc

Its not so much as duplicating records but creating records which dont exist in the table. I have attached the example db if you could kindly take a look and share your thoughts?

It would have been nice for you to explain HOW (what steps led to) you get these phantom records. Which query do I open and under what circumstances. However, since you only had three queries I looked. The first two are no biggie. The third one, however, has a likely candidate for the phantoms. "TrainingRecordQwithCourseJT" has four tables. You have expressed relationships among the four tables. Two of them are fine. The third is the likely source of your confusion.

EmployeeXCourse_JT to EmployeeT : a many/one relationship with the "one" side on EmployeeT at its prime key. No problem.
EmployeeXCourse_JT to CourseListT : a one/one relationship but at least one side is on the CourseListT prime key. No problem.
CourseListT to Course_JT : a one/many relationship but neither end aligns with a prime key. Big worries.

Unless you constrain the link to Course_JT, you will get every combination of records where the aligned fields match. SQL is a maker of many combinations of records. When you have a query with multiple tables, you can expect to see EVERY combination of records where the criteria match. If you fail to qualify the potential matches then you will get every possible combination of records. It is this combination function that gives you "impossible" courses, I think. This might not be the only way to get phantom combinations, it is just the first one that popped up to my eyes.

 

[nashaz]

Hi all

Apologies for late response. Been off work due to unseen circumstances. Appreciate all the help.

 

[nashaz]

Well you could still add training courses, but if you wished to keep a separate table with just courses, you could certainly do something like this:
View attachment 110836
So each EmployeeXCourse_JT has both a provider and employee foreign key. ACCESS can certainly do that too. Table normalization is the key and referencial integrity.

You can also reverse ProviderT and CourseListT so that each Provider has multiple courses. In that case, the CourseListT would need a ProviderID foreign key and the EmployeeXCourseJT table would need a CourseID foreign key. That may work better.

Good Luck

Hi Larry

I liked the idea you proposed but very soon figured out I would have to change alot elsewhere in the db It doesn't help that I have had to make changes this late into the development of db. But I appreciate your time and suggestions very much. Thank you

 

[nashaz]

Should look more like this.
An employee takes a specific course.
A specific course is in the Course_JT table uniquely defined by CourseID, ProviderID. If course code and training level is not unique to a specific providers delivery of a course then those fields are generic and go in the courseListT.


View attachment 110841

That's done the trick! Thank you very much MajP!

 

[nashaz]

It would have been nice for you to explain HOW (what steps led to) you get these phantom records. Which query do I open and under what circumstances. However, since you only had three queries I looked. The first two are no biggie. The third one, however, has a likely candidate for the phantoms. "TrainingRecordQwithCourseJT" has four tables. You have expressed relationships among the four tables. Two of them are fine. The third is the likely source of your confusion.

EmployeeXCourse_JT to EmployeeT : a many/one relationship with the "one" side on EmployeeT at its prime key. No problem.
EmployeeXCourse_JT to CourseListT : a one/one relationship but at least one side is on the CourseListT prime key. No problem.
CourseListT to Course_JT : a one/many relationship but neither end aligns with a prime key. Big worries.

Unless you constrain the link to Course_JT, you will get every combination of records where the aligned fields match. SQL is a maker of many combinations of records. When you have a query with multiple tables, you can expect to see EVERY combination of records where the criteria match. If you fail to qualify the potential matches then you will get every possible combination of records. It is this combination function that gives you "impossible" courses, I think. This might not be the only way to get phantom combinations, it is just the first one that popped up to my eyes.

Hi doc

Thanks for the spot regarding relationship not being with PK. I certainly overlooked that totally somehow.