Rabu, 05 Oktober 2011

RE: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal Text Values

Are City and ST unique/primary key fields in the table [7-26-22]? Typically a query with more than one table should be joined on primary/foreign key fields in order to be updateable.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: yrichter@hotmail.com
Date: Wed, 5 Oct 2011 20:47:57 +0000
Subject: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal Text Values


John,

Any reason why the resulting query is not updatable?

Isaac

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Isaac-
>
> Ah, I didn't notice that you had included a join on StationName, which nullifies
> the WHERE clause. Try this:
>
> SELECT tbl_Property.City, tbl_Property.State, tbl_Property.Zip,
> tbl_Property.StationName, tbl_Property.OnClosingList
> FROM tbl_Property INNER JOIN [7-26-11] ON (tbl_Property.City = [7-26-11].City)
> AND (tbl_Property.State = [7-26-11].ST)
> WHERE (((InStr([tbl_Property].[StationName],[7-26-11].[StationName]))<>0));
>
> That returns 3,447 rows.
>
> 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)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of I
> Sent: Wednesday, October 05, 2011 6:39 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: What Query Builder Function do I Use to Find Equal
> Text Values
>
> Thank you so much for your quick reply.
>
> Unfortunately, your SQL returned the same number of results as my version. I
> uploaded my test file (Closing Test.zip) into the Assistance Needed folder. Just
> for background the table 7-26-11 is a list of Post Office here in the States
> that are being considered for closure. The table Property is a list of all Post
> Offices. We are trying to check off on the Property table every Post Office that
> has been marked for closure. I've been working with MVP's Juan Soto and Ben
> Clothier but we didn't get much further than this and decided to do it manually.
> I just can't leave a challenge alone though, so I have kept it at the back of my
> mind and when I saw a similar post I thought I'd enter the fray.
>
> Isaac
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Isaac-
> >
> > Yes, it does, but it's not obvious.
> >
> > SELECT tbl_Property.City, tbl_Property.State, tbl_Property.Zip,
> > tbl_Property.StationName, tbl_Property.OnClosingList
> > FROM tbl_Property INNER JOIN [7-26-11] ON ([7-26-11].StationName =
> > tbl_Property.StationName) AND (tbl_Property.City = [7-26-11].City) AND
> > (tbl_Property.State = [7-26-11].ST)
> > WHERE InStr(tbl_Property.StationName, [7-26-11].[StationName]) <> 0;
> >
> >
> > 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)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of I
> > Sent: Wednesday, October 05, 2011 5:43 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: What Query Builder Function do I Use to Find
> Equal
> > Text Values
> >
> > I have struggled with a similar task:
> >
> > Where Property.StationName contains a string like "Bayview Station" and
> > [7-26-11].[StationName] contains "Bayview"
> >
> > I try using something like this:
> >
> > SELECT tbl_Property.City, tbl_Property.State, tbl_Property.Zip,
> > tbl_Property.StationName, tbl_Property.OnClosingList
> > FROM tbl_Property INNER JOIN [7-26-11] ON ([7-26-11].StationName =
> > tbl_Property.StationName) AND (tbl_Property.City = [7-26-11].City) AND
> > (tbl_Property.State = [7-26-11].ST)
> > WHERE (((tbl_Property.StationName) Like "*" & [7-26-11].[StationName] & "*"));
>
> >
> > I think the problem is just that Access doesn't use LIKE the way SQL Server
> > would use CONTAINS so unless you have an exact match you can't query
> > successfully even using *
> >
> > What's interesting is that in datasheet view you can manually enter a CONTAINS
> > filter and Access will find the string wherever it is in the field.
> >
> > Thanks,
> >
> > Isaac Richter
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno" <graham@>
> > wrote:
> > >
> > > Hi Terry
> > >
> > > Further to what John said, if you have a problem with leading or trailing
> > > spaces, then you can use the Trim() function:
> > >
> > > WHERE Trim(Table1.Column) = Trim(Table2.Column)
> > >
> > > If there are trailing spaces that really should not be there then you can
> > > Trim() them in an update query:
> > >
> > > UPDATE Table1 SET Column = Trim(Column) WHERE Column<>Trim(Column);
> > >
> > > Best wishes,
> > > Graham
> > >
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
> > > > Sent: Sunday, 2 October 2011 06:52
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: RE: [MS_AccessPros] What Query Builder Function do I Use to Find
> > > Equal Text Values
> > > >
> > > >
> > > > Terry-
> > > >
> > > > Clearly, one of the two values doesn't exactly equal the other. Is there a
> > > > blank at the end of the string in either table? LIKE should work as long
> > > as
> > > > you're comparing to the shorter string.
> > > >
> > > > WHERE Table1.Column LIKE Table2.Column & "*"
> > > >
> > > > 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)
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terry Olsen
> > > > Sent: Saturday, October 01, 2011 6:15 PM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] What Query Builder Function do I Use to Find
> > > Equal Text
> > > > Values
> > > >
> > > > Hi
> > > > I have two tables, each having a text column with this value
> > > > "P01_V0041_U0000.YC0". Using = returns nothing. Using Like returns more
> > > than I
> > > > need.
> > > > Is it possible to compare two text types?
> > > > Thanks
> > > > Terryomsn
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>


[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

Tidak ada komentar:

Posting Komentar