John, I would love that! When I haven't been sick, on vacation, or working on other tasks I've been getting the stuff ready to give you.
BUT today I'm realizing that the underlying queries need to be changed. I made the assumption that the husband/wife/partner names would always be in a certain order (ex: Paul would always be the main/first contact and Connie the secondary one). WRONG! *&$%#! (How do you say that? ;-))
I had created a several queries which each derived a status for a particular contact involved in an event (Listing, Buying, Selling, etc) (Status = Buyer, Past Buyer, Closed Buyer ,etc.). One or more query for each status. Then I made a union query of these and then combined with another query which had the proper order of spouses/partners taken from the ContactsLink table: Ex. Paul then Connie. But now I find the proper order is determined in the event.
What I'm doing is creating a Customer List Report as dictated to me by my boss. If Paul and Connie Barnett (married) have sold 3 different houses that would be 3 lines. --(LastName, FirstName, SpousesName, Current Address, ID of the related event, etc.)
If they had sold 3 different houses and their last names were different (ex: Paul Hanson and Connie Denler, then there would be 6 lines--3 with Connie first and 3 with Paul. If Connie is looking for a house for her father, that would be another line where Paul's name would be in the SpousesName column but not in a separate line even if his last name is different. If Paul and Connie were brother and sister then they would each have their own line and if they were each married, their spouses name would be in the spouse column. In each event there is an Ordr. The one with the lowest number should be the first listed.
So the initial queries that figure out the statuses also need to include the ContactID's that should be associated with that status/event and exclude the ContactsID's for the spouse if they have the same last name.
Here's the logic as I see it:
If 2 of the contacts in the EventContact table (ex: ListingContacts) are associated in the ContactsLink table with a TypeID1 of 114,115,116 (wife, husband, partner) AND they have the same last name then the one with the smaller Event.Ordr is the ContactID that should be in the queries results. Otherwise both ContactID's are included.
The ContactsLink table has the following related fields:
ContactID1, ContactID2, TypeID1, TypeID2. One record could have the following info with the ID portion fleshed out:
Paul Hanson, Connie Hanson, Husband, Wife.
(ContactID =102,ContactID =113,TypeID1 =115,TypeID2 =114)
Here's an example of one of the initial queries that is deriving a status that I'd like to adapt to account for the above scenario. It derives the Contacts with a "seller" (lister) or "contact" status.
SELECT DISTINCT Contacts.ContactID, IIf([SellerType_List].[Category]="s",2,IIf([SellerType_List].[Category]="c",11,"")) AS StatusID, Listings.ListID AS ID, DatePart("yyyy",Date()) AS Yr, Listings.AgentID, Listings.ListingAgency AS Agency, Listings.Notes AS Notes2, ListingContacts.Priority AS Ordr
FROM (Listings LEFT JOIN Withdrawn ON Listings.ListID = Withdrawn.ListID) INNER JOIN (Contacts INNER JOIN (ListingContacts INNER JOIN SellerType_List ON ListingContacts.SellerTypeID = SellerType_List.SellerTypeID) ON Contacts.ContactID = ListingContacts.ContactID) ON Listings.ListID = ListingContacts.ListID
WHERE (((Listings.ListingAgency)=1) AND ((Listings.CurrentListing)=-1) AND ((SellerType_List.Category)="s" Or (SellerType_List.Category)="c")) OR (((Listings.ListingAgency)=1) AND ((SellerType_List.Category)="s" Or (SellerType_List.Category)="c") AND ((Listings.ExpDate)>Date()) AND ((Withdrawn.ListID) Is Not Null));
Thanks a zillion!
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
>
>
> You're going to have to give us the underlying queries to solve this. And we
> might solve your "too many databases" problem in the bargain!
>
>
>
> John Viescas, author
>
> Microsoft Office Access 2010 Inside Out
>
> Microsoft Office Access 2007 Inside Out
>
> Building Microsoft Access Applications
>
> Microsoft Office Access 2003 Inside Out
>
> SQL Queries for Mere Mortals
>
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Monday, March 12, 2012 5:46 PM
> To: MS_Access_Professionals@yahoogroups.com;
> MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>
>
>
>
> Good morning! Or whatever it is where you are :-)
>
> I have a series of many queries that is used to create a Customer list which has
> one line per Customer/transaction. This is about the final query.
>
> Husbands/wives or partners haves two lines per transaction--one for each
> ContactID. A transaction (Pending, Off, Sold, Renting ...) can have an infinite
> number of contacts linked with it.
>
> I would like to only have one record per above mentioned relationship and have
> the person with the lowest Ordr in that relationship be the one in
> Contacts.ContactID column. The table that links them in relationship is
> ContactsLink. The fields are:
>
> ContactsLinkID
> TypeID1
> TypeID2
> ContactID1
> ContactID2
>
> Types 114, 115, 116 refer to husband,wife, partner. I have included TypeID1 in
> the query when it is 114,or 115 in case that helps.
>
> The Ordr comes from the related table PendingBuyers, ListingContacts,
> SalesBuyers. Sometimes there is no Ordr if the transaction was entered for a
> year before 2011.
>
> I am a bit concerned because this is the recordsource for one of the forms
> (which acts as a report) that gives me the "cannot open any more databases"
> messages when there are a couple of other forms open.
>
> Here's the query for the recordsource. I'd give you the underlying queries but
> there are MANY of them.
>
> SELECT qCustomerList_Pre1.ContactID, qCustomerList_Pre1.Last,
> qCustomerList_Pre1.First, qCustomerList_Pre1.Spouse, qCustomerList_Pre1.Mailing,
> qCustomerList_Pre1.City, qCustomerList_Pre1.ST, qCustomerList_Pre1.Zip,
> IIf([ContactFormNotes] Is Not Null,[ContactFormNotes] & "; " &
> [Notes2],[Notes2]) AS Notes, qCustomerListAllStatusYrListID.Yr,
> Status_List.Status, qCustomerListAllStatusYrListID.ID,
> IIf([SpID]="",Null,CLng([SpID])) AS SpouseID, Agent_List_1.AgentFN,
> qCustomerListAllStatusYrListID.Ordr, qCustomerList_Pre1.TypeID1
> FROM qCustomerList_Pre1 LEFT JOIN ((Status_List RIGHT JOIN
> qCustomerListAllStatusYrListID ON Status_List.StatusID =
> qCustomerListAllStatusYrListID.StatusID) LEFT JOIN Agent_List AS Agent_List_1 ON
> qCustomerListAllStatusYrListID.AgentID = Agent_List_1.AgentID) ON
> qCustomerList_Pre1.ContactID = qCustomerListAllStatusYrListID.ContactID
> WHERE (((Status_List.Status) Is Not Null)) OR
> (((qCustomerListAllStatusYrListID.ContactID) Is Not Null))
> ORDER BY qCustomerList_Pre1.Last, qCustomerList_Pre1.First;
>
> Thanks!
> Connie
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar