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

Rabu, 30 November 2011

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