update table with generated record count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query which generates MatchNumber, based on values in the table
(thanks to Karl Dewey in an earlier post in this forum).

SELECT T.Player, T.MatchDate, T.Position, (SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate <= T.MatchDate
AND T1.Position <= T.Position) AS MatchNumber
FROM Stats AS T
WHERE T.Player<>"default"
ORDER BY T.Player, T.MatchDate, T.Position;

Now I would like to update Stats.MatchNumber with the generated value, but
of course, this is not an updatable query. How can I generate MatchNumber in
an updatable query? Any assistance would be greatly appreciated.

....Larry
 
Hi,


In Jet? Save the query, say as Q1. Make another query, bring Q1 and Stats,
make an inner join on the first three fields of Q1 with their counterpart in
Stats. Change the query type from a SELECT to an UPDATE one. Bring
Stats.MatchNumber in the grid, update it to [Q1].[MatchNumber] (you
probably need the [ ] around the query name and the field name, in the
designer). That's all, thanks to the inner join.

Oh, experiment on 'fake' data, just in case something go wrong, so, be sure
you can get back the initial data right.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks Michel.

I tried your suggestion, and I'm still getting the "Operation must use an
updatable query" message. My query is:

UPDATE Stats INNER JOIN qryMatchNumbersList
ON (Stats.Position = qryMatchNumbersList.Position)
AND (Stats.MatchDate = qryMatchNumbersList.MatchDate)
AND (Stats.Player = qryMatchNumbersList.Player)
SET Stats.MatchNumber = [qryMatchNumbersList].[MatchNumber];

Also, I have another complication. I've had to add three more columns to the
Select and the first Where clause in my original query. It now looks like:

SELECT T.Gender, T.Level, T.Club, T.Player, T.MatchDate, T.Position,
T.[Week Number], T.PSANumber, T.GamesWon, T.GamesLost,
T.Match, T.[SA date],
(SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate & T1.Gender & T1.Level &
T1.Club & T1.Position <= T.MatchDate &
T.Gender &
T.Level & T.Club & T.Position) AS
MatchNumber
FROM Stats AS T
WHERE (((T.Player)<>"default"))
ORDER BY T.Player, T.MatchDate, T.Gender, T.Level, T.Club, T.Position;

When I attempt to join table Stats to the query on more than four columns,
the generated MatchNumber is always zero. If I join on four or fewer columns,
the generated value is correct.

What am I doing wrong?

....Larry

Michel Walsh said:
Hi,


In Jet? Save the query, say as Q1. Make another query, bring Q1 and Stats,
make an inner join on the first three fields of Q1 with their counterpart in
Stats. Change the query type from a SELECT to an UPDATE one. Bring
Stats.MatchNumber in the grid, update it to [Q1].[MatchNumber] (you
probably need the [ ] around the query name and the field name, in the
designer). That's all, thanks to the inner join.

Oh, experiment on 'fake' data, just in case something go wrong, so, be sure
you can get back the initial data right.


Hoping it may help,
Vanderghast, Access MVP




lallen said:
I have a query which generates MatchNumber, based on values in the table
(thanks to Karl Dewey in an earlier post in this forum).

SELECT T.Player, T.MatchDate, T.Position, (SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate <= T.MatchDate
AND T1.Position <= T.Position) AS MatchNumber
FROM Stats AS T
WHERE T.Player<>"default"
ORDER BY T.Player, T.MatchDate, T.Position;

Now I would like to update Stats.MatchNumber with the generated value, but
of course, this is not an updatable query. How can I generate MatchNumber
in
an updatable query? Any assistance would be greatly appreciated.

...Larry
Click to expand...
Click to expand...
 
HI,


I forgot that your query was using COUNT(*) and thus, is marked as
not-updateable, and also mark so the queries into which we will find it. Two
ways to walk around the problem: use a DCOUNT rather than a sub-select query
with the COUNT(*) in it (but it may still produce something not updatable
in the end), or, make a table out of the query using the DCOUNT, and use
that table, not the query, in the update statement.



Hoping it may help,
Vanderghast, Access MVP


lallen said:
Thanks Michel.

I tried your suggestion, and I'm still getting the "Operation must use an
updatable query" message. My query is:

UPDATE Stats INNER JOIN qryMatchNumbersList
ON (Stats.Position = qryMatchNumbersList.Position)
AND (Stats.MatchDate = qryMatchNumbersList.MatchDate)
AND (Stats.Player = qryMatchNumbersList.Player)
SET Stats.MatchNumber = [qryMatchNumbersList].[MatchNumber];

Also, I have another complication. I've had to add three more columns to
the
Select and the first Where clause in my original query. It now looks like:

