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

Connie-

I don't see your file. Where did you put it?

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: Wednesday, March 28, 2012 7:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)

John,
I've uploaded the applicable info--Connie's Customer List. Would be wonderful if
you'd see something that would help solve the "too many databases" message!

I am using a form for the report since we want buttons for each line so a click
will bring up the related event, main contact or spouse.

Contacts for an event that are not a spouse/partner have their own line on the
form. Otherwise they are combined.

Form.CustomerList is the new one. It is for one status. After your approval I'll
adapt it for the other statuses and use a union query to put them together. Is
it best to try to open a table once and get all the info needed at that point?
If so, I'll need to try to do that with qCustomerListContactNameSpID. A new
assumption for this form is that the order for who should be the first contact
is determined by the event (Listing, looking, buying etc. so the Ordr that is
selected in that event is used).

Form.CustomerListOld is the original form minus the data that is in
CustomerList. It should give you a sense for what I'm trying to do even though
it's obviously problematic. An assumption here is that one person will always be
the first when their names are combined and this data is drawn from the
ContactsLink table. Wrong assumption.

A key part of the Customer List Form (which is used as a report) is the person's
status for the line of info. Each line represents one of the statuses in the
list below. Could have 5 lines of "Closed Buyer"for a person if they bought 5
properties with us, etc. Most statuses are derived based on whether they are in
sales, pending, withdrawn etc. tables but some were entered in Contact_Status
because we need to have info about customers in this form that predates 2011.

-Buyer—currently looking or a potential looker
-Past Buyer—Looked with us but ended up buying elsewhere
-Closed Buyer—Looked and bought with us representing them
-Seller—currently listed with us
-Past Seller—Previously listed with us and then listed with another realty
-Closed Seller—Listed their house and sold with us or we represented the buyer
as he bought another agency's listing

Tables—misc info:
HomeInfo (basic info on a house—not changed over time)
Contact_Status—pre 2011 status, year, etc are entered here. "Ent" in queries
ContactLink (relationships between contacts) is a fairly new addition so info re
spouses is also elsewhere.
LookingEvent (Opening of event for buyers, begin/end date etc.)
LookingContacts (I think the info re what a buyer is looking for will be moved
from this table in the future to its own table)
SellerType_List (Seller, renter, non seller to contact). I recognize that this
list needs to be changed because of now having the ContactLink table

Misc info on queries:
-qCustomerListContactNameRelWSp – Contacts with a husband, wife, or partner
-qCustomerListContactNameRelNotSpouse—Contacts without husband,wife or partner
but in the ContactsLink table
-qCustomerListContactNameRelWSpouseReversed—Reversing the order of husband and
wife. (Ex: Usually Kerri Bar is the first listed, but her spouse Ed Bra is a
Buyer since he's looking for a house for his mother. Kerri is associated with
him in the spouse/partner columns)
-qCustomerListOffNoStatusDerived (Those sellers who took their house off the
market and did not relist with anybody)
-qCustomerListSellersRelisted (Took off market and then relisted)

Thank you VERY much!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Run a query:
>
> UPDATE MyTable
> SET LastName = Left([LastName], 3)
>
> 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 27, 2012 11:53 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>
> John,
>
> How would I create and run code to change data in tables. For example: replace
> the last name with the first 3 letters of the last name?
>
> Thanks!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > John,
> >
> > I have one element working--got rid of the distinct and simplified it a bit.
> Before changing the rest I am planning on running it by you. Other work,
family
> emergencies, etc. have gotten in the way of getting it ready to send.
Hopefully
> will work today or tomorrow.
> >
> > Thanks for checking and being willing to look it over!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > 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]
> > > >
> > >
> >
>