Web Development



Access query question

I have a database with data as follows:
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Microsoft Office How to Articles... below is a link

by CG IT In reply to Access query question
Collapse -

The SQL is

by Tony Hopkinson In reply to Access query question

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'

Related Discussions

Related Forums