ACCESS FEED: [MS_AccessPros] Omitting spouses from report

Selasa, 20 November 2012

[MS_AccessPros] Omitting spouses from report

Good morning/afternoon!

In fine tuning a major report I found a key error. The report is the Customer List. It is a composite of Closed Buyers (derived from Sales), Closed Sellers (derived from Sales), Buyers (derived from LookingEvents), Sellers (derived from Listings), Closed Buyers and Closed Sellers that were entered into a table because they were pre 2011, and three other categories that are derived.

In this post I'll focus on Closed Buyers. There can be an unlimited number of buyers for a Sale and they are entered into the SalesBuyers table. Fields for the SalesBuyers table are BuyerContactsID, ContactID, SaleID, Ordr.

Each individual is entered, so if a couple is buying along with an individual there would be three buyers. (Couple = married or partners) If two couples and an individual are buying there would be five entries.

The Customer List Report has one entry for each couple and one per single person. If a couple splits/divorces, the info for the couple should show up as two entries--one for each person. A couple could be listed an unlimited amount of times with the Closed Buyers category because there will be one line for each property they bought.

There is a table ContactsLink that links the individuals. Fields are ContactsLinkID, TypeID1, TypeID2, ContactID1, ContactID2. In the SQL below qCustomerList_2ContactNameSpID links the ContactID to a SpouseID being based upon the ContactsLink table. Its fields are ContactID1 and SpID.

The question is how to retain the individuals and the person from the couple whose SalesBuyers.Ordr is the highest while omitting the person from the couple whose SalesBuyers.Order is the lowest for that couple. One thing you should know is that if one person from the couple bought a business property with business partners, that person would end up being listed in the Customer List report (with the column for his/her spouse entered even thought the spouse didn't buy--just a reminder of who the spouse/partner is). So in one Sale one member of the couple would be Ordr number one and conceiveably the other member of the couple would be ordr number one in another Sale.

I've included the SQL from qCustomerList_2ClosedBuyersDerived. As you'll see I am using TOP 1 in the Ordr field which needs to go. SpID is the ContactID for the record's current spouse.

Hope this is clear! Thanks for being with me as I've walked through this database.

Connie

SELECT SalesBuyers.ContactID, 8 AS StatusID, DatePart("yyyy",[SoldDate]) AS Yr, Listings.ListID AS ID, Sales.AgentID, qCustomerList_2ContactNameSpID.SpID, "List" AS Tbl, Listings.PropertyName, Listings.HomeInfoID, Null AS Notes2
FROM (Listings INNER JOIN Sales ON Listings.ListID = Sales.ListID) INNER JOIN (SalesBuyers LEFT JOIN qCustomerList_2ContactNameSpID ON SalesBuyers.ContactID = qCustomerList_2ContactNameSpID.ContactID1) ON Sales.SalesID = SalesBuyers.SaleID
WHERE (((SalesBuyers.Ordr)=(SELECT Top 1 Ordr FROM SalesBuyers As Dupe WHERE SalesBuyers.SaleID = Dupe.SaleID)) AND ((Sales.BuyerAgencyID)=1) AND ((Sales.SoldDate) Is Not Null)) OR (((SalesBuyers.Ordr)=(SELECT Top 1 Ordr FROM SalesBuyers As Dupe WHERE SalesBuyers.SaleID = Dupe.SaleID)) AND ((Sales.SoldDate) Is Not Null) AND ((Listings.ListingAgency)=1));