Update query using Count

  • Thread starter Thread starter DanRoy
  • Start date Start date
D

DanRoy

Using Access 2003 (11.8204.8221) SP3 in XP Pro

I have a table in which I want to capture summary counts from another table.
I can write a select query and use the count function and get the results I
want. In order to write those results to my summary table, though, I need an
Update query, which does not have the Count function available.

SQL of Select query which provides the results I want:

SELECT DISTINCT Count(Dashmaster.[Team Code]) AS [CountOfTeam Code],
IIf(Len([team code])=1,"0" & [team code],[team code]) AS tc,
Dashmaster.[Change Number] AS dash, [dash] & "-" & [tc] AS keyc
FROM Dashmaster
GROUP BY IIf(Len([team code])=1,"0" & [team code],[team code]),
Dashmaster.[Change Number], Dashmaster.[Team Code];

The results i care about from this query are the CntOfTeam Code and keyc,
which is the match value (Key field) in the Measurement_Plan table.

Not finding a solution there, I went to trying to write SQL in VB.

snippit of code (not complete)

Set rst = db.OpenRecordset("measurement_plan", dbOpenTable, dbConsistent)


DoCmd.SetWarnings False
CurrentDb.Execute "Delete * from Measurement_Plan;", dbFailOnError
DoCmd.OpenQuery "MP_Unique_Team-IPT_Append"

'establishes the shell to which updates are applied

' step through Measurement_Plan 1 record at a time,, collect all inputs
from Dashmaster on match and increment Cnt CAM Boe's field with each match,
then move on to next record in Measurement_plan and repeat

With rst
rst.MoveFirst
'rst.Edit
With CurrentDb.OpenRecordset("dashmaster", dbOpenDynaset)

Do Until .EOF ' To step through all records

If (Len(.Fields("Team code")) = 1) Then TeamRef = "0" &
..Fields("TEAM code") _
Else TeamRef = .Fields("TEAM code")
DashSOW = .Fields("change number") & "-" & TeamRef

