Only where a specific feild is unique.

[Tom]

I am looking for where "MR#", "Name", "Sex", match yet ONLY "Ans" not
matching. The data in other fields are to be listed regardless if they match
or not.("date")


Original Table
MR# Name Sex Age Ans date
123 Joe M 35 Yes 4-1-09
123 Joe M 35 No 4-20-09
125 Tom M 31 Yes 4-1-09
125 Tom M 31 Yes 4-20-09
130 Sue F 39 Yes 4-1-09
130 Sue F 39 Yes 4-10-09
130 Sue F 39 No 4-20-09

Query Should look like this: (where only unique "Ans"# are displayed)
MR# Name Sex Age Ans date
123 Joe M 35 Yes 4-1-09
123 Joe M 35 No 4-20-09
130 Sue F 39 Yes 4-1-09
130 Sue F 39 Yes 4-10-09
130 Sue F 39 No 4-20-09

 

[Jerry Whittle]

How come you want both Sue's that are Yes?

 

[Tom]

Without getting into to many details, I need it to review both Yes items with
the No Item. I know this might not be possible. If it is not, then how would
I end up with only the one Joe pair and one Sue pair.

Thanks Tom

 

[Jeff Boyce]

FYI

If your field name truely is "Name", you are using a word reserved by MS
Access. Both you and Access could get confused. Consider using a different
(?more informative) word for the field name.

Note2: if you are truely storing [Age], reconsider. Won't that field be
incorrect as soon as someone has a birthday? You are better off storing
DOB, then using a function to calculate Age.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

SELECT Main.*
FROM [SomeTable] As Main
WHERE Exists(
SELECT *
FROM SomeTable as Temp
WHERE Temp.[MR#] = Main.[MR#]
AND TEMP.[Name] = Main.[Name]
AND TEMP.Sex = Main.Sex
AND TEMP.Ans <> Main.Ans)

I would suggest that you make sure you have an index on EACH of the four
fields if your table is large.

You might be able to speed this up in a few ways. For instance, MR# seems to
be the same for each combination of Name, Sex, and Age. IF that is the case,
you don't need to check Name and Sex at all.

If this is too slow, post back for other things to try.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Is there a primary key field or a unique index in this table? Also what is
the table name?

 

[Tom]

No to both questions and the table name is GM. The sample I gave is copied
from another question that was close to mine but not quite the same. The
field nameds are genric. If you want actual field names etc. I can do that.

 

[Tom]

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
--
Tom

SELECT Main.*
FROM [SomeTable] As Main
WHERE Exists(
SELECT *
FROM SomeTable as Temp
WHERE Temp.[MR#] = Main.[MR#]
AND TEMP.[Name] = Main.[Name]
AND TEMP.Sex = Main.Sex
AND TEMP.Ans <> Main.Ans)

I would suggest that you make sure you have an index on EACH of the four
fields if your table is large.

You might be able to speed this up in a few ways. For instance, MR# seems to
be the same for each combination of Name, Sex, and Age. IF that is the case,
you don't need to check Name and Sex at all.

If this is too slow, post back for other things to try.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I am looking for where "MR#", "Name", "Sex", match yet ONLY "Ans" not
matching. The data in other fields are to be listed regardless if they match
or not.("date")


Original Table
MR# Name Sex Age Ans date
123 Joe M 35 Yes 4-1-09
123 Joe M 35 No 4-20-09
125 Tom M 31 Yes 4-1-09
125 Tom M 31 Yes 4-20-09
130 Sue F 39 Yes 4-1-09
130 Sue F 39 Yes 4-10-09
130 Sue F 39 No 4-20-09

Query Should look like this: (where only unique "Ans"# are displayed)
MR# Name Sex Age Ans date
123 Joe M 35 Yes 4-1-09
123 Joe M 35 No 4-20-09
130 Sue F 39 Yes 4-1-09
130 Sue F 39 Yes 4-10-09
130 Sue F 39 No 4-20-09

.

 

[John Spencer]

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

 

[John Spencer]

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]

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

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

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

.

 

[John Spencer]

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