ACCESS FEED: RE: [MS_AccessPros] Create a query omitting related records (IN ?)

Connie-

Did you ever solve this?

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/
(Paris, France)

---------------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, March 21, 2012 10:42 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Create a query omitting related records (IN ?)

Also provide a sample of what it is you're looking for in your report - all the
fields.

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/
(Paris, France)

------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, March 21, 2012 10:28 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Create a query omitting related records (IN ?)

Connie-

That gives me a headache. Any particular reason you're using DISTINCT?

I think we need to back up and look at the table design again. Can you lay it
out in a message or upload your database structure to Assistance Needed? (Don't
need the data necessarily.)

I'm off to Luxembourg until Saturday, so won't be able to respond again until
the weekend.

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/
(Paris, France)

---------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Tuesday, March 20, 2012 10:24 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)

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]
>