Selasa, 03 April 2012

Re: [MS_AccessPros] Create a query omitting related records (IN ?)

 

John,
Yup—it's a head spinner. Glad you're not giving up on me! ;-J Just to make sure we're on the same page—CustomerListOld is the old one which will be replaced with CustomerList once it's been completed. The new CustomerList cut down the number of queries needed for ClosedSellers status info and I'm hoping to duplicate that approach with the other statuses. But it still will be complicated once queries the other statuses are added.

Right now my plan is to:
1. Make queries similar to qCustomerListClosedBuyersDerived (without Contacts and qCustomerListContactNameSpID) for the statuses that are derived and one for the ones entered on the ContactsStatus table. Queries based on the following tables:
--a. LookingContacts (Buyers)
--b. SalesBuyers (Closed Buyers)
--c. ListingContacts (Sellers, Contacts, and Renters)
--d. ContactStatus (Entered rather than derived)
--e. ListingContacts and Sales (Closed Sellers)

2. Create a union query to combine those
3. Use that union query as the base of qCustomerList and adding Contacts table and qCustomerListContactNameSpID along with the other info that is currently on that query.

Purposes of CustomerList:
1. Reminder of contacts and what AER did and is doing with each person/couple. Therefore each time someone/couple lists, looks, buys, sells or rents, one or more lines will be created. If 3 individuals buy a property, each will have their own line. If 3 couples buy a property, each couple will have their own line. If a couple bought in 2007 they have one line. But if they divorce in 2013, once we disconnect them in the ContactsLink table they will each have their own line and if there is a new significant other/spouse that new person will be in the spouse columm.
2. Misc info to jog memory as to who they are
3. Other info my boss wants

Rows:
Refer to the persons/couple and associated events (list, sell, buy, rent, …)
Each row has a main Contact (ContactID)—If it is a single person it will be their ContactID. If a couple buys a house, it is the person who has the lowest Ordr for the transaction who is the main Contact for that line of the report. So if 2 couples and a single bought a house for a parent, 5 people are entered into the SalesBuyers table along with an Ordr for each person. Let's say Tom and Sue are one of the couples and Tom has the Ordr =1 and Sue has the Ordr =2. Bob and Mary are the second couple and Bob's Ordr is 4 and Mary's Ordr is 3. Paul is the single with Ordr 5. He will have his own line. For the line referring to Tom and Sue, Tom will be the Main Contact (Ordr =1) and Sue will have her ContactID in the SpouseID column. For the line referring to Bob and Mary, Mary will be the main Contact (Ordr 3 is less than Bob's 4) and Bob's ContactID will be in the SpouseID column. If Bob and Mary divorce 5 years later, at that point the Customer List will have a line for each of them for that transaction.

Columns:
1. LastName of ContactID for transaction. (Main Contact)
2. Spouse—current spouse. (regardless of who the spouse was when the transactions occurred) If same last name as ContactID then only first name, otherwise first and last name.
3.Mailing Address—current mailing address
4. City—current city for mailing address
5. ST—current state
6. Zip—current zipcode
7. Notes Re Contact or specific event-- Combination of:
--a. Notes from Contact table for main contact
--b. Address of the property the transaction involved
8.Agent—Agent involved in the transaction. So if this line is for a closed buyer, the agent will be taken from the Sales table.
9.Yr—Year the transaction occurred.
10. Status—See select box on CustomerList Form
--a. Buyer—currently looking or a potential looker. Several types of buyers
-----Normal (entered as normal in the ContactStatus table OR in LookingContacts table where the EndDate is null. The specific type of buyers listed below could also be a normal buyer if they are in the LookingContacts table, but in the Customer List the status listed will be the more precise one.
-----Investment (entered in the ContactStatus table). Could also be a normal buyer.
-----First Time (entered in the ContactStatus table) Could also be a normal buyer.
-----Pending (entered in the PendingContacts table). Will also be a normal buyer.
--b. Seller (From ListingContacts table if Listings.CurrentListing = -1)
--c. Potential Seller (entered in ContactStatus table)
--d. Past Seller (Previously listed with us and then listed with another realty. Entered in the ContactStatus table)
--e. Past Buyer (Looked with us but ended up buying elsewhere or looking with another realty, Entered into ContactStatus table )
--f. Closed Seller (Listed their house and sold with us or we represented the buyer as he bought another agency's listing. Derived from Sales and ListingContacts table or was entered into ContactStatus table.)
--g. Closed Buyer (Looked and bought with us representing them. Derived from SalesBuyers table or was entered into ContactStatus table.)
--h. Renter (From ListingContacts table)
--i. Contact for current seller (from ListingContacts table)

*****Most statuses are DERIVED based on whether they are in listings, sales, off, withdrawn, etc. tables BUT some were ENTERED in Contact_Status table because we need to have info about customers that predates 2011. Some statuses are not derived but are entered—ex past seller. For a couple the status would be entered on each contact record but on the customer list they would be combined.

Thanks Again!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> I don't get any errors when I open form CustomerListOld on my machine. I
> started to investigate the underlying queries, but it made my head spin around!
>
> Can you explain in English what it is you want to display on the final form or
> report? There's gotta be an easier way to do 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 mrsgoudge
> Sent: Friday, March 30, 2012 9:55 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)
>
>  
> 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]
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar