Update MS Access table from nested query

Question

My SQL is very rusty. Am trying to update a counter in a row in a table with a COUNT(*) from that same table in the form of a nested query. The SQL is below:

UPDATE DWInvoiceHeader AS A
SET A.InvCount = (Select Count(B.HIINV) From DWInvoiceHeader AS B 
                  WHERE (B.HIVENT = '0') 
                  Group By B.HIINV 
                  Order By B.HIINV)
WHERE (A.HIVENT = '0');

The rows look like:

HIINV1.......Seq1.....InvCount   - want InvCount to be 3
HIINV1.......Seq2.....InvCount   - want InvCount to be 3
HIINV1.......Seq3.....InvCount   - want InvCount to be 3
HIINV2.......Seq1.....InvCount   - want InvCount to be 2 
HIINV2.......Seq2.....Invcount   - want InvCount to be 2
.
.
.
HIINVn.......Seq1.....InvCount   - want InvCount to be 1

The SQL above gives me the message "Operation must be an updateable query".

Any ideas ?

Why is my query read-only?

marlan
– marlan

2016-05-05 13:10:59 +00:00
Commented May 5, 2016 at 13:10 — marlan
– marlan, Commented May 5, 2016 at 13:10

marlan · Accepted Answer · 2016-05-05 14:11:12Z

0

UPDATE DWInvoiceHeader AS A
SET A.InvCount = 
(
    Select Count(B.HIINV) 
        From DWInvoiceHeader AS B 
        WHERE (B.HIVENT = '0') AND (A.HIINV = B.HIINV)
        Group By B.HIINV 
)
WHERE (A.HIVENT = '0');

edited May 5, 2016 at 14:11

answered May 5, 2016 at 13:04

marlan

1,4851 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marlan Over a year ago

@RawleRamkeesoon, Wasn't it a nested query you have requested? dose the above solution give the desired result set? has the above solution caused an error?

kostas · Accepted Answer · 2016-05-06 08:21:34Z

0

You could use the Domain Function Dcount in a way similar to:

UPDATE test
SET InvCount = Dcount("HIVENT","test","[HIVENT]='0'")

If you want to use a generic approach then I would suggest to store the counts in a working table and then update your table joining the working table.

    Select B.HIINV,Count(B.HIINV) as InvCount 
    into WorkingTbl
    From DWInvoiceHeader AS B 
    WHERE (B.HIVENT = '0') AND (A.HIINV = B.HIINV)
    Group By B.HIINV 

   Update   DWInvoiceHeader  A 
   Inner Join WorkingTbl b 
   on a.HIINV=b.HIINV 
   Set A.InvCount=b.InvCount

edited May 6, 2016 at 8:21

answered May 5, 2016 at 14:45

kostas

4715 silver badges13 bronze badges

4 Comments

Rawle Ramkeesoon Over a year ago

Tried this 2-part solution involving an intermediate table and it worked fine. The DCOUNT function code gave a "Missing operator" message.

marlan Over a year ago

Dcount should be like this: Dcount("HIINV" ,"DWInvoiceHeader", "(B.HIVENT = '0') AND (A.HIINV = B.HIINV)")

marlan Over a year ago

But In general: Office support on Dcount function tip: The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression instead of the DCount function, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control.

kostas Over a year ago

try the edited Dcount function again and see if it works please

Collectives™ on Stack Overflow

Update MS Access table from nested query

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related