Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My data looks like so:

Date Identifier Type Volume
20060302 611654 A 22
20060302 611654 A 22
20060302 611660 A 10
20060302 611660 B 10

Is it possible to pull out only the records where the identifier and type
are equal ?

In this example, the query would result with:

Date Identifier Type Volume
20060302 611654 A 22
20060302 611654 A 22

Thank you in advance.
 
Your question does not make sense, the identifier and type are NOT equal in
your example.
Perhaps you mean rows where the values of both identifier and type are
duplicated in more than one row?

-Dorian
 
thank you for looking at my question.

In the example, the identifier in record 1 and record2 (row1 and row2) is
611644, the type for both records is A.

Does that clarify ?
 
Try this:

SELECT T1.*
FROM YourTable AS T1
WHERE
(SELECT COUNT(*)
FROM YourTable AS T2
WHERE T2.Identifier = T1.Identifier
AND T1.Type = T2.Type) > 1;

Ken Sheridan
Stafford, England
 
Thank you Ken. I get the following error message:

The Microsoft Jet database engine does not recognize 'T1*' as a valid field
name or expression.

Can you please advise ?
 
It looks like you've omitted the dot between T1 and *. It should be T1.*,
not T1*. T1 is here an alias for the table which distinguishes it from T2,
the other alias for the same table in the subquery. This enables the two
instances of the same table to be correlated so the query returns all rows
where the count of rows in the subquery when the Identifier and Type values
are the same as the outer query's current row is more than one.

Ken Sheridan
Stafford, England
 
Back
Top