SQL = "Update Measurement_Plan " & _
" Set [measurement_plan].[Cnt RAM Boe's] = " & rst![Cnt RAM
Boe's] + 1 & _
" Where [measurement_plan].Key = '" & DashSOW & "'"

Me.Repaint
SQLAll = SQLAll & vbCrLf & DashSOW & " " & rst![Cnt RAM Boe's]
Label30.Caption = SQLAll
CurrentDb.Execute SQL, dbFailOnError
.MoveNext

Loop
' .Update
End With
rst.MoveNext
End With

This got me closer, in that I was able to get results to write to my summary
table, Measurement_Plan, but the results were not correct. The intent in the
code was to query the measurement_Plan table one record at a time and extract
the MATCH value for DashSOW that I needed to find in the details, table,
Dashmaster. If a match, I wanted to increment the counter in the
Measurement_Plan table, the Cnt CAM BOE's field. I did not find a way to
directly calculate a count of occurrence in the dashmaster table to update
the Measurement_Plan table with, though that would be the preferred approach.

Can anyone tell me why this is not working as I expect it should? Can anyone
offer an alternative I have not yet considered?

Thanks
 
First off, I'm assuming from what you said that you want to update a field in
the Measurement_Plan table with the value from CntOfTeam Code value wherever
the keyc fields match. If that's not right, then I'm not sure what you're
doing. If that is right, here's what I would do. I usually use 'stored'
queries, and I use the Query Design grid to create them. Especially for Group
BY type queries. I would create a 'stored' query that represents your Group
By query as you have it. Then I would create a 2nd query (again using the
design grid) that joins the stored query and the Measurement_Plan table by
that key field. Make it an update query, updating the appropriate field in
the Measurement_Plan table with the value from CntOfTeam Code. Hope that
makes sense.

DanRoy said:
Using Access 2003 (11.8204.8221) SP3 in XP Pro

I have a table in which I want to capture summary counts from another table.
I can write a select query and use the count function and get the results I
want. In order to write those results to my summary table, though, I need an
Update query, which does not have the Count function available.

SQL of Select query which provides the results I want:

SELECT DISTINCT Count(Dashmaster.[Team Code]) AS [CountOfTeam Code],
IIf(Len([team code])=1,"0" & [team code],[team code]) AS tc,
Dashmaster.[Change Number] AS dash, [dash] & "-" & [tc] AS keyc
FROM Dashmaster
GROUP BY IIf(Len([team code])=1,"0" & [team code],[team code]),
Dashmaster.[Change Number], Dashmaster.[Team Code];

The results i care about from this query are the CntOfTeam Code and keyc,
which is the match value (Key field) in the Measurement_Plan table.

Not finding a solution there, I went to trying to write SQL in VB.

snippit of code (not complete)

Set rst = db.OpenRecordset("measurement_plan", dbOpenTable, dbConsistent)


DoCmd.SetWarnings False
CurrentDb.Execute "Delete * from Measurement_Plan;", dbFailOnError
DoCmd.OpenQuery "MP_Unique_Team-IPT_Append"

'establishes the shell to which updates are applied

' step through Measurement_Plan 1 record at a time,, collect all inputs
from Dashmaster on match and increment Cnt CAM Boe's field with each match,
then move on to next record in Measurement_plan and repeat

With rst
rst.MoveFirst
'rst.Edit
With CurrentDb.OpenRecordset("dashmaster", dbOpenDynaset)

Do Until .EOF ' To step through all records

If (Len(.Fields("Team code")) = 1) Then TeamRef = "0" &
.Fields("TEAM code") _
Else TeamRef = .Fields("TEAM code")
DashSOW = .Fields("change number") & "-" & TeamRef

SQL = "Update Measurement_Plan " & _
" Set [measurement_plan].[Cnt RAM Boe's] = " & rst![Cnt RAM
Boe's] + 1 & _
" Where [measurement_plan].Key = '" & DashSOW & "'"

Me.Repaint
SQLAll = SQLAll & vbCrLf & DashSOW & " " & rst![Cnt RAM Boe's]
Label30.Caption = SQLAll
CurrentDb.Execute SQL, dbFailOnError
.MoveNext

Loop
' .Update
End With
rst.MoveNext
End With

This got me closer, in that I was able to get results to write to my summary
table, Measurement_Plan, but the results were not correct. The intent in the
code was to query the measurement_Plan table one record at a time and extract
the MATCH value for DashSOW that I needed to find in the details, table,
Dashmaster. If a match, I wanted to increment the counter in the
Measurement_Plan table, the Cnt CAM BOE's field. I did not find a way to
directly calculate a count of occurrence in the dashmaster table to update
the Measurement_Plan table with, though that would be the preferred approach.

Can anyone tell me why this is not working as I expect it should? Can anyone
offer an alternative I have not yet considered?

Thanks
Click to expand...
 
Thank you, Jim. I have pursued the direction you suggestted to no avail. As
in my original, the first Select query produced the correct counts of each of
the teams with the correct conversion to the key-field format. All I want to
do is take that select file, join it to the Measurement_Plan via the key
field and update the count field in Measurement_Plan. problem is i am
unable to create an update query (Part 2 above) that executes. i keep
getting error reports that I haven't used a field with tc ( the key field
conversion from Step 1). I played around with this for hours today.

Result of applying Step 2
UPDATE Query12 INNER JOIN Measurement_Plan ON Query12.ct =
Measurement_Plan.key SET Measurement_Plan.[Cnt RAM BOE's] = [query12].[cta];

error from access is :

Operation must use an updateable query error 3073
I read the help which indicated to close the database down and retry.

Shut down the database and tried to rerun the query with the same result.

Any suggestions?

Jim Burke in Novi said:
First off, I'm assuming from what you said that you want to update a field in
the Measurement_Plan table with the value from CntOfTeam Code value wherever
the keyc fields match. If that's not right, then I'm not sure what you're
doing. If that is right, here's what I would do. I usually use 'stored'
queries, and I use the Query Design grid to create them. Especially for Group
BY type queries. I would create a 'stored' query that represents your Group
By query as you have it. Then I would create a 2nd query (again using the
design grid) that joins the stored query and the Measurement_Plan table by
that key field. Make it an update query, updating the appropriate field in
the Measurement_Plan table with the value from CntOfTeam Code. Hope that
makes sense.

DanRoy said:
Using Access 2003 (11.8204.8221) SP3 in XP Pro

I have a table in which I want to capture summary counts from another table.
I can write a select query and use the count function and get the results I
want. In order to write those results to my summary table, though, I need an
Update query, which does not have the Count function available.

SQL of Select query which provides the results I want:

SELECT DISTINCT Count(Dashmaster.[Team Code]) AS [CountOfTeam Code],
IIf(Len([team code])=1,"0" & [team code],[team code]) AS tc,
Dashmaster.[Change Number] AS dash, [dash] & "-" & [tc] AS keyc
FROM Dashmaster
GROUP BY IIf(Len([team code])=1,"0" & [team code],[team code]),
Dashmaster.[Change Number], Dashmaster.[Team Code];

The results i care about from this query are the CntOfTeam Code and keyc,
which is the match value (Key field) in the Measurement_Plan table.

Not finding a solution there, I went to trying to write SQL in VB.

snippit of code (not complete)

Set rst = db.OpenRecordset("measurement_plan", dbOpenTable, dbConsistent)


DoCmd.SetWarnings False
CurrentDb.Execute "Delete * from Measurement_Plan;", dbFailOnError
DoCmd.OpenQuery "MP_Unique_Team-IPT_Append"

'establishes the shell to which updates are applied

' step through Measurement_Plan 1 record at a time,, collect all inputs
from Dashmaster on match and increment Cnt CAM Boe's field with each match,
then move on to next record in Measurement_plan and repeat

With rst
rst.MoveFirst
'rst.Edit
With CurrentDb.OpenRecordset("dashmaster", dbOpenDynaset)

Do Until .EOF ' To step through all records

If (Len(.Fields("Team code")) = 1) Then TeamRef = "0" &
.Fields("TEAM code") _
Else TeamRef = .Fields("TEAM code")
DashSOW = .Fields("change number") & "-" & TeamRef

SQL = "Update Measurement_Plan " & _
" Set [measurement_plan].[Cnt RAM Boe's] = " & rst![Cnt RAM
Boe's] + 1 & _
" Where [measurement_plan].Key = '" & DashSOW & "'"

Me.Repaint
SQLAll = SQLAll & vbCrLf & DashSOW & " " & rst![Cnt RAM Boe's]
Label30.Caption = SQLAll
CurrentDb.Execute SQL, dbFailOnError
.MoveNext

Loop
' .Update
End With
rst.MoveNext
End With

This got me closer, in that I was able to get results to write to my summary
table, Measurement_Plan, but the results were not correct. The intent in the
code was to query the measurement_Plan table one record at a time and extract
the MATCH value for DashSOW that I needed to find in the details, table,
Dashmaster. If a match, I wanted to increment the counter in the
Measurement_Plan table, the Cnt CAM BOE's field. I did not find a way to
directly calculate a count of occurrence in the dashmaster table to update
the Measurement_Plan table with, though that would be the preferred approach.

Can anyone tell me why this is not working as I expect it should? Can anyone
offer an alternative I have not yet considered?

Thanks
Click to expand...
Click to expand...
 
Offhand the query looks OK. What is the exact statement you're using to run
the query? If you use DoCmd.RunSQL strSQL, where strSQL is set to the update
SQL text, it should run OK. If it's a named 'stored' query you should be to
use DoCmd.OpenQuery "yourQueryNameHere".

DanRoy said:
Thank you, Jim. I have pursued the direction you suggestted to no avail. As
in my original, the first Select query produced the correct counts of each of
the teams with the correct conversion to the key-field format. All I want to
do is take that select file, join it to the Measurement_Plan via the key
field and update the count field in Measurement_Plan. problem is i am
unable to create an update query (Part 2 above) that executes. i keep
getting error reports that I haven't used a field with tc ( the key field
conversion from Step 1). I played around with this for hours today.

Result of applying Step 2
UPDATE Query12 INNER JOIN Measurement_Plan ON Query12.ct =
Measurement_Plan.key SET Measurement_Plan.[Cnt RAM BOE's] = [query12].[cta];

error from access is :

Operation must use an updateable query error 3073
I read the help which indicated to close the database down and retry.

Shut down the database and tried to rerun the query with the same result.

Any suggestions?

Jim Burke in Novi said:
First off, I'm assuming from what you said that you want to update a field in
the Measurement_Plan table with the value from CntOfTeam Code value wherever
the keyc fields match. If that's not right, then I'm not sure what you're
doing. If that is right, here's what I would do. I usually use 'stored'
queries, and I use the Query Design grid to create them. Especially for Group
BY type queries. I would create a 'stored' query that represents your Group
By query as you have it. Then I would create a 2nd query (again using the
design grid) that joins the stored query and the Measurement_Plan table by
that key field. Make it an update query, updating the appropriate field in
the Measurement_Plan table with the value from CntOfTeam Code. Hope that
makes sense.

DanRoy said:
Using Access 2003 (11.8204.8221) SP3 in XP Pro

I have a table in which I want to capture summary counts from another table.
I can write a select query and use the count function and get the results I
want. In order to write those results to my summary table, though, I need an
Update query, which does not have the Count function available.

SQL of Select query which provides the results I want:

SELECT DISTINCT Count(Dashmaster.[Team Code]) AS [CountOfTeam Code],
IIf(Len([team code])=1,"0" & [team code],[team code]) AS tc,
Dashmaster.[Change Number] AS dash, [dash] & "-" & [tc] AS keyc
FROM Dashmaster
GROUP BY IIf(Len([team code])=1,"0" & [team code],[team code]),
Dashmaster.[Change Number], Dashmaster.[Team Code];

The results i care about from this query are the CntOfTeam Code and keyc,
which is the match value (Key field) in the Measurement_Plan table.

Not finding a solution there, I went to trying to write SQL in VB.

snippit of code (not complete)

Set rst = db.OpenRecordset("measurement_plan", dbOpenTable, dbConsistent)


DoCmd.SetWarnings False
CurrentDb.Execute "Delete * from Measurement_Plan;", dbFailOnError
DoCmd.OpenQuery "MP_Unique_Team-IPT_Append"

'establishes the shell to which updates are applied

' step through Measurement_Plan 1 record at a time,, collect all inputs
from Dashmaster on match and increment Cnt CAM Boe's field with each match,
then move on to next record in Measurement_plan and repeat

With rst
rst.MoveFirst
'rst.Edit
With CurrentDb.OpenRecordset("dashmaster", dbOpenDynaset)

Do Until .EOF ' To step through all records

If (Len(.Fields("Team code")) = 1) Then TeamRef = "0" &
.Fields("TEAM code") _
Else TeamRef = .Fields("TEAM code")
DashSOW = .Fields("change number") & "-" & TeamRef

SQL = "Update Measurement_Plan " & _
" Set [measurement_plan].[Cnt RAM Boe's] = " & rst![Cnt RAM
Boe's] + 1 & _
" Where [measurement_plan].Key = '" & DashSOW & "'"

Me.Repaint
SQLAll = SQLAll & vbCrLf & DashSOW & " " & rst![Cnt RAM Boe's]
Label30.Caption = SQLAll
CurrentDb.Execute SQL, dbFailOnError
.MoveNext

Loop
' .Update
End With
rst.MoveNext
End With

This got me closer, in that I was able to get results to write to my summary
table, Measurement_Plan, but the results were not correct. The intent in the
code was to query the measurement_Plan table one record at a time and extract
the MATCH value for DashSOW that I needed to find in the details, table,
Dashmaster. If a match, I wanted to increment the counter in the
Measurement_Plan table, the Cnt CAM BOE's field. I did not find a way to
directly calculate a count of occurrence in the dashmaster table to update
the Measurement_Plan table with, though that would be the preferred approach.

Can anyone tell me why this is not working as I expect it should? Can anyone
offer an alternative I have not yet considered?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Jim. I have pursued the direction you suggestted to no avail. As
in my original, the first Select query produced the correct counts of each of
the teams with the correct conversion to the key-field format. All I want to
do is take that select file, join it to the Measurement_Plan via the key
field and update the count field in Measurement_Plan.
Click to expand...

Ummmm... No. You DON'T want to do this, I'm almost certain!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If (for some reason, such as DEMONSTRATED, not assumed, inadequate
performance) you need to store the total, then be aware that no Totals query -
nor any query *containing* a Totals query - is ever updateable. You'll need to
update the field to

=DCount("*", <tablename>, <criteria string>)

instead of using the totals query.
 
Back
Top