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

Kamis, 01 Desember 2011

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
>
>
>