Writing an Update Query based on 2 criteria

[Hanif Merali]

Hello I'm having some trouble writing an update query that updates a
certain field in a table based on 2 criteria. The Table has 3 fields
that I'm going to be using a CUSTOMERNUM field a ADD1 field and a DNP
field. I want to update the DNP field to equal 9 if ADD1 is
duplicated but I only wanna update the DNP field for all of the
duplicates but 1 based on the customer number.

An example of this would be:

CUSNUM ADD1 DNP
2 22 Hunting
1 22 Hunting
5 22 Hunting

I'd want the table to look like this after running the query

CUSNUM ADD1 DNP
2 22 Hunting 9
1 22 Hunting 9
5 22 Hunting

I want the GREATEST CUSNUM value to not be updated but all other
cusnum values that had duplicate ADD1 field to be updated to DNP=9.
This seems pretty confusing but I hope you understand what I am trying
to accomplish. Thanks in advance for your help!


Sincerly,

Hanif Merali

 

[prabha]

Hi Hanif,

Here's what you can do (easy to maintain and troubleshoot):

1 - Create a MakeTable query based on your table and call the resulting
table "temp". The query SQL Syntax would be
SELECT Table1.ADD1, Max(Table1.CUSNUM) AS MaxOfCUSNUM
INTO temp
FROM Table1
GROUP BY Table1.ADD1;

2 - Create your Update query based on your table (in this case "Table1")
and the newly created temporary table called "temp". The SQL Syntax would
be
UPDATE Table1 LEFT JOIN temp ON (Table1.CUSNUM =
temp.MaxOfCUSNUM) AND
(Table1.ADD1 = temp.ADD1) SET Table1.DNP = "9"
WHERE (((temp.MaxOfCUSNUM) Is Null));

You can create a Macro that runs both the above queries to give you the end
result needed.

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

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




--------------------
| From: (e-mail address removed) (Hanif Merali)
| Newsgroups: microsoft.public.access.queries
| Subject: Writing an Update Query based on 2 criteria
| Date: 21 Jan 2004 11:00:02 -0800
| Organization: http://groups.google.com
| Lines: 30
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 12.40.5.66
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1074711603 26538 127.0.0.1 (21 Jan 2004
19:00:03 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 21 Jan 2004 19:00:03 +0000 (UTC)
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.su
l.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!po
stnews1.google.com!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:187082
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hello I'm having some trouble writing an update query that updates a
| certain field in a table based on 2 criteria. The Table has 3 fields
| that I'm going to be using a CUSTOMERNUM field a ADD1 field and a DNP
| field. I want to update the DNP field to equal 9 if ADD1 is
| duplicated but I only wanna update the DNP field for all of the
| duplicates but 1 based on the customer number.
|
| An example of this would be:
|
| CUSNUM ADD1 DNP
| 2 22 Hunting
| 1 22 Hunting
| 5 22 Hunting
|
| I'd want the table to look like this after running the query
|
| CUSNUM ADD1 DNP
| 2 22 Hunting 9
| 1 22 Hunting 9
| 5 22 Hunting
|
| I want the GREATEST CUSNUM value to not be updated but all other
| cusnum values that had duplicate ADD1 field to be updated to DNP=9.
| This seems pretty confusing but I hope you understand what I am trying
| to accomplish. Thanks in advance for your help!
|
|
| Sincerly,
|
| Hanif Merali
|