ACCESS FEED: [MS_AccessPros] query problem

Senin, 31 Desember 2012

[MS_AccessPros] query problem

Happy New Year!

I'm going to be spending this holiday on Access. I've been at a stalemate so decided to ask for help. As Poirot would say "the little grey cells, they are having difficulty" :-)

I have a query that pulls all the people that have bought using our agency or have bought one of our properties. If a couple has bought a house, there is one line for that transaction. If a single has bought there is one line for that transaction. If one half of a couple has bought there is one line for that transaction.

The part I'm having problems is where one person from a couple has bought. If the main person has had only one transaction and the spouse has not been entered in any transactions, there's no problem. But if there are several transactions and the spouse/partner was involved in one of them but not in all of them, the query does not have the transaction in which he/she was not involved.

I believe the problem originates from the joining of the qCustomerList_2ContactNameSpID.ContactID1.SpID to the SalesBuyers_1 table. Since the spouse/partner is not null in one instance (SalesBuyers_1.ContactID) and the criteria says that it is null, it does not show up. But... what do I do .. ? Can't get my head around this.

This query is one of 4 similar queries that feed into a Union query (Buyers, Sellers, ClosedBuyers, ClosedSellers), so whatever is done here will be duplicated in three other queries. I'd love it if there's a way to minimize the opening of tables since I've encountered the "cannot open databases" table here in the past and have repeated tried to simplify these queries and remove the subqueries.

The query is below.

Thanks much!
Connie

SELECT Sales.SalesID, Contacts.LastName, Contacts.FirstName, Sales.ListID, Listings.ListingAgency, Sales.BuyerAgencyID, qCustomerList_2ContactNameSpID.SpID, SalesBuyers_1.ContactID, SalesBuyers_1.SaleID
FROM (Listings INNER JOIN Sales ON Listings.ListID = Sales.ListID) LEFT JOIN (Contacts RIGHT JOIN ((SalesBuyers LEFT JOIN qCustomerList_2ContactNameSpID ON SalesBuyers.ContactID = qCustomerList_2ContactNameSpID.ContactID1) LEFT JOIN SalesBuyers AS SalesBuyers_1 ON qCustomerList_2ContactNameSpID.SpID = SalesBuyers_1.ContactID) ON Contacts.ContactID = SalesBuyers.ContactID) ON Sales.SalesID = SalesBuyers.SaleID
WHERE (((Listings.ListingAgency)=1) AND ((SalesBuyers_1.ContactID) Is Null)) OR (((Sales.BuyerAgencyID)=1) AND ((SalesBuyers_1.ContactID) Is Null));