Query to Find Records with only 1 transaction

[Steve Haack]

All,
I thought I posted this last week, but cannot fidn it.

I am working on a table that has transactions for registering for events. So
in my table, I have PersonIDNum and TransactionType. TransactionType will
equal 1 (for a registration), 2 (for cancellation), etc.

I want to find all of the people that registered, and exclude those that
also have a cancellation transaction. So I want to find all records where
Transaction Type =1 and exclude the recordf there is also a corresponding
record where Transaction Type =2.

Does that make sense?

Thanks,
Steve

 

[anlu]

Hi Steve,

To find the persons with exactly 1 transaction you can use this sql (where
you should substitute tblPersonTrans with the name of your table:

SELECT tblPersonTrans.PersonIDNum
FROM tblPersonTrans
GROUP BY tblPersonTrans.PersonIDNum
HAVING Count(tblPersonTrans.TransactionType)=1;

Regards,
anlu

 

[Steve Haack]

Anlu,
Thanks for the quick respone.

Maybe I should have given more background. There are other transaction codes
as well. So a person might multiple records with different transactions.

All will have a type 1 for sure, and maybe others. I need to find all of
them that have a record with type 1, AND DO NOT also have a record with type
2.

Seve

 

[anlu]

Hi Steve,

In that case, I think you could use something like this:

SELECT tblPersonTrans.PersonIDNum
FROM tblPersonTrans
WHERE TransactionType = 1
AND tblPersonTrans.PersonIDNum NOT IN
(SELECT tblPersonTrans.PersonIDNum FROM tblPersonTrans WHERE
TransactionType = 2)

Regards,
anlu

 

[Steve Haack]

Anlu,
That gave me exactly what I was expecting to get. Thanks for that. Now I
have another related question, if I may.

This returns back the PersonIDNum, which corresponds to the person's record
tblPerson. I want to use this query to lookup the first and last names in
tblPerson and display them in a list box. How would I modigy this query to
return that?

When I tried adding the tblPerson in the Query Design, it stopped returning
any records.

I really appreciate the help.

Steve

 

[anlu]

Steve,

I think the easiest way is to create a new query, that joins tblPerson with
the first query on PersonIDNum - this is a straightforward query and you can
simply select the first and last names from tblPerson.

Regards,
anlu

 

[Steve Haack]

That did it. Thanks for all your help.

Steve

Steve,

I think the easiest way is to create a new query, that joins tblPerson with
the first query on PersonIDNum - this is a straightforward query and you can
simply select the first and last names from tblPerson.

Regards,
anlu