identify duplicates

  • Thread starter Thread starter trex44
  • Start date Start date
T

trex44

I used the dupicate wizrd in Access 07 to find dupes in my table; I have 5
columns: student id, courseid, semester, grade, and course year.
I identified the duplicates using this:
SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1));

Now I need to also pull in the semester and grade associated with the
duplicate rows. When I try creating a main query and using the above as a
subquery i get all of my rows back, not just the duplicates.
select grade, semester
from transcript
where exists
(SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1)));


If i try this query pasted below, i get asked to enter a parameter value???
when i do, i get all the rows,not the dupicates

select grade, semester, [studentid] & [courseid] as studentcourseid
from transcript
where studentcourseid in

(SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1)));

thanks for any help
 
trex44 said:
I used the dupicate wizrd in Access 07 to find dupes in my table; I have 5
columns: student id, courseid, semester, grade, and course year.
I identified the duplicates using this:
SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1));

Now I need to also pull in the semester and grade associated with the
duplicate rows. When I try creating a main query and using the above as a
subquery i get all of my rows back, not just the duplicates.
select grade, semester
from transcript
where exists
(SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1)));


If i try this query pasted below, i get asked to enter a parameter
value???
when i do, i get all the rows,not the dupicates

select grade, semester, [studentid] & [courseid] as studentcourseid
from transcript
where studentcourseid in

(SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1)));
Click to expand...
Hi trex,

An Access query gets parsed in steps.

First the join(s) if any.

Then the WHERE clause.

Then the SELECT clause.

--> an alias ("studentcourseid") established in the SELECT clause
will not yet be known in the WHERE clause
when the WHERE clause is processed.

try

select
t.grade,
t.semester,
(t.studentid & t.courseid) as studentcourseid
from transcript AS t
where (t.studentid & t.courseid) in

(SELECT [studentid] & [courseid] as studentcourseid
FROM TRANSCRIPT
GROUP BY TRANSCRIPT.[STUDENTID], TRANSCRIPT.[COURSEID]
HAVING (((Count(TRANSCRIPT.[STUDENTID]))>1) AND
((Count(TRANSCRIPT.[COURSEID]))>1)));

good luck,

gary
 
Back
Top