Selecting the OPPOSITE of this SELECT statement 1

[cruz610]

This is my original code which selects all the assumptions that ahve not been chosen by the user:
SELECT tblAssumptions .*
FROM tblAssumptions LEFT JOIN tblTransAssumptions ON tblAssumptions.numAssumptionID = tblTransAssumptions.numAssumptionID
WHERE (((tblTransAssumptions.numAssumptionID) Is Null));
The following code selects all the assumptions that the Client has selected as well as the category and name of category the assumption belongs to. What I now need is basically the same thing as above, only including this client and category information:
SELECT tblAssumptionCats.*, tblAssumptions.strAssumption, tblAssumptions.numAssumptionCatID, tblClients.strClientName, tblTransAssumptions.*
FROM tblClients INNER JOIN ((tblAssumptionCats INNER JOIN tblAssumptions ON tblAssumptionCats.numAssumptionCatID = tblAssumptions.numAssumptionCatID) INNER JOIN tblTransAssumptions ON tblAssumptions.numAssumptionID = tblTransAssumptions.numAssumptionID) ON tblClients.numTransactionID = tblTransAssumptions.numTransactionID;
A nice complex query I cant seem to find a way to select everything that the client has not selected. Any suggestions?

 

[ZmrAbdulla]

Hi,
Can you try replacing "=" sign to "<>". This is the normal procedure if you want to avoid records containing the criteria value.
regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[JonFer]

Zmr, it is not quite as simple as that. These are join conditions for matching values between two tables. Just changing = to <> will give you a near-cartesian result.

My understanding is that Cruz is looking for missing records - where an Assumption ID from "tblAssumptions" is not in the "tblTransAssumptions" table for a client transaction.

To do this, you would change the INNER JOIN to a LEFT JOIN (or RIGHT JOIN) just between these two tables and use the same "Is NULL" criteria on the Assumption ID as in your first SQL example. Leave the other INNER JOINs alone.

 

[cruz610]

Which INNER JOIN should I be changing? I have tried changing them all but I am not getting the desired results. Thanks for your help so far!

 

[JonFer]

I looked at your query again and you should probably do this in two steps because you have multiple joins on tblAssumptions with different join types.

Step1 Joins:

Clients, Assumptions INNER JOIN AssumptionCat (produces all Assumptions for a Client because Clients is not joined)

Step2 Joins:

Step1 LEFT JOIN TransAssumptions (fills in Assumption info where present)


I believe you can combine these steps if you are using Access 2000 or better.

 

[cruz610]

Thanks for you help Jon. What I actually ended up doing was adding another column in the TransAssumption table that was a yes/no column. I then made an append query that would simply insert all the different assumptions for each client. Not the most efficient way but it ended up working in the end