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

Rabu, 05 Oktober 2011

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

Duane is correct. The combination of City and State in the two tables is not
unique in either, so you get the Cartesian Product across city and state, which
will not be updatable.

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 Duane Hookom
Sent: Wednesday, October 05, 2011 11:51 PM
To: Access Professionals Yahoo Group
Subject: 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