Re: Same field different values

--Query 1
SELECT field1
FROM Table1
GROUP BY field1
HAVING COUNT(distinct field2) = 2
AND COUNT(distinct CASE WHEN field2 IN ('a', 'b')
THEN field2
END) = 2

--Query 2
SELECT field1
FROM Table1
WHERE field2 IN ('a', 'b')
GROUP BY field1
HAVING COUNT(distinct field2) = 2

Roy Harvey
Beacon Falls, CT

On Mon, 23 Jul 2007 08:00:23 -0700, gelangov@xxxxxxxxxxx wrote:

create table table1
(field1 varchar (8),
field2 varchar (4))

field1 and field2 together is unique.

Example data:

Field1 Field2
1 A
1 B
1 C
2 A
2 C
3 A
3 B
3 D
4 A
4 B

1. The query one should find all the Field1 that has Field2 value of
ONLY (A) and (B)
Here the result will be Field1, 4 (only one result)

2. The query two should find all the Field1 that has Field2 value of
(A)and (B)
Here the result will be Field1,1,3 and 4 (three results)

Right now, I am doing this way...Is there a more efficient way than
this?


-- Query one:


SELECT <a.field1> from

(SELECT <field1>, <field2> FROM table1

WHERE <field2> ='A'

UNION

SELECT <field1>, <field2> FROM table1

WHERE <field2> ='B'

UNION

SELECT <field1>, <field2> FROM table1

WHERE <field2> ='C') a

WHERE <a.field1> not in (SELECT <field1>

FROM table1 WHERE <field2> not in ('a', 'b', 'c'))

GROUP by <a.field1>

HAVING count(*) =3

order by <a.field1>


--query 2

SELECT <a.field1> from

(SELECT <field1>, <field2> FROM table1

WHERE <field2> ='a'

UNION

SELECT <field1>, <field2> FROM table1

WHERE <field2> ='b'

UNION

SELECT <field1>, <field2> FROM table1

WHERE <field2> ='c'

)a

GROUP by <a.field1>

HAVING count(*) =3

order by <a.field1>



Thank you so much !!!
.

Relevant Pages

  • Re: Group By, Max and Min
    ... SELECT field1, field2, field3, LAST, MIN, MAX ... I have (from a previous saved query) these fields: ...
    (microsoft.public.access.queries)
  • Re: Using flexible query criteria
    ... criteria for the query is based on the user's input in the main form. ... One of the user inputs is a field called "Vendor_Name" (in the form of ... and said that Field1 is the primary key and is an autonumber field, and Field2 is the field shown in the combo box, you must modify it to a UNION select, similar to ...
    (comp.databases.ms-access)
  • Re: need to delete record having two different fields with same identical value
    ... Assuming you're always comparing the same two fields (Field1 and Field2) ... Run the query to check it, and then turn it into a delete query. ... which might be a lot safer (I hate deleting data unless I ...
    (microsoft.public.access.gettingstarted)
  • Re: Count Problem
    ... Access MVP 2002-2005, 2007-2008 ... Query as below: ... But after running, the result of field2 is correct, while field1 is the total records number of table x, not what I wanted. ...
    (microsoft.public.access.queries)
  • Re: Query problem - How do I do this?
    ... You may be able to normalize your table with a union query ... SELECT 1 as Fld, Field1 as TheValue, Countas NumOf ... FROM tblTooManyFields ... SELECT 2, Field2, Count ...
    (microsoft.public.access.queries)