Web Development

Ask something
Question Locked

Access query question

0 helpful
3 replies

by a former member

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a database with data as follows:
ID STATUS
12345 O
12345 O
54321 C
54321 O
45678 C
45678 O
78901 C
78901 C

I would like to know a query that will allow me to pull IDs that, in this case, have the same status in each occurence. For example, I would need to return 12345 since both records are STATUS = O and 78901 since both records are STATUS = C
Thanks!!

3 replies

by Former member

Clarifications

by tony hopkinson

Select ID, Status From
(Select ID,Status, Count(*) as NumberOfTimes Group By ID, Status) innerquery
Where NumberOfTimes > 1

In access if you are using the GUI, do the count query, then another query to select more than one occurence from that.

PS innerquery is just dummy name for the subquery in the parntheses, SQL requires a 'table name'

This discussion is closed to new replies.