ACCESS FEED: Re: [MS_AccessPros] How to set up an update query

Thanks! I'll press on. I do want to study the WHERE clause and understand
exactly how it's working and will likely have some questions about that
later.

James
www.james-mc.com
Words To Live By

On Thu, Dec 1, 2011 at 9:23 AM, John Viescas <john@viescas.com> wrote:

> **
>
>
> James-
>
> Yes, that should select all the single occurrences of a combination of
> NameLast,
> NameFirst, and Zipcode in your table. Have confidence!
>
>
> 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 James
> McAlister
> Sent: Thursday, December 01, 2011 3:38 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] How to set up an update query
>
> I've started over on this because my thinking has been wrong. I believe
> that what I must do is 1) find all unique records (last name, first name,
> zip code) in Permits, 2) find all unique records in Registrations (last
> name, first name, zip code) and then 3) join these two queries (last name,
> first name, zipcode) to identify records in Registrations that match
> records in Permits.
>
> So here's how I've done it. First, I've tweaked the results of the Find
> Duplicates query wizard to locate unique records in Permits:
>
> SELECT Permits.NameLast, Permits.NameFirst, Permits.Zipcode
> FROM Permits
> WHERE (((Permits.NameLast) In (SELECT [NameLast] FROM [Permits] As Tmp
> GROUP BY [NameLast],[NameFirst],[Zipcode] HAVING Count(*)=1 And
> [NameFirst] = [Permits].[NameFirst] And [NameLast] = [Permits].[NameLast]
> And [Zipcode] = [Permits].[Zipcode])))
> ORDER BY Permits.NameLast, Permits.NameFirst, Permits.Zipcode;
>
> Then I've done the same thing for Registrations and then joined those two
> queries on last name, first name and zip code.
>
> This appears to give me the right results and runs fairly fast, but I don't
> really understand how the WHERE clause is working well enough to be
> confident that I'm doing the right thing.
>
> So, will this do what I need, or would some other approach be better.
>
> Thanks!
>
> James
> www.james-mc.com
> Words To Live By
>
> On Wed, Nov 30, 2011 at 11:30 AM, John Viescas <john@viescas.com> wrote:
>
> > **
> >
> >
> > James-
> >
> > Lemme see if I can spot an error:
> >
> >
> > UPDATE Registrations
> > SET Permits = 1
> > WHERE 1 = DCount("ID", "qryPermitsMatch", "[TEXT_NAME_LAST] = '" &
> > Registrations.[TEXT_NAME_LAST] & "' AND [TEXT_NAME_FIRST] = '" &
> > Registrations.[TEXT_NAME_FIRST] & "' AND [TEXT_RES_ZIP5] = '" &
> > Registrations.[TEXT_RES_ZIP5] & "'")
> >
> > Ah, yes - was missing an & toward the end.
> >
> >
> > 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 James
> > McAlister
> > Sent: Wednesday, November 30, 2011 6:09 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: Re: [MS_AccessPros] How to set up an update query
> >
> > Ah! Our friend DCOUNT! I suspected he might be in there somewhere but
> have
> > yet to understand the syntax with multiple criteria and the use of single
> > and double quotes properly. My first query is OK, but the second is
> giving
> > my a syntax error with the last set of quotes highlighted. Says there
> might
> > be an operand without an operator. Does this mean that one of the quotes
> is
> > misplaced or missing? I need to study how they really work so that I will
> > know next time.
> >
> > James
> > www.james-mc.com
> > Words To Live By
> >
> > On Wed, Nov 30, 2011 at 2:55 AM, John Viescas <john@viescas.com> wrote:
> >
> > > **
> >
> > >
> > >
> > > James-
> > >
> > > You have to fool Access into thinking you're not doing any totalling -
> > > usually
> > > with a Domain function like DCount.
> > >
> > > First, find all the registrations that match permits:
> > >
> > > qryPermitsMatch:
> > > SELECT Permits.ID,
> > > Registrations.TEXT_NAME_LAST, Registrations.TEXT_NAME_FIRST,
> > > Registrations.TEXT_RES_ZIP5
> > > FROM Permits INNER JOIN Registrations ON (Permits.NameLast =
> > > Registrations.TEXT_NAME_LAST) AND (Permits.NameFirst =
> > > Registrations.TEXT_NAME_FIRST) AND (Permits.Zipcode =
> > > Registrations.TEXT_RES_ZIP5)
> > >
> > > Now update only the ones that match on First, Last, and ZIP where the
> > > count is
> > > just 1:
> > > UPDATE Registrations
> > > SET Permits = 1
> > > WHERE 1 = DCount("ID", "qryPermitsMatch", "[TEXT_NAME_LAST] = '" &
> > > Registrations.[TEXT_NAME_LAST] & "' AND [TEXT_NAME_FIRST] = '" &
> > > Registrations.[TEXT_NAME_FIRST] & "' AND [TEXT_RES_ZIP5] = '" &
> > > Registrations.[TEXT_RES_ZIP5] "'")
> > >
> > > 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 James
> > > McAlister
> > > Sent: Wednesday, November 30, 2011 3:07 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] How to set up an update query
> > >
> > > I have a table Permits that contains a list of permit holders (first
> > > name, last name and zip code). No address or other info. What I'm
> > > trying to do is match these permit holders to the voters in my
> > > Registrations table (TEXT_REGISTRANT_ID is the primary key) to
> > > determine which voters are also permit holders. Since I can only match
> > > on the first name, last name and zip code, numerous names in Permits
> > > will result in multiple matches in Registrations. So with a totals
> > > query I'm trying to identify which matches are unique (i.e., no
> > > multiples). This is what I have for qryUniquePermits:
> > >
> > > SELECT Permits.ID, Count(Permits.ID) AS ID_Count,
> > > Registrations.TEXT_NAME_LAST, Registrations.TEXT_NAME_FIRST,
> > > Registrations.TEXT_RES_ZIP5
> > > FROM Permits INNER JOIN Registrations ON (Permits.NameLast =
> > > Registrations.TEXT_NAME_LAST) AND (Permits.NameFirst =
> > > Registrations.TEXT_NAME_FIRST) AND (Permits.Zipcode =
> > > Registrations.TEXT_RES_ZIP5)
> > > GROUP BY Permits.ID, Registrations.TEXT_NAME_LAST,
> > > Registrations.TEXT_NAME_FIRST, Registrations.TEXT_RES_ZIP5
> > > HAVING (((Count(Permits.ID))=1))
> > > ORDER BY Registrations.TEXT_NAME_LAST, Registrations.TEXT_NAME_FIRST;
> > >
> > > This appears to give me the desired results in identifying the unique
> > > matches (Count_ID=1), but I don't know what to do next. There is a
> > > Permits field in Registrations, and for those unique matches I'd like
> > > to set that Permits field to "1". I thought that an update query based
> > > on qryUniquePermits might work but it won't since the query isn't
> > > updatable.
> > >
> > > So what do I need to do to get this to work? I'd appreciate any
> guidance.
> > >
> > > James
> > > www.james-mc.com
> > > Words To Live By
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>