Query to return only one value.

[Gwenk]

Hi, I need help please

Trying to do a select query where I only want one row returned if a field
has two values>

Example: Joe Bloggs can have two records one for SUBSTANTIVE and one for
HDA, however if both exist I only want HDA to be returned.
Name: Joe Bloggs
Duty Type: SUBSTANTIVE

2nd Record
Name: Joe Bloggs
Duty Type: HDA

I can get the query to retun just Substantive or just HDA but I have other
records that are ONLY Substantive - so I only want the records where they
have BOTH Substantive and HDA to return only HDA and all the other records to
return SUBSTANTIVE.

ANy help appreciated.

 

[Tom van Stiphout]

On Mon, 2 Nov 2009 19:30:02 -0800, Gwenk

Perhaps you could use an Exists clause:
select * from myTable where DutyType='HDA'
and exists(select T2.[Name] from myTable T2 where T2.[Name] =
myTable.[Name] and T2.DutyType='SUBSTANTIVE')

Alternatively you could have two queries, one returning HDA and one
returning SUBST records, and in a third query inner join them on
[Name], and set the Where clause to return only the HDA records.

-Tom.
Microsoft Access MVP

 

[Dale Fye]

Gwen,

You don't indicate what the rest of your query looks like, or whether you
want to return values from other fields that relate to the "HDA" record. If
all you want is name and Duty Type, you could do something like:

SELECT [Name], Min(yourTable.[Duty Type]) as [Duty Type]
FROM yourTable
GROUP BY [Name]

BTW, "Name" is a reserved word in Access. I strongly recommend against
using it as a field name.

If you want more fields than that, you might try:

SELECT yourTable.*
FROM yourTable
INNER JOIN (SELECT [Name], MIN([Duty Type])
FROM yourTable
GROUP BY [Name]) as Temp
ON yourTable.[Name] = Temp.[Name]
AND yourTable.[Duty Type] = Temp.[Duty Type]

 

[John Spencer]

If just the two fields

SELECT [Name], Min([Duty Type]) as FirstDuty
FROM [SomeTable]
GROUP BY [Name]

If you need other fields in the query you can post back for a solution for that.

In query design view:
== Add your table
== Add the Name and Duty Type fields
== Select View: Totals from the menu
== Change GROUP BY to Min under Duty Type

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

 

[Krzysztof Naworyta]

Juzer Gwenk <[email protected]> napisa³

| Trying to do a select query where I only want one row returned if a
| field has two values>
|
| Example: Joe Bloggs can have two records one for SUBSTANTIVE and one
| for HDA, however if both exist I only want HDA to be returned.
| Name: Joe Bloggs
| Duty Type: SUBSTANTIVE
|
| 2nd Record
| Name: Joe Bloggs
| Duty Type: HDA
|
| I can get the query to retun just Substantive or just HDA but I have
| other records that are ONLY Substantive - so I only want the records
| where they have BOTH Substantive and HDA to return only HDA and all the
| other records to return SUBSTANTIVE.

Because "HDA" is before "SUBSTANTIVE" in alphabetical order...

SELECT
*
FROM
TableX as t1
Where
Exists
(
Select 1
from TableX as t2
Where
t2.[Name]=t1.[Name]
Having
Min(t2.[Duty Type])=t1.[Duty Type]
)

 

[Silva]

I need help and can not access the new query box.