Only where a specific feild is unique.

With some minor changes to the below, I am testing on only one column
matching and the other not matching. It has been running a half hour so far
and does not seem like it is going to finish any time soon. Previously you
mention Indexing columns--Stupid me--what do you mean?
--
Tom


"John Spencer" wrote:

DANG IT!!!

The wizards won't build the query you need.

Open a new query
== SELECT yourtable from the list
== Add the fields you want to see to the grid
== In a blank field "cell" you will have to type
EXISTS ( SELECT *
FROM [NameOfYourTable] as Temp
WHERE Temp.[MR#] = [NameOfYourTable].[MR#]
AND TEMP.[Name] = [NameOfYourTable].[Name]
AND TEMP.Sex = [NameOfYourTable].Sex
AND TEMP.Ans [NameOfYourTable].Ans)
== In the criteria below this calculated column enter
TRUE

Run the query. If this does not work for you, do steps one and two above and
then post the SQL of your query.

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

If you do that I or someone can try to build the query you need and then post
it for you to copy.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tom wrote:
John

I use the wizards due to my lack of SQL experience or programing knowledge.
It apprears your suggestion may work but, need a little more specific details
of how to enter it and where. Your help and understanding is appreciated.
THANKS
.

Open your table in design view.
Click on the field
In the bottom pain, there should be an index property.
If Indexed is NO, Select Yes (Duplicates Okay)

Repeat for other fields.

The following may be faster. Build query one to find records where MR#, Name,
and Sex are the same but have at least two different answers.

SELECT [MR#],[Name],[Sex]
FROM SomeTable
GROUP BY [MR#],[Name],[Sex]
HAVING Max(Ans) Min(Ans)

Save that query and use it to return the desired results
SELECT SomeTable.*
FROM SomeTable INNER JOIN qDifferentAnswers
ON SomeTable.[MR#] = qDifferentAnswers.[MR#]
AND SomeTable.[Name] = qDifferentAnswers.[Name]
AND SomeTable.[SEX] = qDifferentAnswers.[SEX]

You can build the queries in design view
Query One: Saved a qDifferentAnswers
== Add your table
== Add fields MR#, Name, Sex, Ans
== SELECT View: Totals from the menu
== Change GROUP BY To MAX under the Ans field
== Enter criteria under the Ans field
MIN(Ans)
Save this query

Query Two
== Add your table and the query
== drag from MR to MR, from Name to Name, from Sex to Sex to set up the link
== Select the fields from the table that you want to see.

THE records returned by this query cannot be updated.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Tom wrote:
With some minor changes to the below, I am testing on only one column
matching and the other not matching. It has been running a half hour so far
and does not seem like it is going to finish any time soon. Previously you
mention Indexing columns--Stupid me--what do you mean?