Only where a specific feild is unique.

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

--
Tom

How come you want both Sue's that are Yes?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom" wrote:

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

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


--
Tom


"Jerry Whittle" wrote:

How come you want both Sue's that are Yes?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom" wrote:

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

--
Tom

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.

"Tom" wrote in message
...
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

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

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

Is there a primary key field or a unique index in this table? Also what is
the table name?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom" wrote:

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


--
Tom


"Jerry Whittle" wrote:

How come you want both Sue's that are Yes?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom" wrote:

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

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


"Jerry Whittle" wrote:

Is there a primary key field or a unique index in this table? Also what is
the table name?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom" wrote:

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


--
Tom


"Jerry Whittle" wrote:

How come you want both Sue's that are Yes?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Tom" wrote:

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

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


"John Spencer" wrote:

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

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

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