Updating Record Count into Table

  • Thread starter Thread starter smeghead
  • Start date Start date
S

smeghead

I'm trying use UPDATE in a Query to update selected fields in a table with
the results of a Count(*).

I have been able to do it with INSERT INTO, but am having a hard time trying
to get it to work using UPDATE.

Here is my Append Query code:

INSERT INTO [Summary Table]
SELECT Count(*) AS [AP Total]
FROM Combined_120804
WHERE (((Combined_120804.Region)="Asia Pacific"));

This Appends the Count(*) data to the table just fine.

Can anyone suggest a way to work the SELECT Count(*) into an UPDATE
statement?

UPDATE table
SET newvalue
WHERE criteria;
 
Try

UPDATE table
SET table.field = DCount("*", "Combined_120804", "Region='Asia Pacific')
WHERE criteria;
 
Thanks Wayne!! Worked great!

Just a note for anyone wanting to cut and paste this code, remember to add
another quotation mark after 'Asia Pacific'. It should read: DCount("*",
"Combined_120804","Region='Asia Pacific'") Just a nit, but may save you
some debugging time.

Wayne Morgan said:
Try

UPDATE table
SET table.field = DCount("*", "Combined_120804", "Region='Asia Pacific')
WHERE criteria;


--
Wayne Morgan
MS Access MVP


smeghead said:
I'm trying use UPDATE in a Query to update selected fields in a table with
the results of a Count(*).

I have been able to do it with INSERT INTO, but am having a hard time
trying
to get it to work using UPDATE.

Here is my Append Query code:

INSERT INTO [Summary Table]
SELECT Count(*) AS [AP Total]
FROM Combined_120804
WHERE (((Combined_120804.Region)="Asia Pacific"));

This Appends the Count(*) data to the table just fine.

Can anyone suggest a way to work the SELECT Count(*) into an UPDATE
statement?

UPDATE table
SET newvalue
WHERE criteria;
Click to expand...
Click to expand...
 
Back
Top