Trouble with sub query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to structure a query that utilizes a sub-query to select all
records from tblTransactionsALL that do not appear in
qryTransactions-MultipleSponsorsCorrectBankerAllocations for a given
transaction number. For example:

tblTransactionsALL has 3 records affilliated with transaction #1;
qryTransactions-MultipleSponsorsCorrectBankerAllocations has correctly
selected 2 of the 3 records associated with transaction #1. I need to figure
out which of the three was not selected from tblTransactionsALL.

I should note that the [Banker] field is what will distinquish the each of
the three records from the others for a given transaction.

This is the most recent in a long line of failed SQL attempts:

SELECT DISTINCT qryTransactionsAll.TransactionID,
qryTransactionsAll.CompanyName, qryTransactionsAll.ProjectName,
qryTransactionsAll.TransactionType, qryTransactionsAll.DealStatus,
qryTransactionsAll.DealSize, qryTransactionsAll.GrossRevenue,
qryTransactionsAll.Probability, qryTransactionsAll.PWR,
qryTransactionsAll.[BAS Role], qryTransactionsAll.DealID,
qryTransactionsAll.Banker, qryTransactionsAll.BankerAllocation,
qryTransactionsAll.BankerPWR, qryTransactionsAll.Bucket
FROM qryTransactionsAll
WHERE (((qryTransactionsAll.Banker) Not In (SELECT [Banker] FROM
[qryTransactions-MultipleSponsorsCorrectBankerAllocations] WHERE
(([qryTransactions-MultipleSponsorsCorrectBankerAllocations].[TransactionID]=[qryTransactionsAll].[TransactionID])))));

Any guidance will be appreciated. Thanks in advance.
 
Jeff said:
I'm trying to structure a query that utilizes a sub-query to select all
records from tblTransactionsALL that do not appear in
qryTransactions-MultipleSponsorsCorrectBankerAllocations for a given
transaction number. For example:

tblTransactionsALL has 3 records affilliated with transaction #1;
qryTransactions-MultipleSponsorsCorrectBankerAllocations has correctly
selected 2 of the 3 records associated with transaction #1. I need to figure
out which of the three was not selected from tblTransactionsALL.

I should note that the [Banker] field is what will distinquish the each of
the three records from the others for a given transaction.

This is the most recent in a long line of failed SQL attempts:

SELECT DISTINCT qryTransactionsAll.TransactionID,
qryTransactionsAll.CompanyName, qryTransactionsAll.ProjectName,
qryTransactionsAll.TransactionType, qryTransactionsAll.DealStatus,
qryTransactionsAll.DealSize, qryTransactionsAll.GrossRevenue,
qryTransactionsAll.Probability, qryTransactionsAll.PWR,
qryTransactionsAll.[BAS Role], qryTransactionsAll.DealID,
qryTransactionsAll.Banker, qryTransactionsAll.BankerAllocation,
qryTransactionsAll.BankerPWR, qryTransactionsAll.Bucket
FROM qryTransactionsAll
WHERE (((qryTransactionsAll.Banker) Not In (SELECT [Banker] FROM
[qryTransactions-MultipleSponsorsCorrectBankerAllocations] WHERE
(([qryTransactions-MultipleSponsorsCorrectBankerAllocations].[TransactionID]
=[qryTransactionsAll].[TransactionID])))));
Click to expand...


Off the top of my head, I don't see why that doesn't provide
the records you want. However, that may be rather slow way
to do it. Try this instead:

SELECT DISTINCT A.TransactionID,
A.CompanyName,
. . .
FROM qryTransactionsAll As A
LEFT JOIN
[qryTransactions-MultipleSponsorsCorrectBankerAllocations]
As M
ON A.Banker = M.Banker
AND A.TransactionID = M.TransactionID
WHERE M.Banker Is Null
 
Thanks for your reply. It sent me in the right direction.

Marshall Barton said:
Jeff said:
I'm trying to structure a query that utilizes a sub-query to select all
records from tblTransactionsALL that do not appear in
qryTransactions-MultipleSponsorsCorrectBankerAllocations for a given
transaction number. For example:

tblTransactionsALL has 3 records affilliated with transaction #1;
qryTransactions-MultipleSponsorsCorrectBankerAllocations has correctly
selected 2 of the 3 records associated with transaction #1. I need to figure
out which of the three was not selected from tblTransactionsALL.

I should note that the [Banker] field is what will distinquish the each of
the three records from the others for a given transaction.

This is the most recent in a long line of failed SQL attempts:

SELECT DISTINCT qryTransactionsAll.TransactionID,
qryTransactionsAll.CompanyName, qryTransactionsAll.ProjectName,
qryTransactionsAll.TransactionType, qryTransactionsAll.DealStatus,
qryTransactionsAll.DealSize, qryTransactionsAll.GrossRevenue,
qryTransactionsAll.Probability, qryTransactionsAll.PWR,
qryTransactionsAll.[BAS Role], qryTransactionsAll.DealID,
qryTransactionsAll.Banker, qryTransactionsAll.BankerAllocation,
qryTransactionsAll.BankerPWR, qryTransactionsAll.Bucket
FROM qryTransactionsAll
WHERE (((qryTransactionsAll.Banker) Not In (SELECT [Banker] FROM
[qryTransactions-MultipleSponsorsCorrectBankerAllocations] WHERE
(([qryTransactions-MultipleSponsorsCorrectBankerAllocations].[TransactionID]
=[qryTransactionsAll].[TransactionID])))));
Click to expand...


Off the top of my head, I don't see why that doesn't provide
the records you want. However, that may be rather slow way
to do it. Try this instead:

SELECT DISTINCT A.TransactionID,
A.CompanyName,
. . .
FROM qryTransactionsAll As A
LEFT JOIN
[qryTransactions-MultipleSponsorsCorrectBankerAllocations]
As M
ON A.Banker = M.Banker
AND A.TransactionID = M.TransactionID
WHERE M.Banker Is Null
Click to expand...
 
Back
Top