Hi Mike,
I think you want to do the following:
Query Name: Query1
Query Syntax:
SELECT TOP 1 STR, ACV, CITY
FROM DATA1
WHERE Data1.County=Test.County AND
STR Not In (SELECT STORE FROM TEST3) And
STR Not In (SELECT STORE2 FROM TEST3) And
STR Not In (SELECT STORE3 FROM TEST3);
Then create the following UPDATE query:
UPDATE TEST3, Query1
SET TEST3.str = [Query1].[str],
TEST3.acv = [Query1].[acv],
TEST3.city = [Query1].[city];
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| Thread-Topic: Update query with a Select statement
| thread-index: AcRTGX2zsNXGnn78ScCI5JTMZUx/wg==
| X-WBNR-Posting-Host: 12.109.161.4
| From: "=?Utf-8?B?TUs=?=" <
[email protected]>
| Subject: Update query with a Select statement
| Date: Tue, 15 Jun 2004 13:44:02 -0700
| Lines: 21
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA0
1.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:203997
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi,
|
| I'd like to run a select query (a little complicated one) to retrieve 3
values,
| then use these values to update a table. Can this be done ? Or must I
create a
| 3rd table and append into it from tables 1 & 2 ?
|
| Thanks,
| Mike
|
| UPDATE TEST3
| SET STR = Data1.STR
| SET ACV = Data1.ACV
| SET CITY = Data1.City
|
| SELECT TOP 1 STR, ACV, CITY
| FROM DATA1
| WHERE Data1.County=Test.County AND
| STR Not In (SELECT STORE FROM TEST3) And
| STR Not In (SELECT STORE2 FROM TEST3) And
| STR Not In (SELECT STORE3 FROM TEST3);
|
|
