Query producing Duplicate Records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query producing Duplicate Records

Author

Topic

nottawayblue
Starting Member

2 Posts

Posted - 2009-12-15 : 12:56:43

Attempting to eleminate duplicate records from a query of two many-to-many tables.

Basic premise: My database tracks training requirements for our members. I have three basic relevant tables: Members, Positions, and Courses. The positions table covers the available positions, the courses table covers the available courses. Each member can fill multiple positions and each position can be filled by multiple members. Similarly, each position has multiple course requirements and each course is required for multiple positions. So I have two seperate tables to track these: JMemberANDPosition and JPositionCourseRequirements.
JMemberANDPosition has fields: MemberID, PositionID
JPositionCourseRequirements has fields: PositionID, CourseID
The problem is when I join the two tables (to show what courses a member need to take), the result has multiple completely duplicative records. I am working solely in Access here, so DISTINCTROW is in play, but isn't working.

Sample Data


JMemberANDPosition
Member ID          PositionID
1                  1
1                  3
2                  1
2                  2
3                  3
...

JPositionANDCourse
PositionID         CourseID
1                  2
1                  3
1                  5
2                  1 
2                  3
2                  6
3                  4
3                  5
...

Resulting Join
MemberID          CourseID
1                 2
1                 3
1                 5
1                 4
1                 5
2                 2
2                 3
2                 5
2                 1
2                 3
2                 6
3                 4
...

The rows in red being the duplicates.
The current SQL is


SELECT DISTINCTROW JMemberANDPosition.MemberID, JPositionANDCourse.CourseID
FROM JMemberANDPosition INNER JOIN JPositionANDCourse ON JMemberANDPosition.PositionID = JPositionANDCourse.PositionID;

Don't so much care about using DISTINCTROW, just looking to avoid the duplication.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-15 : 13:14:45

How about just use DISTINCT?

Not for nuthin

That can't be the SQL though

You using QBE grids?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nottawayblue
Starting Member

2 Posts

Posted - 2009-12-15 : 14:06:25

I promise that is the SQL, and it's a copy/paste job from when I designed the original DB (which I tweaked to make this one) a few years back for a similar purpose. Back then I was actually playing with code at least once or twice a week, so my instint is that it wasn't pulled form the QBE, but I can't be sure.

The distinct worked, btw. My interpretation was that DITINCT would cut it down to only say one listing per member, or per course, but, hmm. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-15 : 14:31:02

quote:
Originally posted by nottawayblue
The distinct worked, btw.

<Pacino>
BOO YAH
</Pacino>

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources