John,
I'm not at work since I don't work Fridays so I'm working from memory.
Sorry--I must not have been clear. You are right, Customer List is working and as far as I can see is ordering them aright. I do not always want the males to be first. It's based upon each transaction so the Ordr in the relevant event will determine who is ContactID and who is SpouseID. I had not realized this when first creating it and that's why I'm redoing it.
The CustomerListOld form is the one that was giving me the "cannot open any more databases" message. It happened when I had other forms open. Hopefully, decreasing the number of queries behind the CustomerList form will keep this from being a problem
1. Would you please check the queries behind the CustomerList?
--a. The qCustomerList...SpID queries the Contact form. Should I be getting all relevant Contact info at that point so that the Contact table doesn't need to be opened again later in another query? Everything in this query will be joined with about 6 queries later so if I add more fields from Contact table there will be more info to be joined. Makes a difference?
--b. I believe that there will be a union query of 6 or so queries (each with the relevant status info). Any tips on creating efficient union query? qCustomerListClosedSeller is the one I will use as a template for the others. And all of them will be joined together and that union query will be used to create qCustomerList.
I'm looking forward to getting to work on this once I have your feedback. Tis the season for more houses to be put on the web thus giving me less time to work on Access.
Thanks again!
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Upon further reflection, I think you can get the "male" spouses to always appear
> first by changing qCustomerListContactNameSPID to this:
>
> SELECT ContactsLink.ContactID1, ContactsLink.ContactID2 As SpID
> FROM ContactsLink
> WHERE (((ContactsLink.TypeID1) In (115,116)))
> UNION SELECT ContactsLink.ContactID2, ContactsLink.ContactID1 As SpID
> FROM ContactsLink
> WHERE (((ContactsLink.TypeID1) In (115,116)))
> UNION SELECT ContactsLink.ContactID1, ContactsLink.ContactID2 As SpID
> FROM ContactsLink
> WHERE (ContactsLink.TypeID1 = 114) And ((ContactsLink.TypeID2 <> 115) Or
> (ContactsLink.TypeID2 IS NULL))
> UNION SELECT ContactsLink.ContactID2, ContactsLink.ContactID1 As SpID
> FROM ContactsLink
> WHERE (ContactsLink.TypeID2 = 114) And ((ContactsLink.TypeID1 <> 115) Or
> (ContactsLink.TypeID1 IS NULL));
>
> Note that the first two are as in your original query, except I don't pick up
> TypeID 114 - spouse. Those should be gathered as a pair to the 115 (husband)
> records. The last two pick up any "stray" 114 records where the partner ID
> isn't 115 or is blank.
>
> 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: Thursday, March 29, 2012 8:15 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: RE: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>
> Connie-
>
> The Customer List form opens for me just fine with no errors. But it does have
> entries that don't meet your criteria, such as:
>
> Contact 1 Spouse
> Bur Cari Matthew
>
> If I understand your requirement correctly, you want Matthew listed as the
> Contact 1 and Cari listed as the Spouse, correct? (How sexist is THAT?)
>
> If that's the case, I'll start playing with it after you confirm.
>
> 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 10:01 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>
> It's in Files> 2_AssistanceNeeded>MSAccessPro3.26.12.accdb . Sorry--should have
> realized the name was the name of the file I uploaded.
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > 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]
> > > > > >
> > > > >
> > > >
> > >
> >
>
