Query Question

[mlr0911]

I have some data that I am trying to extract. I basically need the following

If an Account Number has multiple transactions, I only want those listed.

Example Table

Account NumberField TransactionField

1234567891 Transaction1
1234567891 Transaction2
1234567891 Transaction3
1234567891 Transaction4

1234567890 Transaction1

1472583699 Transaction1
1472583699 Transaction2



Does anyone have any ideas how I can do this?

Thanks in advance for your help.

 

[Michael J Ross]

There may be a better way, but I'd do it using 2 queries.

The first is a totals query , group by Account NumberField and do Count on TransactionField. Enter criteria of >1 under TransactionField

Then create a second query which joins query 1 to your table on Account NumberField and run it putting the fields from your table in the query, that should bring back only those records where more than 1 transaction exists

 

[Bungle]

Solution using a single query

You can do as Michael suggests, but it in a single query, with the following SQL

SELECT AccountNumberField, TransactionField FROM ExampleTable WHERE AccountNumberField IN (
SELECT AccountNumberField FROM ExampleTable GROUP BY AccountNumberField HAVING COUNT(TransactionField)>1);