Finding wrong values for keyfield

[Guest]

I'm trying to identify data entry mistakes in one of our tables.
I have a table with Student ID's (primary not unique) and Lastnames. How can
I construct a query to find records where the same Student Id may have two
differant last names?

 

[Guest]

Well "primary not unique" is a little confusing. A primary key must be
unique. Therefore is Student ID the primary key field?

Anyway put in the correct table and field names below:

SELECT [Student ID],
Count([LastNames]),
First([LastNames]),
Last([LastNames])
FROM YourTable
GROUP BY [Student ID]
HAVING Count([LastNames]) > 1;

 

[John Spencer]

SELECT StudentID, LastName
FROM TheTable
WHERE StudentID in
(SELECT StudentID
FROM (SELECT DISTINCT StudentID, LastName FROM TheTable) as A
GROUP BY StudentID
HAVING Count(*) > 1)

You can do this by building and "stacking" queries
First Query
SELECT DISTINCT StudentID, LastName FROM TheTable
(In the query grid, select unique values and include just the two fields)

second query uses first query.
SELECT StudentID
FROM qOne
GROUP BY StudentID
HAVING Count(StudentID) > 1
(In the query grid, select the first query
-- Add the StudentId field to the query twice
-- Select View Totals
-- Change one GROUP BY to Count
-- Put > 1 as the criteria under Count

Now make a query with your table and the second query. Join studentID to
StudentID and you should get a list of all the students with more than one
last name


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..