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
Tidak ada komentar:
Posting Komentar