Kamis, 05 April 2012

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

 

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