Nested Update Query in MS Access (1 Viewer)

ashchak

New member
Local time
Today, 09:25
Joined
Aug 9, 2016
Messages
5
Hi Everyone,

i have four field in the database Field 1,Field 2,Field 3,Fields 4
Here Field 3 is subset of Field 2 & Field 2 is subset of Field 1 & Field 4 is Date & rest all fields are Numbers. Now i need to add Field 5 which will be a status where i need to update this basis an update query.

Update Query will be a nested query which will assign Status " New " to all those records where the record under Field 3 and date under Field 4 got inserted for the first time since you will have records with Different date under Field 2.

Example

Field 1 Field 2 Field 3 Field 4 Field 5
1 101 1001 1/5/2016 New
1 102 1002 1/21/2016 New
1 102 1003 2/5/2016
1 102 1004 3/15/2016
2 105 1005 1/11/2016 New
2 105 1006 2/17/2016
2 105 1007 3/15/2016
2 105 1008 4/20/2016
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:25
Joined
Feb 19, 2013
Messages
16,553
try something like this

Code:
UPDATE myTable A 
    INNER JOIN (SELECT Field1, Field2, Field3, min(Field4) as FirstDate FROM myTable GROUP BY Field1, Field2, Field3) F 
        ON A.field1=F.field1 and A.field2=F.field2 and A.field3=F.field3 and A.field4=F.FirstDate
SET A.Field5="New"

note that it will run significantly faster if you index fields 1 to 4
 

sneuberg

AWF VIP
Local time
Today, 09:25
Joined
Oct 17, 2014
Messages
3,506
I suggest just having an expression to indicate the values of Field5 rather than physically adding it to the table. I believe you want Field5 to be equal to "New" when Field1 and Field2 are equal and the date for that group is the earliest, i.e., the case where there is no earlier date. You can determine that with DCount and use the IIF function to display "New" when the dcount returns 0. The expression is:

Code:
Field5: IIf(DCount("*","[Table1]","[Field1] = " & [Field1] & " And [Field2] = " & [Field2] & " And [Field4] < # " & [Field4] & "#")=0,"New",Null)

which is demonstrated in the attached database. You would have to change the Table1 to your table name and maybe the field names (I didn't put spaces in them). Also Field1 and Field2 are handled as numbers. If they are text you would need to delimiter them with single quotes.
 

Attachments

  • FlagRecordAsNew.accdb
    436 KB · Views: 79
Last edited:

sneuberg

AWF VIP
Local time
Today, 09:25
Joined
Oct 17, 2014
Messages
3,506
try something like this

Code:
UPDATE myTable A 
    INNER JOIN (SELECT Field1, Field2, Field3, min(Field4) as FirstDate FROM myTable GROUP BY Field1, Field2, Field3) F 
        ON A.field1=F.field1 and A.field2=F.field2 and A.field3=F.field3 and A.field4=F.FirstDate
SET A.Field5="New"

note that it will run significantly faster if you index fields 1 to 4

Since I already made a test database I tried this and I got "Operation must use an updateable query" but in any case wouldn't this assign "New" to every record as it groups on Field3 too?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:25
Joined
Feb 19, 2013
Messages
16,553
Since I already made a test database I tried this and I got "Operation must use an updateable query"
Ah - forgot about update queries not liking subqueries

but in any case wouldn't this assign "New" to every record as it groups on Field3 too?
good point - code amended

so create a new query, called say qryFirst

Code:
 SELECT Field1, Field2, min(Field4) as FirstDate FROM myTable GROUP BY Field1, Field2

then modify my query to

Code:
 UPDATE myTable A 
    INNER JOIN qryFirst F 
        ON A.field1=F.field1 and A.field2=F.field2 and A.field4=F.FirstDate
SET A.Field5="New"
 

sneuberg

AWF VIP
Local time
Today, 09:25
Joined
Oct 17, 2014
Messages
3,506
Ah - forgot about update queries not liking subqueries

good point - code amended

so create a new query, called say qryFirst

Code:
 SELECT Field1, Field2, min(Field4) as FirstDate FROM myTable GROUP BY Field1, Field2

then modify my query to

Code:
 UPDATE myTable A 
    INNER JOIN qryFirst F 
        ON A.field1=F.field1 and A.field2=F.field2 and A.field4=F.FirstDate
SET A.Field5="New"

I'm getting the same thing. I believe any aggregate anywhere in the food chain will make a query non updateable.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:25
Joined
Feb 19, 2013
Messages
16,553
you are right! It's the aggregate that is the problem

Don't, like to be beaten so this works on your table

Code:
 UPDATE Table1 A 
 SET A.Field5 = 'New'
WHERE A.Field4=(SELECT TOP 1 field4 FROM Table1 WHERE Field1=A.field1 and field2=a.field2 ORDER BY field4)

although I agree with you, it is a calculated value and should not be stored
 

sneuberg

AWF VIP
Local time
Today, 09:25
Joined
Oct 17, 2014
Messages
3,506
you are right! It's the aggregate that is the problem

Don't, like to be beaten so this works on your table

Code:
 UPDATE Table1 A 
 SET A.Field5 = 'New'
WHERE A.Field4=(SELECT TOP 1 field4 FROM Table1 WHERE Field1=A.field1 and field2=a.field2 ORDER BY field4)

although I agree with you, it is a calculated value and should not be stored

That does it. Nice and concise too.
 

ashchak

New member
Local time
Today, 09:25
Joined
Aug 9, 2016
Messages
5
Expr1: IIf(CDate([Weekly_Upload].[Gift Date])>[Transaction_Failed].[Gift Date],"Success","Failure")

Getting error in this expression
 

ashchak

New member
Local time
Today, 09:25
Joined
Aug 9, 2016
Messages
5
Error : Your Query does not include specified expression as a part of aggregate function.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:25
Joined
Feb 19, 2013
Messages
16,553
without any context difficult to say what the problem is. Only thought is you are using code from an earlier post
 

ashchak

New member
Local time
Today, 09:25
Joined
Aug 9, 2016
Messages
5
The context is , there are two tables as mentioned below. Basis the column ID & the date, Just need to update the latest status in another table whether success or failure which ever comes last.

Table 1 : Rejection Table 2 : Success

ID Rejection Rej_Date ID Succ_Date
1001 No Funds 1/1/2016 1001 30/1/2016
1001 No Funds 15/1/2016 1002 15/01/2016
1001 No Funds 29/1/2016
1002 mismatch 16/02/2016
1002 mismatch 23/02/2016
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:25
Joined
Feb 19, 2013
Messages
16,553
a solution was provided in post#7. Please try that first - if it generates errors or does not do what you want, please provide the sql you actually used
 

sneuberg

AWF VIP
Local time
Today, 09:25
Joined
Oct 17, 2014
Messages
3,506
I don't see any connection between

Expr1: IIf(CDate([Weekly_Upload].[Gift Date])>[Transaction_Failed].[Gift Date],"Success","Failure")

and

Table 1 : Rejection Table 2 : Success

ID Rejection Rej_Date ID Succ_Date
1001 No Funds 1/1/2016 1001 30/1/2016
1001 No Funds 15/1/2016 1002 15/01/2016
1001 No Funds 29/1/2016
1002 mismatch 16/02/2016
1002 mismatch 23/02/2016

and neither seems to have any connection to the problem and solution of August 9, 2016. Since we haven't seen any gratitude or even feedback on the first solution why do you think we should help you with this new problem?

You will probably be better off if you post this in a new thread with a better description of what you want, i.e., what output do you expect for a sample input.
 
Last edited:

Users who are viewing this thread

Top Bottom