Thanks John--political issues are a part of life. Haven't encountered this intense before. Hanging onto my Higher Power :-)
Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Good luck with your political issues!
>
> Yes, UNION ALL is much less resource-intensive than a plain UNION. UNION has to
> build a temp index to find all the unique values.
>
> 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: Thursday, April 05, 2012 6:59 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Create a query omitting related records (IN ?)
>
>
> John-
>
> I have been using UNION rather than UNION ALL so might changing that help with
> the "too many databases message?" Is the "too many databases" message more
> likely to occur as the number of records increases?
>
> Her Excel spreadsheet has all the data visible at once (all statuses in one
> spreadsheet). She sorts it to get what she wants. Of course she sorts it by
> statuses which is where your AfterUpdate event change of recordsource would
> work.
>
> Another way we use it is to find what we've done with a person. That person's
> info could be x lines--a line each for Buyer, Seller, Contact4CurrentSeller,
> PastBuyer, 10 lines for Closed Seller, 5 lines for Closed Buyer, etc. Most of
> those statuses require their own query because of left joins in them.
>
> I do not know what other ways she uses the Customer List but my best guess is
> that she doesn't know herself and that she will opt to keep her Excel
> spreadsheet when she finds Access is different and not as "flexible". Which
> means we'll be entering the same data into Access and the spreadsheet. And a
> similar situation could occur for one or two other reports.
>
> I think I've not been smart in designing this query so I'm hoping that
> simplifying it will help and that hopefully I can simplify the other large
> queries.
>
> Part of this is that (as you know) change for the user is hard. Whenever I
> approach her with a question that relates to Access, she doesn't want to talk
> and doesn't have time. At this point my ability to influence is low. I never
> know when something will happen to put me in the doghouse, so as much as
> possible I need to give her what she's been used to along with the added
> benefits that a database gives.
>
> Thanks!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Connie-
> >
> > Please explain what you mean by "I can't pull a similar report." The potential
> > problems with the humongous query are:
> >
> > 1) It'll take forever to run
> > 2) It'll fail with "query too complex"
> > 3) It'll fail with "can't open any more databases"
> >
> >
> > 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, April 04, 2012 6:57 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] Create a query omitting related records (IN ?)
> >
> >
> > John,
> > The "list" of statuses is the status combo-box.
> > The EndDate is in the LookingEvent table. If it is entered, that event is
> ended
> > so the contacts are no longer "Buyers".
> > Yes, there could be many statuses for a person. For example if they bought 10
> > houses with us they will forever have 10 lines with "Closed Buyer" as status.
> > And if that person currently is selling a home they would have another line
> with
> > the status "Seller". Some statuses like Past Buyer and Buyer will not have
> more
> > than one line with that status. It is crazy! Maybe I'll create each query one
> by
> > one and if I have a question re best way to do that one, I'll ask for help.
> >
> > So I've a question re approach. What I've been trying to do is duplicate my
> > boss' excel spreadsheet that she's had since pre 2007. It extremely valuable
> to
> > her. Changing the Recordsource in the AfterUpdate event will mean that I can't
> > pull a similar report. What are the problems with a humongous query? Will more
> > problems crop us as the database grows?
> >
> > Thanks for sticking with me!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Connie-
> > >
> > > I find no "list" of statuses on the CustomerList form. And where is the End
> > > Date you talk about? I get some clue from looking at the Status_List table,
> > but
> > > I have no clue how you determine the status for each person listed in the
> > > Contacts table. That's the "head spinning" part. The Contact_Status table is
> > > also a puzzle. I can see several contact IDs that have multiple status
> > entries.
> > > Do you pick the "latest" one to determine the status? But some of the
> records
> > > have no Year entry, so it's impossible to determine when they were at what
> > > status.
> > >
> > > It would seem to me that the easiest way to tackle this is to use the
> > > AfterUpdate event of a Status combo box to dynamically change the Record
> > Source
> > > of the form to the UNION query that pulls the contacts with the status
> > selected.
> > > Don't try to put everyone with their status in one humongous query.
> > >
> > > 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, April 03, 2012 11:51 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: 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