How to update a field based on information from other fields from a query in VBA

[ferarbe]

Hello all. I have known my way for many years in Access but I am novice in VBA, so I require you generous help and expertise. I guess it should be pretty simple.
I have this query:

proyect_id	organization_id	previous_counter
272	57	0
272	15	0
274	28	0
296	35	1

and I want to create a VBA module that can create a new field (new_counter) based on the old counter and the count of organizations per project, like this:

proyect_id	organization_id	previous_counter	new_counter
272	57	0	1
272	15	0	2
274	28	0	1
296	35	1	2

Is that possible? Any help would be greatly appreciated.

Thank you very much.

[CJ_London]

providing a) you do not have the same organisation_id in more than once per projectID and b) you don't mind the order the organisations appear then something like this will do what you require

SELECT *, (SELECT Count(*) FROM myTable As T WHERE projectID=myTable.projectID and organisationID<=myTable.organisationID) As newcounter
FROM myTable
ORDER BY projectID,organisationID

[ferarbe]

Brilliant! Thank you very much. I am much more confortable using SQL but I hadn't figured out a way to do it with it.
Now I just need to sum it to the the old counter when it is not 0.
I only do not understand why the "As T" since it is not mentioned anywhere else.
Thanks again.

[ferarbe]

Is it possible to make it directly an update query?

when I try to do an update query based on the query, it says "Operation must use an updatable query". I am using this code:

UPDATE Query3 INNER JOIN MyTable ON ([Query3].organizationID=MyTable.organizationID) AND ([Query3].projectID =MyTable.projectID) SET MyTable.[previous_counter] = [query3].[recounter];

in which recounter is new_counter+max_counter
and max_counter is the max previous_counter of a project.

Do you see the proble?
Thank you very much for your time

[CJ_London]

problem is that you cannot use sub queries in update queries.

try using dcount instead

SELECT *,DCount("*","myTable" ,"projectID=" & myTable.projectID & " and organisationID<=" & myTable.organisationID) As newcounter

[ferarbe]

Great! That worked perfectly. Thank you very much. I made it an update query and added DMax to calculate the newcounter:

UPDATE MyTable SET MyTable.[counter] = DCount("*","myTable","projectID=" & [myTable].[projectID] & " and organizationID<=" & [myTable].[organizationID])+DMax("[counter]","myTable","projectID=" & [MyTable].[projectID]);

Thanks a lot for your time.
Cheers!