John, repeating to make sure you get this.
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