SELECT T.Gender, T.Level, T.Club, T.Player, T.MatchDate, T.Position,
T.[Week Number], T.PSANumber, T.GamesWon, T.GamesLost,
T.Match, T.[SA date],
(SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate & T1.Gender & T1.Level &
T1.Club & T1.Position <= T.MatchDate &
T.Gender &
T.Level & T.Club & T.Position) AS
MatchNumber
FROM Stats AS T
WHERE (((T.Player)<>"default"))
ORDER BY T.Player, T.MatchDate, T.Gender, T.Level, T.Club, T.Position;

When I attempt to join table Stats to the query on more than four columns,
the generated MatchNumber is always zero. If I join on four or fewer
columns,
the generated value is correct.

What am I doing wrong?

...Larry

Michel Walsh said:
Hi,


In Jet? Save the query, say as Q1. Make another query, bring Q1 and
Stats,
make an inner join on the first three fields of Q1 with their counterpart
in
Stats. Change the query type from a SELECT to an UPDATE one. Bring
Stats.MatchNumber in the grid, update it to [Q1].[MatchNumber] (you
probably need the [ ] around the query name and the field name, in the
designer). That's all, thanks to the inner join.

Oh, experiment on 'fake' data, just in case something go wrong, so, be
sure
you can get back the initial data right.


Hoping it may help,
Vanderghast, Access MVP




lallen said:
I have a query which generates MatchNumber, based on values in the table
(thanks to Karl Dewey in an earlier post in this forum).

SELECT T.Player, T.MatchDate, T.Position, (SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate <= T.MatchDate
AND T1.Position <= T.Position) AS MatchNumber
FROM Stats AS T
WHERE T.Player<>"default"
ORDER BY T.Player, T.MatchDate, T.Position;

Now I would like to update Stats.MatchNumber with the generated value,
but
of course, this is not an updatable query. How can I generate
MatchNumber
in
an updatable query? Any assistance would be greatly appreciated.

...Larry
Click to expand...
Click to expand...
Click to expand...
 
Thanks Michel.

Michel Walsh said:
HI,


I forgot that your query was using COUNT(*) and thus, is marked as
not-updateable, and also mark so the queries into which we will find it. Two
ways to walk around the problem: use a DCOUNT rather than a sub-select query
with the COUNT(*) in it (but it may still produce something not updatable
in the end), or, make a table out of the query using the DCOUNT, and use
that table, not the query, in the update statement.



Hoping it may help,
Vanderghast, Access MVP


lallen said:
Thanks Michel.

I tried your suggestion, and I'm still getting the "Operation must use an
updatable query" message. My query is:

UPDATE Stats INNER JOIN qryMatchNumbersList
ON (Stats.Position = qryMatchNumbersList.Position)
AND (Stats.MatchDate = qryMatchNumbersList.MatchDate)
AND (Stats.Player = qryMatchNumbersList.Player)
SET Stats.MatchNumber = [qryMatchNumbersList].[MatchNumber];

Also, I have another complication. I've had to add three more columns to
the
Select and the first Where clause in my original query. It now looks like:

SELECT T.Gender, T.Level, T.Club, T.Player, T.MatchDate, T.Position,
T.[Week Number], T.PSANumber, T.GamesWon, T.GamesLost,
T.Match, T.[SA date],
(SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate & T1.Gender & T1.Level &
T1.Club & T1.Position <= T.MatchDate &
T.Gender &
T.Level & T.Club & T.Position) AS
MatchNumber
FROM Stats AS T
WHERE (((T.Player)<>"default"))
ORDER BY T.Player, T.MatchDate, T.Gender, T.Level, T.Club, T.Position;

When I attempt to join table Stats to the query on more than four columns,
the generated MatchNumber is always zero. If I join on four or fewer
columns,
the generated value is correct.

What am I doing wrong?

...Larry

Michel Walsh said:
Hi,


In Jet? Save the query, say as Q1. Make another query, bring Q1 and
Stats,
make an inner join on the first three fields of Q1 with their counterpart
in
Stats. Change the query type from a SELECT to an UPDATE one. Bring
Stats.MatchNumber in the grid, update it to [Q1].[MatchNumber] (you
probably need the [ ] around the query name and the field name, in the
designer). That's all, thanks to the inner join.

Oh, experiment on 'fake' data, just in case something go wrong, so, be
sure
you can get back the initial data right.


Hoping it may help,
Vanderghast, Access MVP




I have a query which generates MatchNumber, based on values in the table
(thanks to Karl Dewey in an earlier post in this forum).

SELECT T.Player, T.MatchDate, T.Position, (SELECT COUNT(*)
FROM Stats T1
WHERE T1.Player = T.Player
AND T1.MatchDate <= T.MatchDate
AND T1.Position <= T.Position) AS MatchNumber
FROM Stats AS T
WHERE T.Player<>"default"
ORDER BY T.Player, T.MatchDate, T.Position;

Now I would like to update Stats.MatchNumber with the generated value,
but
of course, this is not an updatable query. How can I generate
MatchNumber
in
an updatable query? Any assistance would be greatly appreciated.

...Larry
Click to expand...
Click to expand...
Click to expand...
 
Back
Top