Query to find offstting transactions

[Steve Haack]

I have a table, tblTransactions. In there I have some transactions which
represent a person registering for an event, and some which represent
cancelling a registration.

each record has an EventID, PersonID, and a Code (1 for Registration, 2 for
Cancellation, etc.)

On a form, I want to have a listbox that has the events that someone has
registered for. I assign a query to the listbox to populate it by finding the
records with the PersonID that I am interested in.

Is there a way in my query that I can include only the events where there is
ONLY a registration record, and exclude those where where was a registration
and a cancellation?

Thanks,
Steve

 

[KARL DEWEY]

Do not use the quotes around the number if you Code field is a number a mine
was text --
[tblTransactions_Reg-Cnl]
SELECT tblTransactions.EventID, tblTransactions.PersonID,
tblTransactions.Code, tblTransactions_1.Code
FROM tblTransactions INNER JOIN tblTransactions AS tblTransactions_1 ON
(tblTransactions.PersonID = tblTransactions_1.PersonID) AND
(tblTransactions.EventID = tblTransactions_1.EventID)
WHERE (((tblTransactions.Code)="1") AND ((tblTransactions_1.Code)="2"));

SELECT tblTransactions.EventID, tblTransactions.PersonID
FROM tblTransactions LEFT JOIN [tblTransactions_Reg-Cnl] ON
(tblTransactions.PersonID = [tblTransactions_Reg-Cnl].PersonID) AND
(tblTransactions.EventID = [tblTransactions_Reg-Cnl].EventID)
WHERE ((([tblTransactions_Reg-Cnl].PersonID) Is Null) AND
(([tblTransactions_Reg-Cnl].EventID) Is Null));

 

[Steve Haack]

Karl,
Thanks for the guidance. One question, do I need both of these SELECT
statements, or are they 2 different ways to get the same thing? If I need
both, then how do I apply them to a ListBox as the RowSource?

Thanks,
Steve

Do not use the quotes around the number if you Code field is a number a mine
was text --
[tblTransactions_Reg-Cnl]
SELECT tblTransactions.EventID, tblTransactions.PersonID,
tblTransactions.Code, tblTransactions_1.Code
FROM tblTransactions INNER JOIN tblTransactions AS tblTransactions_1 ON
(tblTransactions.PersonID = tblTransactions_1.PersonID) AND
(tblTransactions.EventID = tblTransactions_1.EventID)
WHERE (((tblTransactions.Code)="1") AND ((tblTransactions_1.Code)="2"));

SELECT tblTransactions.EventID, tblTransactions.PersonID
FROM tblTransactions LEFT JOIN [tblTransactions_Reg-Cnl] ON
(tblTransactions.PersonID = [tblTransactions_Reg-Cnl].PersonID) AND
(tblTransactions.EventID = [tblTransactions_Reg-Cnl].EventID)
WHERE ((([tblTransactions_Reg-Cnl].PersonID) Is Null) AND
(([tblTransactions_Reg-Cnl].EventID) Is Null));

--
Build a little, test a little.

I have a table, tblTransactions. In there I have some transactions which
represent a person registering for an event, and some which represent
cancelling a registration.

each record has an EventID, PersonID, and a Code (1 for Registration, 2 for
Cancellation, etc.)

On a form, I want to have a listbox that has the events that someone has
registered for. I assign a query to the listbox to populate it by finding the
records with the PersonID that I am interested in.

Is there a way in my query that I can include only the events where there is
ONLY a registration record, and exclude those where where was a registration
and a cancellation?

Thanks,
Steve

 

[KARL DEWEY]

The two queries work together as the first pulls list where person registered
and cancelled. The second uses the first to eliminate those people that
registered and cancelled.
--
Build a little, test a little.

Karl,
Thanks for the guidance. One question, do I need both of these SELECT
statements, or are they 2 different ways to get the same thing? If I need
both, then how do I apply them to a ListBox as the RowSource?

Thanks,
Steve

