Query to Find Records with only 1 transaction
in [Queries]
|
Prev: Custom Counter Question (Repost)
Next: Inverted Query
From: Steve Haack on 7 Nov 2009 16:20 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
From: anlu on 7 Nov 2009 16:36 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" wrote: > 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
From: Steve Haack on 7 Nov 2009 16:49 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" wrote: > 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" wrote: > > > 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
From: anlu on 7 Nov 2009 17:33 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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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
From: Steve Haack on 7 Nov 2009 18:26
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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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 |