Duplicate Records in my Query

  • Thread starter Thread starter Joy Mendleson
  • Start date Start date
J

Joy Mendleson

Hi there,

I see that I have some duplicate records in some of my queries. I know now
that they can be eliminated by setting Unique Values = Yes in the Query
Properties box.

But looking at the data I can't see why they occured in the first place!
Why do these duplicate records occur? Does it have anything to do with
Criteria boxes?
Is it a good idea to always use Unique values = Yes?

Here is the SQL statement (quite long!) that produced duplicates.

SELECT tblCourseDetails.[Course Number], tblCourseName.CNM_CourseName,
tblCourseDetails.[Course Start Date], tblCourseDetails.Location,
[student/course table].[Student ID], [student table].[Student Member],
[First Name]+" "+[Last Name] AS [Student Name], [department
table].department_name, [student/course table].[Student Mark] AS [Mark P/F],
[student/course table].[Accommodations Required], [student/course
table].[Attendance Day 1], [student/course table].[Attendance Day 2],
[student/course table].Course, [student/course table].Books, [student/course
table].Other, [student/course table].Total, [student/course table].Paid
FROM tblCourseName INNER JOIN (tblCourseDetails INNER JOIN (([student table]
INNER JOIN [student/course table] ON [student table].[Student ID] =
[student/course table].[Student ID]) INNER JOIN ([department table] INNER
JOIN [student/department table] ON [department table].[Department ID] =
[student/department table].[Department ID]) ON [student table].[Student ID]
= [student/department table].[Student ID]) ON tblCourseDetails.[Course ID] =
[student/course table].[Course ID]) ON tblCourseName.CNM_CourseNumber =
tblCourseDetails.[Course Number]
WHERE (((tblCourseDetails.[Course Number])=[Enter a Course ID]) AND
((tblCourseDetails.[Course Start Date])=[Enter the Course Start Date]));

Thanks for your help,

Joy
 
Joy Mendleson said:
Hi there,

I see that I have some duplicate records in some of my queries. I know now
that they can be eliminated by setting Unique Values = Yes in the Query
Properties box.

But looking at the data I can't see why they occured in the first place!
Why do these duplicate records occur? Does it have anything to do with
Criteria boxes?
Is it a good idea to always use Unique values = Yes?

Here is the SQL statement (quite long!) that produced duplicates.

SELECT tblCourseDetails.[Course Number], tblCourseName.CNM_CourseName,
tblCourseDetails.[Course Start Date], tblCourseDetails.Location,
[student/course table].[Student ID], [student table].[Student Member],
[First Name]+" "+[Last Name] AS [Student Name], [department
table].department_name, [student/course table].[Student Mark] AS [Mark P/F],
[student/course table].[Accommodations Required], [student/course
table].[Attendance Day 1], [student/course table].[Attendance Day 2],
[student/course table].Course, [student/course table].Books, [student/course
table].Other, [student/course table].Total, [student/course table].Paid
FROM tblCourseName INNER JOIN (tblCourseDetails INNER JOIN (([student table]
INNER JOIN [student/course table] ON [student table].[Student ID] =
[student/course table].[Student ID]) INNER JOIN ([department table] INNER
JOIN [student/department table] ON [department table].[Department ID] =
[student/department table].[Department ID]) ON [student table].[Student ID]
= [student/department table].[Student ID]) ON tblCourseDetails.[Course ID] =
[student/course table].[Course ID]) ON tblCourseName.CNM_CourseNumber =
tblCourseDetails.[Course Number]
WHERE (((tblCourseDetails.[Course Number])=[Enter a Course ID]) AND
((tblCourseDetails.[Course Start Date])=[Enter the Course Start Date]));

Thanks for your help,

Joy
Click to expand...



SELECT tblCourseDetails.[Course Number]
,tblCourseName.CNM_CourseName
,tblCourseDetails.[Course Start Date]
,tblCourseDetails.Location
,[student/course table].[Student ID]
,[student table].[Student Member]
,[First Name]+" "+[Last Name] AS [Student Name]
,[department table].department_name
,[student/course table].[Student Mark] AS [Mark P/F]
,[student/course table].[Accommodations Required]
,[student/course table].[Attendance Day 1]
,[student/course table].[Attendance Day 2]
,[student/course table].Course
,[student/course table].Books
,[student/course table].Other
,[student/course table].Total
,[student/course table].Paid
FROM tblCourseName
INNER JOIN
(tblCourseDetails
INNER JOIN
(([student table]
INNER JOIN
[student/course table]
ON [student table].[Student ID] = [student/course table].[Student
ID])
INNER JOIN
([department table]
INNER JOIN
[student/department table]
ON [department table].[Department ID] = [student/department
table].[Department ID])
ON [student table].[Student ID] = [student/department table].[Student
ID])
ON tblCourseDetails.[Course ID] = [student/course table].[Course ID])
ON tblCourseName.CNM_CourseNumber = tblCourseDetails.[Course Number]
WHERE (((tblCourseDetails.[Course Number]) = [Enter a Course ID])
AND ((tblCourseDetails.[Course Start Date]) = [Enter the Course Start
Date]));


Without knowing the structure of the Tables (including Relationships), and
some sample data (from each table), and a sample of the incorrect output,
and a sample of the expected desired output, it's hard to answer this one.


Sincerely,

Chris O.
 
Back
Top