Do not use the quotes around the number if you Code field is a number a mine
was text --
[tblTransactions_Reg-Cnl]
SELECT tblTransactions.EventID, tblTransactions.PersonID,
tblTransactions.Code, tblTransactions_1.Code
FROM tblTransactions INNER JOIN tblTransactions AS tblTransactions_1 ON
(tblTransactions.PersonID = tblTransactions_1.PersonID) AND
(tblTransactions.EventID = tblTransactions_1.EventID)
WHERE (((tblTransactions.Code)="1") AND ((tblTransactions_1.Code)="2"));

SELECT tblTransactions.EventID, tblTransactions.PersonID
FROM tblTransactions LEFT JOIN [tblTransactions_Reg-Cnl] ON
(tblTransactions.PersonID = [tblTransactions_Reg-Cnl].PersonID) AND
(tblTransactions.EventID = [tblTransactions_Reg-Cnl].EventID)
WHERE ((([tblTransactions_Reg-Cnl].PersonID) Is Null) AND
(([tblTransactions_Reg-Cnl].EventID) Is Null));

--
Build a little, test a little.

I have a table, tblTransactions. In there I have some transactions which
represent a person registering for an event, and some which represent
cancelling a registration.

each record has an EventID, PersonID, and a Code (1 for Registration, 2 for
Cancellation, etc.)

On a form, I want to have a listbox that has the events that someone has
registered for. I assign a query to the listbox to populate it by finding the
records with the PersonID that I am interested in.

Is there a way in my query that I can include only the events where there is
ONLY a registration record, and exclude those where where was a registration
and a cancellation?

Thanks,
Steve

 

[John Spencer]

Try this query (not updatable)
SELECT T.EventID, T.PersonId, T.Code
FROM tblTransactions as T LEFT JOIN
(SELECT EventID, PersonID
FROM tblTransactions
WHERE T.Code = 2) as T2
ON T.EventID = T2.EventID
AND T.PersonID = T2.PersonID
WHERE T2.PersonID is Null

If you needed to be able to update records then you might need something along
the lines of the following. Also if the Code values are text and not numeric
then you will need quotes around "2".

SELECT EventID, PersonID, Code
FROM tblTransactions
WHERE Code = 1
AND NOT Exists
(SELECT *
FROM tblTransactions as TEMP
WHERE Temp.EventID = tblTransactions.EventID
AND Temp.PersonID = tblTransactions.PersonID
AND Code = 2)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve Haack]

John,
You second option below worked perfectly. However I have a follow up
question. the transactions table is linked to a Person table via the
PersonID. I would like to use this query to populate a list box with the
Person First Name and Last Name that is associated with the records returned
by this query. Anything I have tried in the query tools makes all of the
records disappear. Any ideas on how to take this query and have it return
people names?

 

[John Spencer]

Probably something like the following

SELECT tblPersonID.FirstName
, tblPersonID.LastName
, tblTransactions.EventID
, tblTransactions.PersonID
, tblTransactions.Code
FROM tblTransactions INNER JOIN tblPersons
ON tblTransactions.PersonID = tblPersons.PersonID
WHERE Code = 1
AND NOT Exists
(SELECT *
FROM tblTransactions as TEMP
WHERE Temp.EventID = tblTransactions.EventID
AND Temp.PersonID = tblTransactions.PersonID
AND Code = 2)

If there are multiple records for the person in the transactions table you
might need to use the DISTINCT keyword and trim the number of fields being
returned to eliminate the duplicates.


SELECT DISTINCT tblPersonID.FirstName
, tblPersonID.LastName
, tblTransactions.PersonID
, tblTransactions.Code
FROM tblTransactions INNER JOIN tblPersons
ON tblTransactions.PersonID = tblPersons.PersonID
WHERE Code = 1
AND NOT Exists
(SELECT *
FROM tblTransactions as TEMP
WHERE Temp.EventID = tblTransactions.EventID
AND Temp.PersonID = tblTransactions.PersonID
AND Code = 2)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County