Update with Count()

  • Thread starter Thread starter Sheryl
  • Start date Start date
S

Sheryl

I have created a SUMMARY file and want to do an update on
SUMMARY.NUMBER which is a count of records in some other
file. Here's some SQL "pseudo code" which (if it worked)
would do what I want.

update SUMMARY set [SUMMARY].[NUMBER] = Count([KITS].
[KIT_ID]) where [SUMMARY].[ID] = "TOT1" and [KITS].[CDATE]
6/1/2003
Click to expand...

What I want is to count all records in KITS having CDATE
after 6/1/03, and set SUMMARY.NUMBER to that value for the
record having SUMMARY.ID equal to "TOT1"

I'm ok with having my SQL include a subquery, but I just
can't figure this out.
 
Sheryl,

I don't know if this would help but try to set the value of the count you want to update first to a global variable.

Declare a public (global) variable in the database then in the code set the PublicVariableName = (Count([KITS].
[KIT_ID]) where [SUMMARY].[ID] = "TOT1" and [KITS].[CDATE] >6/1/2003)

Then use a simple statement to change the value of [SUMMARY].[NUMBER] to the PublicVariableName value. It may not help but taking the calculation of the count out of the update statement may make it work. Sorry if it does not.

Jay
 
How do I "declare a public variable in the database"?

When you say "in the code" do you mean VBA or SQL?

I can't find any reference to "PublicVariableName" in the
help files for either Access or Access-VBA.

Does "use a simple statement" mean something like "update
SUMMARY set [SUMMARY].[NUMBER]= whatever"?

Thanks
-----Original Message-----
Sheryl,

I don't know if this would help but try to set the value
Click to expand...
of the count you want to update first to a global variable.
Declare a public (global) variable in the database then
Click to expand...
in the code set the PublicVariableName = (Count([KITS].
[KIT_ID]) where [SUMMARY].[ID] = "TOT1" and [KITS]. [CDATE] >6/1/2003)

Then use a simple statement to change the value of
Click to expand...
[SUMMARY].[NUMBER] to the PublicVariableName value. It
may not help but taking the calculation of the count out
of the update statement may make it work. Sorry if it does
not.
 
You could use DCOUNT to get the value.

UPDATE Summary
Set Summary.[Number]=
DCount("*","KITS","[Cdate]>#6/1/03#")
WHERE Summary.Id = "TOT1"

I'm not sure this is the best way, but it should work.
I have created a SUMMARY file and want to do an update on
SUMMARY.NUMBER which is a count of records in some other
file. Here's some SQL "pseudo code" which (if it worked)
would do what I want.

update SUMMARY set [SUMMARY].[NUMBER] = Count([KITS].
[KIT_ID]) where [SUMMARY].[ID] = "TOT1" and [KITS].[CDATE]
6/1/2003
Click to expand...

What I want is to count all records in KITS having CDATE
after 6/1/03, and set SUMMARY.NUMBER to that value for the
record having SUMMARY.ID equal to "TOT1"

I'm ok with having my SQL include a subquery, but I just
can't figure this out.
Click to expand...
 
Back
Top