Thanks for responding Bill! I did mean the actual data since I was getting ready to upload a file and I wanted to change our contact's personal data. Now I've learned how to do that :-)
Connie
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Connie
>
> I assume you mean change the display and not the actual data.
>
> In your query, add a field to do this. On the line for the field's name put something like this:
> ClientCode: Left(LastName,3)
>
> Bill Mosca
>
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > 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