Update with Count()

[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

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.

 

[Jay]

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

 

[Guest]

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

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.

 

[John Spencer (MVP)]

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

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.